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

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

Hi,
thanks a lot. It works fine.
Grofaty