在DB2中，更新和插入是否可能只有一个触发器？

ddhy6vgd 于 2022-11-07 发布在 DB2

关注(0)|答案(4)|浏览(174)

我必须创建一个或多个触发器来保持对我的表的审计。该触发器应该在插入和更新时执行。
目前我有两个触发器

一个用于插入：

CREATE TRIGGER SCH.TRG_TBL1_AFT_I
  AFTER INSERT ON SCH.TBL1  
  REFERENCING
    NEW AS n
  FOR EACH ROW
 MODE DB2SQL
INSERT INTO SCH.TBL1_AUDIT
VALUES( .. ,, .. );

另一个用于更新

CREATE TRIGGER SCH.TRG_TBL1_AFT_U
  AFTER UPDATE ON SCH.TBL1  
  REFERENCING
    NEW AS n
  FOR EACH ROW
 MODE DB2SQL
INSERT INTO SCH.TBL1_AUDIT
VALUES( .. ,, .. );

但问题是，是否有可能在DB2中创建一个触发器来执行任务？[假设两个触发器都执行相同的操作。]

db2

来源：https://stackoverflow.com/questions/1011673/in-db2-is-it-possible-to-have-just-a-single-trigger-for-both-update-and-insert

4条答案

按热度按时间

uubf1zoe1#

请尝试此功能，此功能自9.7.x版起就已可用。

CREATE or replace TRIGGER PASSENGER_TR01_BEFORE_IUD
 BEFORE 
    DELETE
 OR UPDATE OF FIRST_NAME 
 OR INSERT ON PASSENGER
REFERENCING 
OLD AS oldRow
NEW AS newRow
FOR EACH ROW
WHEN (1=1) 
Begin
Declare ACTION Char(1) Default '';
-- Use Case/When to inquire trigger-event
Case 
When INSERTING Then
    Set ACTION='I';
When UPDATING  Then
    Set ACTION='U';    
When DELETING  Then
    Set ACTION='D';    
Else
    Set ACTION='N';    
End Case;

-- Use If/Then/Else to inquire trigger-event
If INSERTING Then
    Set ACTION='I';
ElseIf UPDATING  Then
    Set ACTION='U';
ElseIf DELETING  Then
    Set ACTION='D';
Else
    Set ACTION='N';    
End If;
End

赞(0）回复(0）举报 2022-11-07

ufj5ltwl2#

是的，这是可能的。See the documentation of create trigger。这里是一个粘贴：

trigger-event

   .-OR--------------------------------------.   
   V                                     (4) |   
|----+-INSERT--------------------------+-----+------------------|
     +-DELETE--------------------------+         
     '-UPDATE--+---------------------+-'         
               |     .-,-----------. |           
               |     V             | |           
               '-OF----column-name-+-'

这样你就可以说：
create trigger blah before insert on blah or update of blah .

赞(0）回复(0）举报 2022-11-07

bt1cpqcv3#

You can find more info here

CREATE OR REPLACE TRIGGER SET_SALARY
 NO CASCADE
 BEFORE UPDATE OR INSERT ON employee
 REFERENCING NEW AS n OLD AS o
 FOR EACH ROW
 WHEN( o.edlevel IS NULL OR n.edlevel > o.edlevel )
 BEGIN
    -- Give 10% raise to existing employees with new education level.
 IF UPDATING THEN SET n.salary = n.salary * 1.1;
     -- Give starting salary based on education level.
 ELSEIF INSERTING THEN
     SET n.salary = CASE n.edlevel WHEN 18 THEN 50000
  WHEN 16 THEN 40000
         ELSE 25000
  END;
  END IF;
 END

赞(0）回复(0）举报 2022-11-07

djmepvbi4#

对不起，DB2没有提供将更新和插入触发器组合在一起的方法。

赞(0）回复(0）举报 2022-11-07