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.