Mega Code Archive
Trigger for auditing
<![CDATA[
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, 'Jack', 'Tester', 6,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, 'Wil', 'Tester', 6,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, 'Jane', 'Designer', 9,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, 'Mary', 'Tester', 6,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, 'Black', 'Designer', 9,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, 'Chris', 'Designer', 9,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, 'Smart', 'Helper', 4,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, 'Peter', 'Manager', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, 'Take', 'Tester', 6,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, 'Fake', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL>
SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13)
5 );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
1 row created.
SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
1 row created.
SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL> CREATE TABLE DEPT$AUDIT (
2 DEPTNO NUMBER,
3 DNAME VARCHAR2(14 byte),
4 LOC VARCHAR2(13 byte),
5 CHANGE_TYPE VARCHAR2(1 byte),
6 CHANGED_BY VARCHAR2(30 byte),
7 CHANGED_TIME DATE
8 );
SQL>
SQL> CREATE OR REPLACE TRIGGER auditDEPTAR AFTER
2 INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW
3 declare
4 my DEPT$audit%ROWTYPE;
5 begin
6 if inserting then my.change_type := 'I';
7 elsif updating then my.change_type :='U';
8 else my.change_type := 'D';
9 end if;
10
11 my.changed_by := user;
12 my.changed_time := sysdate;
13
14 case my.change_type
15 when 'I' then
16 my.DEPTNO := :new.DEPTNO;
17 my.DNAME := :new.DNAME;
18 my.LOC := :new.LOC;
19 else
20 my.DEPTNO := :old.DEPTNO;
21 my.DNAME := :old.DNAME;
22 my.LOC := :old.LOC;
23 end case;
24
25 insert into DEPT$audit values my;
26 end;
27 /
Trigger created.
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
]]>