DB2 Triggers

[sjc327]

Hi,

My question is can I have multiple WHEN conditions in one trigger? I'm using DB2 for Z/OS v7. I want to take three different actions based on the value that is updated in one column. For example:
IF col1 is updated to "A" then do action1
IF col1 is updated to "I" then do action2
IF col1 is updated to "D" then do action3

It looks like from the syntax diagram for the CREATE TRIGGER statement that the WHEN contition cannot be repeated and I can't seem to get it to work. If this is true then I would have to define 3 separate triggers to accomplish this?

Thanks,
Steve

 

[MarcLodge]

Hi Steve,
I think you have to include a CASE statement eg.

CREATE TRIGGER PassFail
AFTER INSERT ON db2cert.test_taken
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
UPDATE db2cert.test_taken
SET PASS_FAIL =
CASE
WHEN n.score >=
(SELECT cut_score FROM db2cert.test
WHERE number = n.number)
THEN'P'
WHEN n.score <
(SELECT cut_score FROM db2cert.test
WHERE number = n.number)
THEN'F'
END
WHERE n.cid = cid
AND n.tcid = tcid
AND n.number = number
AND n.date_taken = date_taken

You'll find more info on this here:

http://www.informit.com/articles/article.asp?p=31095

Hope this helps

Marc