Logging All Operatins Using Autonumbering : Auidt Table « Trigger « Oracle PL/SQL Tutorial

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

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>