Trigger: Which columns are changed?

peterfarge · #1 (**permalink**) 01-21-10, 10:21

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

ARWinner · #2 (**permalink**) 01-21-10, 10:57

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

peterfarge · #3 (**permalink**) 01-21-10, 11:28

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.

ARWinner · #4 (**permalink**) 01-21-10, 11:32

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

dbzTHEdinosaur · #5 (**permalink**) 01-21-10, 11:33

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.

peterfarge · #6 (**permalink**) 01-21-10, 12:12

@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.

dbzTHEdinosaur · #7 (**permalink**) 01-21-10, 13:28

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.

ARWinner · #8 (**permalink**) 01-21-10, 13:45

Quote:

Originally Posted by peterfarge

@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

peterfarge · #9 (**permalink**) 01-22-10, 06:06

I have changed my mind and put the logic into the program.

Thanks

Peter

sathyaram_s · #10 (**permalink**) 01-25-10, 20:10

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

ARWinner · #11 (**permalink**) 01-26-10, 08:12

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

Internet Services

Web Development

Digital Marketing

Consumer Tech