Salaried_worker (name, office, phone, salary) Hourly_worker (name, hourly_wage)

Question

Salaried_worker (name, office, phone, salary) Hourly_worker (name, hourly_wage) Address (name, street, city) 1. 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 or 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.)

Explanation / Answer

create or replace trigger updt_total_sal
after insert on EMP
for each row
declare
dept_exists pls_integer :=0;
begin
select count(1)
into dept_exists
from DEPTSALARY
where DEPT = :new.DEPT;

if dept_exists > 0 then
UPDATE DEPTSALARY
SET TOTALSALARY = TOTALSALARY +:new.SALARY
where DEPT = :new.DEPT;
else
INSERT INTO DEPTSALARY VALUES (:new.DEPT,:new.SALARY);
end if;
end;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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