Trigger for auditing : Auidt Table « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Auidt Table

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.