How to use trigger on Oracle

Prepare table

CREATE TABLE t1
(
c1 INTEGER,
c2 VARCHAR(10),
c3 VARCHAR(10)
)
CREATE TABLE t1_audit
(
a1 DATE,
a2 VARCHAR(3),
c1 INTEGER,
c2 VARCHAR(10),
c3 VARCHAR(10)
)
insert into t1 values (1,'111','aaa');
insert into t1 values (2,'222','bbb');
insert into t1 values (3,'333','ccc');
insert into t1 values (4,'444','ddd');

Create trigger

CREATE OR REPLACE TRIGGER t1_trig
AFTER UPDATE ON t1
FOR EACH ROW
BEGIN
IF(:NEW.c2 IS NULL) THEN
INSERT INTO t1_audit VALUES (SYSDATE, 'OLD', :OLD.c1, :OLD.c2, :OLD.c3);
INSERT INTO t1_audit VALUES (SYSDATE, 'NEW', :NEW.c1, :NEW.c2, :NEW.c3);
END IF;
END;

or use this command

CREATE OR REPLACE TRIGGER t1_trig
AFTER UPDATE ON t1
FOR EACH ROW
WHEN (NEW.c2 IS NULL)
BEGIN
INSERT INTO t1_audit VALUES (SYSDATE, 'OLD', :OLD.c1, :OLD.c2, :OLD.c3);
INSERT INTO t1_audit VALUES (SYSDATE, 'NEW', :NEW.c1, :NEW.c2, :NEW.c3);
END;

Test the trigger

UPDATE t1
SET t1.c2 = ''
WHERE t1.c1 = 3;

View result on t1_audit

07/08/2008 14:26:02 OLD 3 333 ccc
07/08/2008 14:26:02 NEW 3 ccc

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-triggers.html
http://www.psoug.org/reference/table_trigger.html
http://www.exforsys.com/tutorials/oracle-9i/oracle-database-triggers.html
http://somkiat.exteen.com/20040715/trigger-short-tutorial