DB2 触发器

测试表

db2 => CREATE TABLE test_trigger_table (
db2 (cont.) =>     id     INT,
db2 (cont.) =>     name   VARCHAR(10),
db2 (cont.) =>     val    INT
db2 (cont.) => );
DB20000I  SQL 命令成功完成。

INSERT

db2 => CREATE TRIGGER BeforeInsertTest \

db2 (cont.) => BEFORE INSERT ON test_trigger_table \

db2 (cont.) => REFERENCING NEW AS N \

db2 (cont.) => FOR EACH ROW \

db2 (cont.) => BEGIN \

db2 (cont.) =>   SET N.val = 100; \

db2 (cont.) => END

DB20000I  SQL 命令成功完成。

 

db2 => INSERT INTO test_trigger_table(id, name) VALUES (1, 'ABC')

DB20000I  SQL 命令成功完成。

db2 => SELECT * FROM test_trigger_table

 

ID          NAME       VAL

----------- ---------- -----------

          1 ABC                100

 

  1 条记录已选择。

 

UPDATE

db2 => CREATE TABLE log_table(log_text VARCHAR(30))

DB20000I  SQL 命令成功完成。

 

db2 => CREATE TRIGGER AfterUpdateTest \

db2 (cont.) => AFTER UPDATE ON test_trigger_table \

db2 (cont.) => REFERENCING OLD AS O NEW AS N \

db2 (cont.) => FOR EACH ROW \

db2 (cont.) => BEGIN \

db2 (cont.) =>   INSERT INTO log_table VALUES ( 'Old Name = ' || O.name ); \

db2 (cont.) =>   INSERT INTO log_table VALUES ( 'New Name = ' || N.name ); \

db2 (cont.) => END

DB20000I  SQL 命令成功完成。

 

db2 => UPDATE test_trigger_table SET name = 'XYZ' WHERE id = 1

DB20000I  SQL 命令成功完成。

db2 => select * from log_table

 

LOG_TEXT

------------------------------

Old Name = ABC

New Name = XYZ

 

  2 条记录已选择。

 

DELETE

 

db2 => CREATE TRIGGER AfterDeleteTest \

db2 (cont.) => AFTER DELETE ON test_trigger_table \

db2 (cont.) => REFERENCING OLD AS O \

db2 (cont.) => FOR EACH ROW \

db2 (cont.) => BEGIN \

db2 (cont.) =>   INSERT INTO log_table VALUES ( 'Delete Name = ' || O.name ); \

db2 (cont.) => END

DB20000I  SQL 命令成功完成。

db2 => DELETE FROM test_trigger_table WHERE id = 1

DB20000I  SQL 命令成功完成。

db2 => select * from log_table

 

LOG_TEXT

------------------------------

Old Name = ABC

New Name = XYZ

Delete Name = XYZ

 

  3 条记录已选择。

 

语句级/行级

DB2语句级 
db2 => truncate TABLE log_table IMMEDIATE
DB20000I  SQL 命令成功完成。

db2 => CREATE TRIGGER AfterInsertTest \
db2 (cont.) => AFTER INSERT ON test_trigger_table \
db2 (cont.) => REFERENCING NEW_TABLE AS inserted \
db2 (cont.) => for each statement \
db2 (cont.) => BEGIN ATOMIC \
db2 (cont.) =>    INSERT INTO log_table SELECT 'N' || name FROM inserted; \
db2 (cont.) => END
DB20000I  SQL 命令成功完成。

db2 => INSERT INTO test_trigger_table(id, name) VALUES (1, 'ABC')
DB20000I  SQL 命令成功完成。
db2 =>  INSERT INTO test_trigger_table(id, name) VALUES (2, 'DEF')
DB20000I  SQL 命令成功完成。
db2 => insert into test_trigger_table  select * from test_trigger_table
DB20000I  SQL 命令成功完成。
db2 => select * from log_table

LOG_TEXT
------------------------------
NABC
NDEF
NABC
NDEF

  4 条记录已选择。

DB2行级 
前面的 所有的 FOR EACH ROW  的触发器，都是 行级的触发器。 

 

 

针对特定列的触发
测试此处时，先删除其它的触发器

db2 => CREATE TRIGGER AfterUpdateTest \

db2 (cont.) => AFTER UPDATE OF val ON test_trigger_table \

db2 (cont.) => REFERENCING OLD AS O NEW AS N \

db2 (cont.) => FOR EACH ROW \

db2 (cont.) => BEGIN \

db2 (cont.) =>   INSERT INTO log_table VALUES ( 'Old val = ' || O.val ); \

db2 (cont.) =>   INSERT INTO log_table VALUES ( 'New val = ' || N.val ); \

db2 (cont.) => END

DB20000I  SQL 命令成功完成。

 

db2 => truncate TABLE log_table IMMEDIATE

DB20000I  SQL 命令成功完成。

 

db2 => truncate TABLE test_trigger_table IMMEDIATE

DB20000I  SQL 命令成功完成。

db2 =>  INSERT INTO test_trigger_table(id, name, val) VALUES (1, 'ABC', 10)

DB20000I  SQL 命令成功完成。

