Oracleのトリガー

データベースに更新があったときに確実にやっておきたいことを実装する仕組みです。
どこを見ても難しい説明とサンプルしかないので簡単なサンプルを動かしてみました。
T1テーブルへのINSERT/UPDATE/DELETEをT2テーブルに適用するトリガーです。
これで、いろいろと試すことができます。

$ cat sample.sql
COLUMN f1 FORMAT a10;
COLUMN f2 FORMAT a10;
DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1 ( f1 VARCHAR2(255), f2 VARCHAR2(255));
ALTER TABLE T1 ADD PRIMARY KEY (f1);
CREATE TABLE T2 ( f1 VARCHAR2(255), f2 VARCHAR2(255));
ALTER TABLE T2 ADD PRIMARY KEY (f1);
CREATE OR REPLACE TRIGGER TRIG_T1
BEFORE INSERT OR UPDATE OR DELETE
ON T1
FOR EACH ROW
DECLARE
BEGIN
dbms_output.put_line('old.f1:' || :OLD.f1);
dbms_output.put_line('new.f1:' || :NEW.f1);
dbms_output.put_line('old.f2:' || :OLD.f2);
dbms_output.put_line('new.f2:' || :NEW.f2);
IF INSERTING THEN
dbms_output.put_line('Insert!');
INSERT INTO T2 VALUES(:NEW.f1, :NEW.F2);
ELSIF UPDATING THEN
dbms_output.put_line('Update!');
UPDATE T2 SET f1 = :NEW.f1, f2 = :NEW.f2 WHERE f1 = :OLD.f1;
ELSIF DELETING THEN
dbms_output.put_line('Delete!');
DELETE FROM T2 WHERE f1 = :OLD.f1;
END IF;
END;
/
set serveroutput on
/
insert into T1 values('key', 'value');
select * from T2;
update T1 set f1 = 'key2', f2 = 'value2';
select * from T2;
delete from T1;
select count(*) from T2;
$

$ sqlplus scott/tiger@XE @sample.sql
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 13 23:20:50 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Table dropped.
Table dropped.
Table created.
Table altered.
Table created.
Table altered.
Trigger created.
Trigger created.
old.f1:
new.f1:key
old.f2:
new.f2:value
Insert!
1 row created.
F1	   F2
---------- ----------
key	   value
old.f1:key
new.f1:key2
old.f2:value
new.f2:value2
Update!
1 row updated.
F1	   F2
---------- ----------
key2	   value2
old.f1:key2
new.f1:
old.f2:value2
new.f2:
Delete!
1 row deleted.
COUNT(*)
----------
0SQL>