Triggers

A trigger is a procedure fired automatically when a DML statement (INSERT, UPDATE, or DELETE) is run against a certain table. A row-level trigger runs once for every row affected. A statement-level trigger runs once for all the rows. A row-level trigger has access to the old and new column values when the trigger fires.

Creating a Trigger

You create a trigger using the CREATE TRIGGER statement. The simplified syntax for the CREATE TRIGGER statement:


CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF | FOR} trigger_event
ON table_name
[FOR EACH ROW]
[WHEN trigger_condition]]
BEGIN
   trigger_body
END trigger_name;

Item	Description
BEFORE	the trigger fires before the triggering event is performed.
AFTER	the trigger fires after the triggering event is performed.
INSTEAD OF	the trigger fires instead of performing the triggering event.
FOR	creates a compound trigger consisting of up to four sections in the trigger body.
trigger_event	the event that causes the trigger to fire.
table_name	the table that the trigger references.
FOR EACH ROW	the trigger is a row-level trigger. If you omit FOR EACH ROW,the trigger is a statement-level trigger.
trigger_condition	a Boolean condition that limits when a trigger actually runs its code.
trigger_body	contains the code for the trigger.

 
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);

CREATE TABLE EMP_AUDIT (EMPNO NUMBER(4) NOT NULL,
                  old_SAL NUMBER(7, 2),
                  new_SAL NUMBER(7, 2));


CREATE TRIGGER before_salary_update
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW WHEN (new.sal < old.sal * 0.75)
BEGIN
   dbms_output.put_line('id = ' || :old.empno);
   dbms_output.put_line('Old:' || :old.sal);
   dbms_output.put_line('New:' || :new.sal);
   dbms_output.put_line('The sal reduction is more than 25%');
   INSERT INTO emp_audit (empno, old_sal, new_sal) 
          VALUES (:old.empno, :old.sal, :new.sal);
END before_salary_update;
/

SET SERVEROUTPUT ON

UPDATE emp SET sal = sal * .7;


SELECT * FROM emp_audit;

Home »
Oracle »
PL/SQL »

Triggers: