db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexander Schatten <ala...@gmx.at>
Subject Update Trigger Issues
Date Sun, 03 Dec 2006 15:49:27 GMT
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!!


View raw message