Jednoduchý trigger na auditování DML operací

SQL> select * from F4211;

SDDELN
----------
 1

create table F4211_audit(
F4211 NUMBER(7), 
AFTER_F4211 NUMBER(7),
user_name VARCHAR2(30),
action VARCHAR2(30),
upd_date DATE);

CREATE OR REPLACE TRIGGER audit_F4211
AFTER INSERT OR DELETE OR UPDATE
ON TOM.F4211
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO F4211_audit VALUES (
NULL,
:new.SDDELN,
User,
'INSERT',
Sysdate
);
ELSIF DELETING THEN
INSERT INTO F4211_audit VALUES (
NULL,
:old.SDDELN,
User,
'DELETE',
Sysdate
);
ELSIF UPDATING THEN
INSERT INTO F4211_audit VALUES (
:old.SDDELN,
:new.SDDELN,
User,
'UPDATE',
Sysdate
);
END IF;
END;
/

SQL> insert into F4211 values (3);

1 row created.

SQL> insert into F4211 values (2);

1 row created.

Report
column F4211 format 99;
column AFTER_F4211 format 99;
column user_name format a20;
column action format a10;
select F4211, AFTER_F4211,user_name,action, TO_CHAR(upd_date, 'DD-MON-YYYY HH:MI:SS') from F4211_audit;

F4211 AFTER_F4211 USER_NAME ACTION TO_CHAR(UPD_DATE,'DD
----- ----------- -------------------- ---------- --------------------
 1 0 TOM UPDATE 22-MAY-2014 04:01:23
 3 TOM INSERT 22-MAY-2014 04:02:43
 2 TOM INSERT 22-MAY-2014 04:02:51
 2 TOM DELETE 22-MAY-2014 04:03:50

Komentáře