Oracle PL/SQL Tutorial/Trigger/Auidt Table

Audit trigger

SQL>
SQL> CREATE TABLE game_player
  2  (player_id    NUMBER,
  3   game_id      NUMBER,
  4   group_number NUMBER,
  5   marked       VARCHAR2(1) DEFAULT "N",
  6   pcmac        VARCHAR2(1) DEFAULT "N",
  7   score        NUMBER,
  8   CONSTRAINT game_player_pk
  9   PRIMARY KEY (player_id, game_id, group_number));
Table created.
SQL>
SQL> CREATE TABLE game_player_audit
  2  (player_id    NUMBER,
  3   game_id      NUMBER,
  4   group_number NUMBER,
  5   old_marked   VARCHAR2(1),
  6   new_marked   VARCHAR2(1),
  7   old_pcmac    VARCHAR2(1),
  8   new_pcmac    VARCHAR2(1),
  9   old_score    NUMBER,
 10   new_score    NUMBER,
 11   change_date  DATE,
 12   operation    VARCHAR2(6));
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER audit_game_players
  2  AFTER INSERT OR UPDATE OR DELETE ON game_player
  3  FOR EACH ROW
  4  BEGIN
  5    IF INSERTING THEN
  6      INSERT INTO game_player_audit(player_id,game_id,group_number,
  7                              new_marked,new_pcmac,new_score,
  8                              change_date,operation)
  9      VALUES(:new.player_id,:new.game_id,:new.group_number,
 10             :new.marked,:new.pcmac,:new.score,
 11             SYSDATE,"INSERT");
 12    ELSIF UPDATING THEN
 13      INSERT INTO game_player_audit(player_id,game_id,group_number,
 14                                old_marked,new_marked,
 15                                old_pcmac,new_pcmac,
 16                              old_score,new_score,
 17                              change_date,operation)
 18      VALUES(:new.player_id,:new.game_id,:new.group_number,
 19             :old.marked,:new.marked,
 20             :old.pcmac,:new.pcmac,
 21             :old.score,:new.score,
 22             SYSDATE,"UPDATE");
 23    ELSIF DELETING THEN
 24      INSERT INTO game_player_audit(player_id,game_id,group_number,
 25                              old_marked,old_pcmac,old_score,
 26                              change_date,operation)
 27      VALUES(:old.player_id,:old.game_id,:old.group_number,
 28             :old.marked,:old.pcmac,:old.score,
 29             SYSDATE,"DELETE");
 30    END IF;
 31  END;
 32  /
Trigger created.
SQL>
SQL>
SQL> drop table game_player;
Table dropped.
SQL>
SQL> drop table game_player_audit;
Table dropped.
SQL>

Logging All Operatins Using Autonumbering

SQL>
SQL>
SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL, PhoneNo VARCHAR(15));
Table created.
SQL>
SQL> CREATE TABLE myTableAudit(
  2  id INT PRIMARY KEY NOT NULL,
  3   Operation VARCHAR(10),
  4   RecordedOn DATE DEFAULT SysDate,
  5   OldName VARCHAR(50),
  6   NewName VARCHAR(50),
  7   OldPhone VARCHAR(15),
  8   NewPhone VARCHAR(15)
  9  );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER myTableAuditTrigger
  2  AFTER INSERT OR DELETE OR UPDATE ON myTable
  3  FOR EACH ROW
  4  BEGIN
  5    IF INSERTING THEN
  6      INSERT INTO myTableAudit (Operation, NewName, NewPhone)VALUES ("Insert ", :NEW.Name, :NEW.PhoneNo);
  7    ELSIF DELETING THEN
  8      INSERT INTO myTableAudit (Operation, OldName, OldPhone)VALUES ("Delete ", :OLD.Name, :OLD.PhoneNo);
  9    ELSIF UPDATING THEN
 10      INSERT INTO myTableAudit (Operation, OldName, OldPhone, NewName, NewPhone)VALUES ("Update ", :OLD.Name, :OLD.PhoneNo, :NEW.Name, :NEW.PhoneNo);
 11    END IF;
 12  END;
 13  /
Trigger created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTableAudit;
Table dropped.
SQL>

Logging All Operations

SQL>
SQL>
SQL>
SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
  2                       PhoneNo VARCHAR(15));
