Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Unanswered: Trigger: Which columns are changed?

    Hello Forum,

    I want to write an Update Trigger. How can I know which columns were updated? Example: A Table Prices with 3 columns: Pruduct, Price, Dozen_price.

    There are 2 cases:
    1. A SQL Update Stmnt changes the price.
    -> In this case the Dozen_price should be updated too. In a way that the Price/Dozen_price ratio stays like before.
    2. A SQL Update Stmnt changes both prices, or only the dozent price or none of the prices. In this case the Trigger should do nothing.

    My Trigger:
    Code:
    CREATE TRIGGER abc.triggerx
        NO CASCADE BEFORE UPDATE ON abc.Prices
        REFERENCING NEW AS N OLD AS O
        FOR EACH ROW MODE DB2SQL
        WHEN ((N.price!=O.price) AND (N.dozen_price IS NULL))
        SET N.dozen_price=123;
    -- This Trigger never fires, because N.dozen_price is never NULL.
    I have tested with: UPDATE abc.prices SET Price = 110 WHERE Product = 'qqqqq';


    Thanks

    Peter

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try something like this:

    Code:
    CREATE TRIGGER abc.triggerx
        NO CASCADE BEFORE UPDATE ON abc.Prices
        REFERENCING NEW AS N OLD AS O
        FOR EACH ROW MODE DB2SQL
        IF (N.price <> O.price) 
           THEN IF (n.dozen_price = o.dozen_price)
               THEN SET n.dozen_price = 123;
           END IF;
        END IF;
    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    4
    Ok. But what happens if the user only wants to reduce the single price?
    Old Values: "Egg; 1$, 11$"

    How can I distinguish this Stmts:
    UPDATE abc.prices SET Price = 0.99, Price_dozen = 11 WHERE Product = 'Egg';
    UPDATE abc.prices SET Price = 0.99 WHERE Product = 'Egg';

    In the first case the user sets both values, the trigger should do nothing. But your Trigger activates. In the second case the dozen price should be updated by the trigger.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    My trigger will always "activate" on any update, no matter what. It will only set dozen_price if price has changed and dozen_price has not.

    Andy

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    a good reference is the IBM Redbook DB2 for z/OS, Application Programming Topics.
    Chapter 3 deals with triggers.
    I would suggest that your CREATE include the columns which concern you (example 3-8) so that your trigger is only activated when one of the three columns is modified.

  6. #6
    Join Date
    Jan 2010
    Posts
    4
    @dbzTHEdinosaur: Ok, the trigger activates if one of the columns is changed. But how can I say "activate only if col1 is changed, but col2 is untouched".

    I'm using the " DB2 Administration Tools Environment window" for executing the Create Trigger Stmnts. Andys Trigger will give error Msg for every semicolon in the Trigger Def. Regardless if I enclose it with BEGIN or BEGIN ATOMIC. So I changed my stmt to:
    Code:
    CREATE TRIGGER abc.triggerx
        NO CASCADE BEFORE UPDATE ON abc.Prices
        REFERENCING NEW AS N OLD AS O
        FOR EACH ROW MODE DB2SQL
        WHEN ((N.price!=O.price) AND (n.dozen_price = o.dozen_price))
        SET N.dozen_price=O.dozen_price/O.price*N.price;
    The old values are: ("Egg", 1, 11)
    Code:
    UPDATE abc.prices SET Price = 0.99, Price_dozen = 11 WHERE Product = 'Egg';
    If I execute the Stmnt above the dozen Price is not 11 after executing, but it should be because this is what the user wants. On the other hand if I execute this:
    Code:
    UPDATE abc.prices SET Price = 0.99 WHERE Product = 'Egg';
    The values should be: ("Egg", 0.99, 10.89)
    In this case, the trigger works correctly.
    Last edited by peterfarge; 01-21-10 at 13:26.

  7. #7
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    again, take a look at chapter 3 of the manual I hyperlinked in my prior post.

    you can not perform an UPDATE in a BEFORE Trigger, only SET

    and as far as determining what columns have been changed:
    even if you use
    BEFORE UPDATE OF COL1 ON abc.Prices

    the trigger will be activated when
    COL1 is changed and COL2 is not
    and
    COL1 is changed and COL2 is changed

    that is where you need the
    WHEN ....
    SET;

    the BEGIN ATOMIC and END are used to enclose SQL Statements like UPDATE, etc... which you can not use in a BEFORE trigger, anyway.

    as far as providing you more help than this,
    I can't, because I am still unsure/not clear on your business rules.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by peterfarge View Post
    @dbzTHEdinosaur: Ok, the trigger activates if one of the columns is changed. But how can I say "activate only if col1 is changed, but col2 is untouched".

    I'm using the " DB2 Administration Tools Environment window" for executing the Create Trigger Stmnts. Andys Trigger will give error Msg for every semicolon in the Trigger Def. Regardless if I enclose it with BEGIN or BEGIN ATOMIC. So I changed my stmt to:
    Code:
    CREATE TRIGGER abc.triggerx
        NO CASCADE BEFORE UPDATE ON abc.Prices
        REFERENCING NEW AS N OLD AS O
        FOR EACH ROW MODE DB2SQL
        WHEN ((N.price!=O.price) AND (n.dozen_price = o.dozen_price))
        SET N.dozen_price=O.dozen_price/O.price*N.price;
    The old values are: ("Egg", 1, 11)
    Code:
    UPDATE abc.prices SET Price = 0.99, Price_dozen = 11 WHERE Product = 'Egg';
    If I execute the Stmnt above the dozen Price is not 11 after executing, but it should be because this is what the user wants. On the other hand if I execute this:
    Code:
    UPDATE abc.prices SET Price = 0.99 WHERE Product = 'Egg';
    The values should be: ("Egg", 0.99, 10.89)
    In this case, the trigger works correctly.
    I do not think you can do it. There is no way to tell the difference between not updating the dozen_price or updating it to the current value.

    Andy

  9. #9
    Join Date
    Jan 2010
    Posts
    4
    I have changed my mind and put the logic into the program.

    Thanks

    Peter

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    it is possible to achieve it using two triggers .. Not that I say it is a good idea ;-)

    Code:
    CREATE TRIGGER abc.triggerb1 -- create this trigger first
        NO CASCADE BEFORE UPDATE of price ON abc.Prices 
        REFERENCING NEW AS N OLD AS O
        FOR EACH ROW MODE DB2SQL
        WHEN ((N.price!=O.price) AND (n.dozen_price = o.dozen_price) )  
        SET N.dozen_price=O.dozen_price/O.price*N.price;
     
     
    CREATE TRIGGER abc.triggerb2 
        NO CASCADE BEFORE UPDATE of price,dozen_price ON abc.Prices 
        REFERENCING NEW AS N OLD AS O
        FOR EACH ROW MODE DB2SQL
        -- add any WHEN condition if required
        SET n.dozen_price=o.dozen_price ;  -- if both cols have changed, then unset the new value of dozen_price
    Last edited by sathyaram_s; 01-26-10 at 05:23.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sathyaram,
    I think that will almost work. The second trigger needs to change slightly for the possibility if the update statement specifies both price and dozen_price but the value for price is the same as the persisted value (unchanged).

    Code:
    CREATE TRIGGER abc.triggerb2 
        NO CASCADE BEFORE UPDATE of price,dozen_price ON abc.Prices 
        REFERENCING NEW AS N OLD AS O
        FOR EACH ROW MODE DB2SQL
        -- add any WHEN condition if required
        WHEN (N.price!=O.price) 
        SET n.dozen_price=o.dozen_price ;  -- if both cols have changed, then unset the new value of dozen_price
    Andy

Posting Permissions

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