DB2触发器简单例子
db2使用版本9.7创建A 、B两个表,A表数据有更新、删除、插入时，将A表ID记录放入B表
1、create table A (id varchar(5),name varchar(30));
create table B (id varchar(5),name varchar(30))
2、创建触发器
--插入时触动
CREATE TRIGGER administrator.tri_insert
AFTER INSERT ON administrator.A
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO administrator.B(ID,NAME) VALUES(N.ID,N.NAME );
END
--测试: INSERT INTOadministrator.A VALUES ('3','Name');
--删除时触动
CREATE TRIGGER administrator.tri_delete
AFTER DELETE ON administrator.A
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO administrator.B(ID) VALUES(O.ID);
END --测试:DELETE FROM administrator.A WHERE ID = '3'
--更新时触动
CREATE OR REPLACETRIGGER administrator.tri_update
AFTER UPDATE OF NAME ON administrator.A
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO administrator.B(ID,NAME) VALUES(O.ID,N.NAME);
END
--测试:UPDATE administrator.A SET NAME = 'n2_name'
--有资料提示 DB2 9.7以上版本支持 AFTER INSERT OR DELETE OR UPDATE 写法，可是9.7版本并不支持
例子：
CREATE OR REPLACE TRIGGER HIRED
AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
ELSEIF
DELETING THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
ELSEIF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%';
END IF;
END;
之后，考虑使用DB2中ORACLE兼容性的设置，db2set DB2_COMPATIBILITY_VECTOR=ORA , 依然不支持 AFTER INSERT OR DELETE OR UPDATE 写法，有成功在9.7版本中成功使用AFTER INSERT OR DELETE OR UPDATE 写法的麻烦指导下
页:
[1]