Examples: Triggers (PL/SQL)
PL/SQL trigger definitions can be compiled by the data server. These examples will help you to create valid triggers and to troubleshoot PL/SQL trigger compilation errors.
Before row-level triggers
The following
example shows a before row-level trigger that calculates the commission
of every new employee belonging to department 30 before a record for
that employee is inserted into the EMP table:
CREATE OR REPLACE TRIGGER emp_comm_trig
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF :NEW.deptno = 30 THEN
:NEW.comm := :NEW.sal * .4;
END IF;
END;
The trigger computes the commissions for two new employees
and inserts those values as part of the new employee rows:INSERT INTO emp VALUES (9005,'ROBERS','SALESMAN',7782,SYSDATE,3000.00,NULL,30);
INSERT INTO emp VALUES (9006,'ALLEN','SALESMAN',7782,SYSDATE,4500.00,NULL,30);
SELECT * FROM emp WHERE empno IN (9005, 9006);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9005 ROBERS SALESMAN 7782 01-APR-05 3000 1200 30
9006 ALLEN SALESMAN 7782 01-APR-05 4500 1800 30
After row-level triggers
The following example
shows three after row-level triggers.
- When a new employee row is inserted into the EMP table, one trigger (EMP_INS_TRIG) adds a new row to the JOBHIST table for that employee and adds a row to the EMPCHGLOG table with a description of the action.
- When an existing employee row is updated, the second trigger (EMP_CHG_TRIG) 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. This trigger also adds a row to the EMPCHGLOG table with a description of the action
- When an employee row is deleted from the EMP table, the third trigger (EMP_DEL_TRIG) 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_ins_trig
AFTER INSERT 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
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');
INSERT INTO empchglog VALUES (SYSDATE,
v_action || ' employee # ' || v_empno);
END;
CREATE OR REPLACE TRIGGER emp_chg_trig
AFTER UPDATE 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
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);
INSERT INTO empchglog VALUES (SYSDATE,
v_action || ' employee # ' || v_empno);
END;
CREATE OR REPLACE TRIGGER emp_del_trig
AFTER 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
v_action := 'Deleted';
v_empno := :OLD.empno;
v_deptno := :OLD.deptno;
INSERT INTO empchglog VALUES (SYSDATE,
v_action || ' employee # ' || v_empno);
END;
In the following example, two employee rows
are added using two separate INSERT statements, and then both rows
are updated using a single UPDATE statement. The JOBHIST table shows
the action of the trigger for each affected row: two new hire entries
for the two new employees and two changed commission records. The
EMPCHGLOG table also shows that the trigger was fired a total of four
times, once for each action against 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
After both employees
are deleted with a single DELETE statement, the EMPCHGLOG table shows
that 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