db2 =>  UPDATE test_trigger_table SET name = 'XYZ' WHERE id=1

DB20000I  SQL 命令成功完成。

db2 =>  UPDATE test_trigger_table SET val = 123 WHERE id=1

DB20000I  SQL 命令成功完成。

db2 => select * from test_trigger_table

 

ID          NAME       VAL

----------- ---------- -----------

          1 XYZ                123

 

  1 条记录已选择。

 

db2 => select * from log_table

 

LOG_TEXT

------------------------------

Old val = 10

New val = 123

 

  2 条记录已选择。

 

 

针对特定条件的触发
测试此处时，先删除其它的触发器

 

db2 => CREATE TRIGGER BeforeUpdateTest \

db2 (cont.) => BEFORE UPDATE OF val ON test_trigger_table \

db2 (cont.) => REFERENCING OLD AS O NEW AS N \

db2 (cont.) => FOR EACH ROW MODE DB2SQL \

db2 (cont.) => WHEN (N.val > (O.val * 1.5)) \

db2 (cont.) => BEGIN ATOMIC \

db2 (cont.) =>   SIGNAL SQLSTATE '75001' ('VAL 增幅过大！') ; \

db2 (cont.) => END

DB20000I  SQL 命令成功完成。

db2 => select * from test_trigger_table

 

ID          NAME       VAL

----------- ---------- -----------

          1 XYZ                123

 

  1 条记录已选择。

 

db2 => update test_trigger_table set val = 130 where id=1

DB20000I  SQL 命令成功完成。

db2 => update test_trigger_table set val = 300 where id=1

DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在

SQL 处理期间，它返回：

SQL0438N  应用程序发生错误或警告，其诊断文本为："VAL 增幅过大！"。

SQLSTATE=75001

 

嵌套触发器
测试此处时，先删除其它的触发器

这里的 INSERT 触发器。 调用了 UPDATE 语句， 导致了 UPDATE 触发器的触发。

db2 => truncate TABLE log_table IMMEDIATE

DB20000I  SQL 命令成功完成。

db2 => truncate TABLE test_trigger_table IMMEDIATE

DB20000I  SQL 命令成功完成。

 

db2 => CREATE TRIGGER AfterUpdateTest \

db2 (cont.) => AFTER UPDATE ON test_trigger_table \

db2 (cont.) => REFERENCING NEW_TABLE AS inserted \

db2 (cont.) => for each statement \

db2 (cont.) => BEGIN ATOMIC \

db2 (cont.) =>   INSERT INTO log_table SELECT 'U_' || name FROM inserted; \

db2 (cont.) => END

DB20000I  SQL 命令成功完成。

 

db2 => CREATE TRIGGER AfterInsertTest \

db2 (cont.) => AFTER INSERT ON test_trigger_table \

db2 (cont.) => REFERENCING NEW_TABLE AS inserted \

db2 (cont.) => for each statement \

db2 (cont.) => BEGIN ATOMIC \

db2 (cont.) =>   INSERT INTO log_table SELECT 'I_' || name FROM inserted; \

db2 (cont.) =>   UPDATE test_trigger_table \

db2 (cont.) =>   SET val = 100 \

db2 (cont.) =>   WHERE id IN (SELECT id FROM inserted); \

db2 (cont.) => END

DB20000I  SQL 命令成功完成。

 

db2 => INSERT INTO test_trigger_table(id, name) VALUES (1, 'ABC')

DB20000I  SQL 命令成功完成。

db2 => select * from test_trigger_table

 

ID          NAME       VAL

----------- ---------- -----------

          1 ABC                100

 

  1 条记录已选择。

 

db2 => select * from  log_table

 

LOG_TEXT

------------------------------

I_ABC

U_ABC

 

  2 条记录已选择。

 

 

已创建的触发器的查询

db2 => SELECT

db2 (cont.) =>   CAST(NAME AS varchar(25)),

db2 (cont.) =>   CAST(TBNAME AS varchar(25))

db2 (cont.) => FROM

db2 (cont.) =>   sysibm.systriggers;

 

1                         2

------------------------- -------------------------

POLICY_IR                 POLICY

POLICY_IV                 POLICY

POLICY_UV                 POLICY

POLICY_DR                 POLICY

V_SALE_REPORT_TRIGGER     V_SALE_REPORT_SUM

AFTERINSERTTEST           TEST_TRIGGER_TABLE

AFTERINSERTDETAIL         ORDERDETAIL

AFTERUPDATETEST           TEST_TRIGGER_TABLE

 

  8 条记录已选择。

 

 

小结

触发器有2种

一种语句级别的for each statement

一种行级的FOR EACH ROW

 

有 BEFORE 有 AFTER

取得操作数据的是通过  old  new

 

对于语句级的触发器

取得操作数据的是通过  OLD_TABLE  NEW_TABLE

 

对于 old / new / old_table / new_table

需要通过

REFERENCING OLD AS O NEW AS N

以及

REFERENCING NEW_TABLE AS inserted

这样的方式，来定义使用。

 

通过OF 字段 ON 表实现只针对特定列的触发。

通过 WHEN 实现特定条件下的触发。