Question

The ‘SALES - Sunshine’ department has unfortunately run into a technical issue and is temporarily unable to process any ‘Credit’ or ‘Debit’ transactions. As a result, it only accepts ‘Cash’ transactions. Besides, the department is offering a 30% discount on ‘Data Recovery’ at the moment. Write a SQL statement to create an Oracle trigger SUNSHINE_DEPT that will set the PaymentType to always be ‘Cash’ for any purchases where the client is served by an employee of this department, and if the ServiceType is ‘Data Recovery’, give the customer a 30% discount. Note that this discount is exclusive to the ‘SALES - Sunshine’ department. Database structure:

The Database: The SALES database (Figure 1) captures the sales information in a company that provides IT services. The database includes four tables: CLIENT, PURCHASE, EMP, and DEPT. CLIENT stores information about all the company's clients. PURCHASE keeps track of the service purchases made by the clients. EMP stores information about the employees who work directly with the clients and serve their purchase requests. Employees work in different departments and the information about these departments is stored in the DEPT table. Figure 1 presents the database schema. EMP PURCHASE EmpNo EName Position DeptNo Purchase No ReceiptNo Service Type PaymentType GST Amount ServedBy ClientNo DEPT CLIENT DeptNo DName ClientNo CName Phone Figure 1 Database schema

Answer 1

The trigger is created below. The trigger is on table Purchase, whenever a purchase happens this trigger will get fired. It will set the paymentType to Cash and give the discount to user who is served by the employee of dept Sales Sunshine and also if the service type is data recovery.

Trigger-

CREATE TRIGGER SUNSHINE_DEPT
BEFORE INSERT ON PURCHASE
FOR EACH ROW
BEGIN
   UPDATE PURCHASE
   SET PaymentType = "Cash", Amount = Amount - Amount * 0.30
   WHERE ServedBy = (SELECT EmpNo FROM EMP INNER JOIN DEPT ON EMP.DeptNo = DEPT.DeptNo WHERE DEPT.DName = "SALES-Sunshine")
   AND ServiceType = "Data Recovery";
END;