Table created.
SQL>
SQL> CREATE TABLE myTableAudit
  2  (id INT PRIMARY KEY NOT NULL,
  3   Operation VARCHAR(10),
  4   RecordedOn DATE DEFAULT SysDate,
  5   OldName VARCHAR(50),
  6   NewName VARCHAR(50),
  7   OldPhone VARCHAR(15),
  8   NewPhone VARCHAR(15));
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER myTableAuditTrigger
  2  AFTER INSERT OR DELETE OR UPDATE ON myTable
  3  FOR EACH ROW
  4  BEGIN
  5    IF INSERTING THEN
  6      INSERT INTO myTableAudit (id, Operation, NewName, NewPhone)
  7      VALUES (1, "Insert ", :NEW.Name, :NEW.PhoneNo);
  8    ELSIF DELETING THEN
  9      INSERT INTO myTableAudit (id, Operation, OldName, OldPhone)
 10      VALUES (1, "Delete ", :OLD.Name, :OLD.PhoneNo);
 11    ELSIF UPDATING THEN
 12      INSERT INTO myTableAudit (id, Operation,
 13                               OldName, OldPhone, NewName, NewPhone)
 14      VALUES (1, "Update ",
 15              :OLD.Name, :OLD.PhoneNo, :NEW.Name, :NEW.PhoneNo);
 16    END IF;
 17  END;
 18  /
Trigger created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTableAudit;
Table dropped.

Logging INSERT Operations

SQL>
SQL>
SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
  2                       PhoneNo VARCHAR(15));
Table created.
SQL>
SQL> CREATE TABLE myTableAudit
  2  (id INT PRIMARY KEY NOT NULL,
  3   Operation VARCHAR(10),
  4   RecordedOn DATE DEFAULT SysDate,
  5   OldName VARCHAR(50),
  6   NewName VARCHAR(50),
  7   OldPhone VARCHAR(15),
  8   NewPhone VARCHAR(15));
Table created.
SQL>
SQL>
SQL> CREATE or replace TRIGGER myTableAuditTrigger
  2  AFTER INSERT ON myTable
  3  FOR EACH ROW
  4  BEGIN
  5    INSERT INTO myTableAudit (id, Operation, NewName, NewPhone)
  6    VALUES (1, "Insert ", :NEW.Name, :NEW.PhoneNo);
  7  END;
  8  /
Trigger created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTableAudit;
Table dropped.
SQL>

Logging INSERT Operations With WHEN Conditions

SQL>
SQL>
SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
  2                       PhoneNo VARCHAR(15));
Table created.
SQL>
SQL> CREATE TABLE myTableAudit
  2  (id INT PRIMARY KEY NOT NULL,
  3   Operation VARCHAR(10),
  4   RecordedOn DATE DEFAULT SysDate,
  5   OldName VARCHAR(50),
  6   NewName VARCHAR(50),
  7   OldPhone VARCHAR(15),
  8   NewPhone VARCHAR(15));
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER myTableAuditTrigger
  2  AFTER INSERT ON myTable
  3  FOR EACH ROW
  4  WHEN (NEW.Name="Peter")
  5  BEGIN
  6    INSERT INTO myTableAudit (id, Operation, NewName, NewPhone)
  7    VALUES (1, "Insert ", :NEW.Name, :NEW.PhoneNo);
  8  END;
  9  /
Trigger created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTableAudit;
Table dropped.

Trigger for auditing

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.

Use audit table in a trigger

SQL>
SQL>
SQL> create table myaudit(
  2     id VARCHAR2(4 BYTE)         NOT NULL,
  3     old_value VARCHAR2(40 BYTE),
  4     new_value VARCHAR2(40 BYTE)
  5  );
Table created.
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER before_employee_salary_update
  2  BEFORE UPDATE OF salary
  3  ON employee
  4  FOR EACH ROW WHEN (new.salary < old.salary * 0.75)
  5  BEGIN
  6    dbms_output.put_line("id = " || :old.id);
  7    dbms_output.put_line("Old salary = " || :old.salary);
  8    dbms_output.put_line("New salary = " || :new.salary);
  9    dbms_output.put_line("The salary reduction is more than 25%");
 10
 11  INSERT INTO Myaudit (
 12    id, old_value, new_value
 13  ) VALUES (
 14    :old.id, :old.salary, :new.salary
 15  );
 16  END before_employee_salary_update;
 17  /
Trigger created.
SQL>
SQL> update employee set salary = 0;
8 rows updated.
SQL>
SQL>
SQL> select * from employee;
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06          0 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86          0 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90          0 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99          0 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98          0 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96          0 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98          0 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02          0 Vancouver  Tester
8 rows selected.
SQL>
SQL> select * from myaudit;
ID   OLD_VALUE                                NEW_VALUE
---- ---------------------------------------- ----------------------------------------
01   1234.56                                  0
02   6661.78                                  0
03   6544.78                                  0
04   2344.78                                  0
05   2334.78                                  0
06   4322.78                                  0
07   7897.78                                  0
08   1232.78                                  0
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL> drop table myaudit;
Table dropped.
SQL>
SQL>
SQL>