db2触发器例子

-- 建表语句
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;