I would like to set up a trigger that updates a field in the row modified
with a timestamp.

The table (linkitad) looks as follow

lktyp char(1)
lkitem char(12)
lkadcode char(10)
as primary key
lkmoddat timestamp to receive the timestamp of the modification by the
trigger

let say 2 fields I want to "observe" the modification
field1, field2..

and some other fields I do not want to observe.

I wrote the following sql

create trigger dba.zvf_linkitad after update of field1, field2
order 1 on DBA.linkitad
referencing old as old_data
for each row
begin
update linkitad set lkmoddat = now(*) where lktyp = old.lktyp and lkitem =
old.lkitem and lkadcode = old.lkadcode
end;

I can run it with no error, but when I change one of the 'observed' fields,
nothing happens ( no mod to lkmoddat, no error reported )

Any idea of the error in the trigger SQL ?

Next time please mention SA version.

Couple problems with your trigger.

- you say 'create trigger dba.zvf_linkitad after update of field1,
field2... ' but field1, field2 are not defined on your table, these should
be valid columns' names
- you have defined 'referencing old as 'old_data' but in your trigger you
are referring to 'old' in your WHERE clause
- you are joining 'new' and 'old' with a WHERE clause on 3 columns assuming
that this will give equality , but if any of these 3 columns is updated
'new' will never be equal to 'old'

Try following:

create trigger dba.zvf_linkitad after update of lktyp, lkitem
order 1 on DBA.linkitad
referencing old as old_data
for each row
begin
update linkitad set lkmoddat = now(*) where old_data.lkadcode = lkadcode
end;

BTW. it would be better to have a default (current timestamp) on lkmoddat,
so then when row is inserted you also have record of that.

Check your dbisql Options -> 'On error' , you may have it set to 'Continue'
which would ignore errors, but you should still be able to see SQLCode <0.

Lucjan









"Claude" <c.verstichel (AT) gmail (DOT) com> wrote

Your second point was the problem. Thank you.
Thank you as well for your other comment.
Claude

"Lucjan Chmura [Sybase iAnywhere]" <lchmura_at_sybase_dot_com> a écrit dans
le message de news: 4aca29d6$1 (AT) forums-1-dub (DOT) ..