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>