Mega Code Archive
Triggers
<![CDATA[
A trigger is a procedure that is run automatically by the database when a specified SQL DML INSERT, UPDATE, or DELETE statement is run against a table.
Triggers are useful for doing things like advanced auditing of changes made to column values in a table.
When a Trigger Runs
A trigger can fire before or after the SQL statement runs.
A trigger can may be run once for every row affected. Such a trigger is known as a row-level trigger.
A trigger can may be run for all the rows. Such trigger is known as a statement-level trigger.
A row-level trigger has access to the old and new column values when the trigger fires as a result of an UPDATE statement on that column.
The firing of a trigger may also be limited using a trigger condition.
Different events may fire a trigger, but these events are always divided into three groups:
DML triggers,
INSTEAD OF triggers, and
system event triggers.
DML triggers are the triggers on INSERT/UPDATE/DELETE operations in any table.
SQL> CREATE TABLE employee_history (
2 name VARCHAR2(100),
3 description VARCHAR2(255),
4 occurred_on DATE);
Table created.
SQL>
SQL> CREATE TABLE employee_compensation (
2 company VARCHAR2(100),
3 name VARCHAR2(100),
4 compensation NUMBER,
5 layoffs NUMBER);
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE employee_audit (
2 name IN VARCHAR2,
3 description IN VARCHAR2,
4 occurred_on IN DATE
5 )
6 IS
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 BEGIN
9 INSERT INTO employee_history VALUES (
10 employee_audit.name,
11 employee_audit.description,
12 employee_audit.occurred_on
13 );
14 COMMIT;
15 END;
16 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
2 BEFORE INSERT ON employee_compensation FOR EACH ROW
3 DECLARE
4 ok BOOLEAN := TRUE;
5 BEGIN
6 IF ok
7 THEN
8 employee_audit (
9 :new.name, 'BEFORE INSERT', SYSDATE);
10 END IF;
11 END;
12 /
Trigger created.
SQL>
SQL> SELECT name,
2 description,
3 TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
4 FROM employee_history;
no rows selected
SQL>
SQL> BEGIN
2 INSERT INTO employee_compensation VALUES ('M', 'J', 9100000, 2700);
3
4 INSERT INTO employee_compensation VALUES ('A', 'H', 33200000, 3300);
5
6 INSERT INTO employee_compensation VALUES ('E', 'G', 10700000, 20100);
7
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT name,
2 description,
3 TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
4 FROM employee_history;
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
OCCURRED_ON
-------------------
J
BEFORE INSERT
07/24/2008 08:03:08
H
BEFORE INSERT
07/24/2008 08:03:08
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
OCCURRED_ON
-------------------
G
BEFORE INSERT
07/24/2008 08:03:08
SQL>
SQL> DROP TABLE employee_compensation;
Table dropped.
SQL>
SQL> DROP TABLE employee_history;
Table dropped.
]]>