Results 1 to 4 of 4

Thread: DB2 trigger

  1. #1
    Join Date
    May 2002
    Posts
    12

    Unanswered: DB2 trigger

    If i want to write a trigger that automatically insert or update a row into table B after an update or insert occur in table A, can write them in one trigger or i have to write them in 2 seperate trigger (one trigger after update, and the other one after insert). Please show me how to write this trigger. Thank you very much for your help.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 trigger

    Two seperate triggers ...

    Chapter 16 in Application Development guide expalins Trigger usage ...

    If you do not have a soft copy of the book already , download it from

    http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main


    Cheers

    Sathyaram

    Originally posted by xauxi
    If i want to write a trigger that automatically insert or update a row into table B after an update or insert occur in table A, can write them in one trigger or i have to write them in 2 seperate trigger (one trigger after update, and the other one after insert). Please show me how to write this trigger. Thank you very much for your help.

  3. #3
    Join Date
    Oct 2001
    Posts
    68
    Ask, and you shall receive. We have the same scenario to record when changes are made to base tables (A) by recording information in log tables (B). Here are the two triggers and source. Some names have been changed to protect the innocent.

    Code:
    -- Drop trigger TRIGGER1;
       Create Trigger TRIGGER1
           After Insert on  TABLEA
           Referencing New As NewData
             For Each Row Mode DB2SQL
               Insert Into TABLEB   (
                     COL1 -- In TABLEB
                    ,COL2 -- In TABLEB
                    ,etc...
                     )
               Values
                     (
                  NewData.COL1 -- From TABLEA
                 ,NewData.COL2 -- From TABLEA
                 ,'INSERT' -- Constant
                 ,Substr(USER,1,8)
                     );
    
    --  **  Row Update Trigger **
    
    -- Drop Trigger TRIGGER2;
       Create Trigger TRIGGER2
         After Update on  TABLEA
           Referencing Old As OldData
             For Each Row Mode DB2SQL
               Insert Into TABLEB  (
                     COL1 -- In TABLEB
                    ,COL2 -- In TABLEB
                    ,etc...
                     )
               Values
                     (
                  OldData.COL1 -- From TABLEA
                 ,OldData.COL2 -- From TABLEA
                 ,'UPDATE' -- Constant
                 ,Substr(USER,1,8)
                     );
    Hope this helps you. Remember to replace TRIGGERn,TABLEx,COLn with your own values and remove the "--" comments.

    WP

  4. #4
    Join Date
    May 2002
    Posts
    12
    Thank you very much for your help. I'd appreciate it.

Posting Permissions

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