Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write simple trigger witch should be fired out on some conditionals

    Hi,
    on db2 v8.2 fp11 on Linux I would like to create trigger.

    I have a table admin.tab with two columns col1 and col2. When col1 is updated to value '1', col2 must update to value 'A'. When col1 is updated to any other value, col2 must not be updated. But If col1 updates to value '1' and in the same time col2 is also updated then trigger should not be fired unless col2 is null.

    Sample:
    CREATE TABLE ADMIN.TAB (COL1 CHAR(1), COL2 CHAR(1)) @
    INSERT INTO ADMIN.TAB VALUES ('0','0') @

    UPDATE ADMIN.TAB SET COL1='1' @ ==> trigger must be executed and col2 must be updated to 'A'
    UPDATE ADMIN.TAB SET COL1='1', COL2=NULL @ ==> trigger must be executed and col2 must be updated to 'A'
    UPDATE ADMIN.TAB SET COL1='1', COL2='XXX' @ ==> trigger must NOT be executed

    UPDATE ADMIN.TAB SET COL1='2' @ ==> trigger must NOT be executed because col1 <> '1'
    UPDATE ADMIN.TAB SET COL1='2', COL2=NULL @ ==> trigger must NOT be executed because col1 <> '1'
    UPDATE ADMIN.TAB SET COL1='2', COL2='XXX' @ ==> trigger must NOT be executed because col1 <> '1'

    I have written so far the following code, but it is not complete. Can you please help me out and write the code to solve my problem.
    CREATE TRIGGER ADMIN.TAB_UPDATE
    BEFORE UPDATE OF COL1 ON ADMIN.TAB
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN ATOMIC
    SET NEW.COL2 =
    CASE
    WHEN NEW.COL1 ='1' THEN 'A'
    END
    ;
    END @

    Thanks,
    Grofaty
    Last edited by grofaty; 10-15-08 at 05:31.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think this covers what you want.

    CREATE TRIGGER ADMIN.TAB_UPDATE
    BEFORE UPDATE OF COL1 ON ADMIN.TAB
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN ATOMIC
    IF new.col1='1'
    THEN if new.col2 is null or new.col2 = old.col2
    then set new.col2 = 'A';
    END IF;
    END IF;
    END @

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    thanks a lot. It works fine.
    Grofaty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •