Re: Update Trigger in IDS 7.3

Madan Mashalkar wrote: > >Hi, > >Environment : IDS 7.30UC3 , Sun Solaris >This is a question about update triggers. > >There is a simple trigger: > create trigger upd_tab1 update of col2 on tab1 > referencing old as pre new as post > for each row when (pre.col2 != post.col2) > (insert into tab2 (col1,col2) values ('GOT' ,'IT!' ));> >Then inserting and updating a row in an empty table tab1: > > insert into tab1 (col1,col2) values('TEST',null); > update tab1 set col2 = 'NEW' where col1 = 'TEST';> >No rows are inserted in tab2. >(Interesting, if either the pre or the post value is NULL, > no rows are inserted in tab2) > >The manual states that if the 'when' evaluates to false/unknown, then >the action(insert) will not be done. > >So, does (pre.col2 != post.col2) evaluate to unknown ??? >If yes, then is it expected behaviour ? > >Confused , and awaiting clarifications... Of course it is unknown when col2 is null or col2 is null. Try this: create trigger upd_tab1 update of col2 on tab1 referencing old as pre new as post for each row when (pre.col2 != post.col2 or (pre.col2 IS NULL and post.col2 IS NOT NULL) or (pre.col2 IS NOT NULL and post.col2 IS NULL)) (insert into tab2 (col1,col2) values ('GOT' ,'IT!' )); Best Regards, Octav -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 20 96 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com