Solve the question in two parts, first using standard SQL and then solve using O

Question

Solve the question in two parts, first using standard SQL and then solve using Oracle. Please answer only if you understand databases well and can answer correctly.

Suppose you are given a relation emp(empid, dept, salary) and wish to maintain a materialized view

deptsalary(dept, totalsalary) which stores the total salary for all employees of each department. Suppose the system does not support materialized views but supports triggers. Write an SQL and a Oracle trigger on insert on emp to keep the relation deptsalary up to date. Do not worry about deletes or updates. You can assume that there is already a tuple for each department in deptsalary so you do not need to worry about new deparments. (Note: if you solve this question using Oracle, do not create a view for deptsalary, just create a table for it. You will use keywords: create trigger, create or replace trigger, insert on .....etc)

Explanation / Answer

1. SQl server

CREATE TRIGGER [dbo].[Customer_UPDATE]
       ON [dbo].[deptsalary]
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @DeptId INT
       DECLARE @TotalSal float

       SELECT @DeptId = emp.dept,
   @totalsal = sum(emp.salary)     
       FROM emp where deptsalary.dept = emp.dept

       IF UPDATE(dept)
       BEGIN
              SET @totalSal = @totalsalary
       END

       INSERT INTO deptsalary
       VALUES@DeptId, @totalSal

   UPDATE deptsalary set dept = @DeptId, totalSal = @totalSal
END

2. Oracle

CREATE OR REPLACE TRIGGER updateTrigger
AFTER UPDATE
   ON deptsalary
   FOR EACH ROW

DECLARE
   deptid varchar2(10);
    totalsal float;

BEGIN

   -- Find dept to be UPDATED into table
   SELECT dept,sum(salary) INTO emp
   FROM dual where deptsalary.dept = emp.dept group by dept;

   -- Insert record into audit table
   INSERT INTO deptsalary
   ( dept,
     totalsalary)
   VALUES
   ( :dept,:totalsalry);

or

update deptsalary set dept = :dept, totalsalary = :totalsalry;

END;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.