Logging INSERT Operations With WHEN Conditions : Audit Log Table « Trigger « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Trigger
  3. Audit Log Table

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.








Related examples in the same category

1.Log and audit data change
2.Log insert, update, delete for a table
3.Log user name and system time in a trigger
4.Logging All Operatins Using Autonumbering
5.Logging All Operations
6.Logging INSERT Operations
7.Trigger for auditing