Logging All Operations : Auidt Table « Trigger « Oracle PL/SQL Tutorial






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.








28.12.Auidt Table
28.12.1.Use audit table in a trigger
28.12.2.Audit trigger
28.12.3.Logging All Operatins Using Autonumbering
28.12.4.Logging All Operations
28.12.5.Logging INSERT Operations
28.12.6.Logging INSERT Operations With WHEN Conditions
28.12.7.Trigger for auditing