I have some issues with triggers; generally I like the SQL Triggers in
Derby very much, however, I am stuck now: I want to add two triggers to
a table, that automatically set a timestamp, when a record is inserted
and when it is updated.
the insert part works fine, however I fail with the update part. I try
somthing like this:
create trigger tablename_timestamp_updated_at
after update on tablename
referencing new as changed_entry
for each row mode db2sql
update tablename set changed=current_timestamp where id=changed_entry.id;
the problem now is: when the trigger is "after insert" something like
this works fine, however, when it is "after update" the whole thing
crashes, because the update IN the trigger appears to call the same
trigger again and then... after 16 inserts the database throws an exception.
I also tried a before trigger, but this is not allowed with updates...
where is my misunderstanding?
thank you very much!!
Alex
