The following example is an AFTER statement-level trigger. Whenever an insert, update, or delete operation occurs on the emp table, a row is added to the empauditlog table recording the date, user, and action.
CREATE TABLE empauditlog (
audit_date DATE,
audit_user VARCHAR2(20),
audit_desc VARCHAR2(20)
);
CREATE OR REPLACE TRIGGER emp_audit_trig
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_action VARCHAR2(20);
BEGIN
IF INSERTING THEN
v_action := 'Added employee(s)';
ELSIF UPDATING THEN
v_action := 'Updated employee(s)';
ELSIF DELETING THEN
v_action := 'Deleted employee(s)';
END IF;
INSERT INTO empauditlog VALUES (SYSDATE, USER,
v_action);
END;
In the following sequence of statements, two rows are inserted into the emp table by using two INSERT statements. The sal and comm columns of both rows are updated by using one UPDATE statement. Finally, both rows are deleted by using one DELETE statement.
INSERT INTO emp VALUES (9001,'SMITH','ANALYST',7782,SYSDATE,NULL,NULL,10);
INSERT INTO emp VALUES (9002,'JONES','CLERK',7782,SYSDATE,NULL,NULL,10);
UPDATE emp SET sal = 4000.00, comm = 1200.00 WHERE empno IN (9001, 9002);
DELETE FROM emp WHERE empno IN (9001, 9002);
SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "AUDIT DATE",
audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;
AUDIT DATE AUDIT_USER AUDIT_DESC
------------------ -------------------- --------------------
31-MAR-05 14:59:48 SYSTEM Added employee(s)
31-MAR-05 15:00:07 SYSTEM Added employee(s)
31-MAR-05 15:00:19 SYSTEM Updated employee(s)
31-MAR-05 15:00:34 SYSTEM Deleted employee(s)
The contents of the empauditlog table show the times the trigger is fired: once each for the two inserts, once for the update even though two rows are changed, and once for the deletion even though two rows are deleted.