DB2触发器例子

来源:IT165收集  发布日期:2014-06-17 21:19:24
-- 建表语句
CREATE TABLE WANGZSTABLE (
  ID	INTEGER	NOT NULL,
  NAME	VARCHAR(20)	NOT NULL,
  AGE	VARCHAR(20)	NOT NULL,
  STATUS	VARCHAR(10),
  CREATETIME	TIMESTAMP,
  UPDATETIME	TIMESTAMP,
  SENDTIME	TIMESTAMP
);

select * from wangzsTable;
insert into WANGZSTABLE(id,name,age) values(1,'wangzs1','26');
update WANGZSTABLE set status='2' where id=1;


--update 更新特定状态 status=4
DROP TRIGGER "WANGZS_UPDATE";

CREATE TRIGGER WANGZS_UPDATE
  AFTER UPDATE OF STATUS ON WANGZSTABLE
  REFERENCING OLD AS OLDROW NEW AS NEWROW
  FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  IF(NEWROW.STATUS='4')
    THEN
      update wangzsTable set UPDATETIME=CURRENT TIMESTAMP where id=NEWROW.id;
  END IF;
END;

--update 更新特定id
DROP TRIGGER "WANGZS_UPDATE";

CREATE TRIGGER WANGZS_UPDATE
  AFTER UPDATE OF STATUS ON WANGZSTABLE
  REFERENCING OLD AS OLDROW NEW AS NEWROW
  FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    update wangzsTable set UPDATETIME=CURRENT TIMESTAMP where id=NEWROW.id;
END;

--insert
DROP TRIGGER "WANGZS_INSERT";

CREATE TRIGGER WANGZS_INSERT
  AFTER INSERT ON WANGZSTABLE
  REFERENCING  NEW AS NEWROW
  FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    update wangzsTable set CREATETIME=CURRENT TIMESTAMP where id=NEWROW.id;
END;


Tag标签: 触发器   例子  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规