Usage of mainframe DB2 trigger


General Trigger Creation Syntax

$ cat create_trigger.sql

--#SET TERMINATOR #
DROP TRIGGER   <TRIG_NAME>#

CREATE TRIGGER <TRIG_NAME>
  AFTER [UPDATE|INSERT|DELETE] ON <TABLENAME>
  REFERENCING [NEW|NEW_TABLE] AS N
              [OLD|OLD_TABLE] AS O
  FOR EACH [ROW|STATEMENT] MODE DB2SQL
BEGIN ATOMIC
     {triggered-SQL-statement}
END#

$ db2 -f create_trigger.sql

Details of syntax please refer to IBM document


About Trigger Event Clause: [INSERT|UPDATE|DELETE]

Only one type could be provided, so if you want to listen on all modification types, you must create 3 triggers, one for INSERT, one for UPDATE, and one for DELETE.

About Transition Variables [NEW|NEW_TABLE|OLD|OLD_TABLE]

NEW|NEW_TABLE could only be used with INSERT and UPDATE trigger-events.

OLD|OLD_TABLE could only be used with UPDATE and DELETE trigger-events.

NEW and OLD could only be used with FOR EACH ROW.

See following tables for details:

GranularityActivation TimeTriggering SQL Operation

Transition Variables Allowe

Transition Tables Allowed
FOR EACH ROWBEFOREINSERTNEW-
  UPDATEOLD, NEW-
  DELETEOLD-
 AFTERINSERTNEWNEW TABLE
  UPDATEOLD, NEWOLD TABLE, NEW TABLE
  DELETEOLDOLD TABLE
OR EACH STATEMENTBEFOREINSERT--
  UPDATE--
  DELETE--
 AFTERINSERT-NEW TABLE
  UPDATE-OLD TABLE, NEW TABLE
  DELETE-OLD TABLE

NEW/OLD with "FOR EACH ROW"

  • NEW means the new values of current INSERT or UPDATE row.
  • OLD means the exact old value of current UPDATE or DELETE row.

NEW_TABLE/OLD_TABLE with "FOR EACH STATEMENT"

  • NEW_TABLE means all the new changed rows of current INSERT or UPDATE statement affected.
  • OLD_TABLE means all the new changed rows of current UPDATE or DELETE statement affected.

OLD_TABLE/NEW_TABLE with "FOR EACH ROW"

Usually when "FOR EACH ROW", you should refer to NEW and OLD, not NEW_TABLE, and OLD_TABLE; this is reasonable cases; but since DB2 provides the transition variable NEW_TABLE and OLD_TABLE with "FOR EACH ROW", you can also use them of course; however, they must be used carefully, because where OLD_TABLE and NEW_TABLE means all the changed rows, not the current NEW and OLD; for example: if you create a trigger as:

CREATE TRIGGER TESTTRIGGER
  AFTER UPDATE ON TESTTAB
  REFERENCING NEW_TABLE AS N
              OLD_TABLE AS O

  FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
              {refer to N and O}
END
And if an UPDATE statement update 10 rows data, this trigger will be fired 10 times, each time the transition variable NEW_TABLE and OLD_TABLE will contain 10 data rows.

About {triggered-SQL-statement}

A trigger body could include only SQL statements and built-in functions. So in a trigger body:
1.      You cannot declare local variable
2.      You cannot execute control statement, like IF, WHILE, etc
So if you want the trigger to perform actions or use logic that is not available in SQL statements or built-in functions, you need to write a stored procedure or a user defined function, and invoke it from the trigger body.

 This is very bad user experience.

To invoke a user defined function using VALUES statement, like
VALUES YOUR_FUNCTION(N.EMPNO,O.SALARY,N.SALARY);

To invoke a stored procedure using CALL statement, like
CALL YOUR_PROCEDURE (N.EMPNO,O.SALARY,N.SALARY);

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值