After row-level trigger v15
This example shows an after row-level trigger. When a new employee row is inserted, the trigger adds a row to the jobhist
table for that employee. When an existing employee is updated, the trigger sets the enddate
column of the latest jobhist
row (assumed to be the one with a null enddate
) to the current date and inserts a new jobhist
row with the employee’s new information.
Then, the trigger adds a row to the empchglog
table with a description of the action.
CREATE TABLE empchglog ( chg_date DATE, chg_desc VARCHAR2(30) ); CREATE OR REPLACE TRIGGER emp_chg_trig AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW DECLARE v_empno emp.empno%TYPE; v_deptno emp.deptno%TYPE; v_dname dept.dname%TYPE; v_action VARCHAR2(7); v_chgdesc jobhist.chgdesc%TYPE; BEGIN IF INSERTING THEN v_action := 'Added'; v_empno := :NEW.empno; v_deptno := :NEW.deptno; INSERT INTO jobhist VALUES (:NEW.empno, SYSDATE, NULL, :NEW.job, :NEW.sal, :NEW.comm, :NEW.deptno, 'New Hire'); ELSIF UPDATING THEN v_action := 'Updated'; v_empno := :NEW.empno; v_deptno := :NEW.deptno; v_chgdesc := ''; IF NVL(:OLD.ename, '-null-') != NVL(:NEW.ename, '-null-') THEN v_chgdesc := v_chgdesc || 'name, '; END IF; IF NVL(:OLD.job, '-null-') != NVL(:NEW.job, '-null-') THEN v_chgdesc := v_chgdesc || 'job, '; END IF; IF NVL(:OLD.sal, -1) != NVL(:NEW.sal, -1) THEN v_chgdesc := v_chgdesc || 'salary, '; END IF; IF NVL(:OLD.comm, -1) != NVL(:NEW.comm, -1) THEN v_chgdesc := v_chgdesc || 'commission, '; END IF; IF NVL(:OLD.deptno, -1) != NVL(:NEW.deptno, -1) THEN v_chgdesc := v_chgdesc || 'department, '; END IF; v_chgdesc := 'Changed ' || RTRIM(v_chgdesc, ', '); UPDATE jobhist SET enddate = SYSDATE WHERE empno = :OLD.empno AND enddate IS NULL; INSERT INTO jobhist VALUES (:NEW.empno, SYSDATE, NULL, :NEW.job, :NEW.sal, :NEW.comm, :NEW.deptno, v_chgdesc); ELSIF DELETING THEN v_action := 'Deleted'; v_empno := :OLD.empno; v_deptno := :OLD.deptno; END IF; INSERT INTO empchglog VALUES (SYSDATE, v_action || ' employee # ' || v_empno); END;
In the first sequence of the following commands, two employees are added using two separate INSERT
commands. Then both are updated using a single UPDATE
command. The contents of the jobhist
table show the action of the trigger for each affected row: two new-hire entries for the two new employees and two changed commission records for the updated commissions on the two employees. The empchglog
table also shows the trigger was fired a total of four times, once for each action on the two rows.
INSERT INTO emp VALUES (9003,'PETERS','ANALYST',7782,SYSDATE,5000.00,NULL,40); INSERT INTO emp VALUES (9004,'AIKENS','ANALYST',7782,SYSDATE,4500.00,NULL,40); UPDATE emp SET comm = sal * 1.1 WHERE empno IN (9003, 9004); SELECT * FROM jobhist WHERE empno IN (9003, 9004);
EMPNO STARTDATE ENDDATE JOB SAL COMM DEPTNO CHGDESC ---------- --------- --------- --------- ---------- ---------- ---------- ------------- 9003 31-MAR-05 31-MAR-05 ANALYST 5000 40 New Hire 9004 31-MAR-05 31-MAR-05 ANALYST 4500 40 New Hire 9003 31-MAR-05 ANALYST 5000 5500 40 Changed commission 9004 31-MAR-05 ANALYST 4500 4950 40 Changed commission
SELECT * FROM empchglog;
CHG_DATE CHG_DESC --------- ------------------------------ 31-MAR-05 Added employee # 9003 31-MAR-05 Added employee # 9004 31-MAR-05 Updated employee # 9003 31-MAR-05 Updated employee # 9004
Then, a single DELETE
command deletes both employees. The empchglog
table shows the trigger was fired twice, once for each deleted employee.
DELETE FROM emp WHERE empno IN (9003, 9004); SELECT * FROM empchglog;
CHG_DATE CHG_DESC --------- ------------------------------ 31-MAR-05 Added employee # 9003 31-MAR-05 Added employee # 9004 31-MAR-05 Updated employee # 9003 31-MAR-05 Updated employee # 9004 31-MAR-05 Deleted employee # 9003 31-MAR-05 Deleted employee # 9004