In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Saturday, July 25, 2009

Triggers

A trigger is a stored subprogram associated with a table, view, or event. The trigger can be invoked once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be invoked before or after the event.

The trigger in example is invoked whenever salaries in the employees table are updated. For each update, the trigger writes a record to the emp_audit table.

Example:

Creating a Trigger

SQL> CREATE TABLE emp_audit (
2 emp_audit_id NUMBER(6),
3 up_date DATE,
4 new_sal NUMBER(8,2),
5 old_sal NUMBER(8,2)
6 );

Table created.

SQL>
CREATE OR REPLACE TRIGGER audit_sal
2
AFTER UPDATE OF salary
3
ON employees
4
FOR EACH ROW
5
BEGIN
6
INSERT INTO emp_audit
7
VALUES(:old.employee_id,SYSDATE,:new.salary,
:old.salary);

8
END;
9 /

Trigger created.

SQL> update employees set salary=5000
2 where salary < 3000;

24 rows updated.


SQL> select * from emp_audit;
EMP_AUDIT_ID UP_DATE NEW_SAL OLD_SAL
------------ --------- ---------- ----------
116 25-JUL-09 5000 2900
117 25-JUL-09 5000 2800
118 25-JUL-09 5000 2600
119 25-JUL-09 5000 2500
126 25-JUL-09 5000 2700
127 25-JUL-09 5000 2400
128 25-JUL-09 5000 2200
130 25-JUL-09 5000 2800
131 25-JUL-09 5000 2500
132 25-JUL-09 5000 2100
134 25-JUL-09 5000 2900

EMP_AUDIT_ID UP_DATE NEW_SAL OLD_SAL
------------ --------- ---------- ----------
135 25-JUL-09 5000 2400
136 25-JUL-09 5000 2200
139 25-JUL-09 5000 2700
140 25-JUL-09 5000 2500
143 25-JUL-09 5000 2600
144 25-JUL-09 5000 2500
182 25-JUL-09 5000 2500
183 25-JUL-09 5000 2800
190 25-JUL-09 5000 2900
191 25-JUL-09 5000 2500
195 25-JUL-09 5000 2800

EMP_AUDIT_ID UP_DATE NEW_SAL OLD_SAL
------------ --------- ---------- ----------
198 25-JUL-09 5000 2600
199 25-JUL-09 5000 2600

24 rows selected.




No comments:

 
Share/Bookmark