-- Demonstrate a simple INSTEAD OF trigger that allows updates to a
-- department salary summary view that would otherwise not be updatable
-- Copyright (c) 2004, Caleb.com

-- Get sample tables
CREATE TABLE emp  AS SELECT * FROM scott.emp;
CREATE TABLE dept AS SELECT * FROM scott.dept;

-- Create a complex view that does a table join and group function
CREATE OR REPLACE VIEW depsum AS
   SELECT deptno, dname, sum(sal) AS dsum
   FROM emp NATURAL JOIN dept 
   GROUP BY deptno, dname;

-- Create the trigger that will fire INSTEAD of an UPDATE statement
CREATE OR REPLACE TRIGGER depsum_update
INSTEAD OF UPDATE ON depsum
REFERENCING new AS new old AS old
DECLARE
   v_diff   number;
   v_emps   number;
   v_incr   number;
BEGIN

   -- Changes to PK not allowed
   IF (:new.deptno != :old.deptno) THEN
      raise_application_error(-20010,'Can''t change primary key!');
   END IF;

   -- If changes to dept records then update single record in DEPT table
   IF (:new.dname != :old.dname) THEN
      UPDATE dept 
      SET dname = :new.dname
      WHERE deptno = :old.deptno;
   END IF;

   -- If changes to sum total from EMP table, then prorate changes over
   -- number of records affected, and apply the change to multiple records
   IF (:new.dsum != :old.dsum) THEN

      -- How many employees are affected?
      select count(*) into v_emps from emp
      where deptno = :old.deptno;

      -- Calculate their pro-rated change
      v_diff := :new.dsum - :old.dsum;
      v_incr := v_diff / v_emps;

      -- Update all employees within the department
      UPDATE emp 
      SET sal = sal + v_incr
      WHERE deptno = :old.deptno;

   END IF;

END;
/