Update Trigger Issues

[Alexander Schatten <al...@gmx.at>]

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

Re: Update Trigger Issues

[Rajesh Kartha <ka...@gmail.com>]

Hello Alex,

Your update trigger condition is on the entire table which will result 
in self-triggers firing repeatedly on the same table.  Derby does
not allow that,m there is an existing issue logged for that - 
http://issues.apache.org/jira/browse/DERBY-1261

Hopefully this  helps, try referencing selected columns in the update 
trigger and that should work. Here is an example:

connect 'jdbc:derby:trigdb';
drop table t1;
create table t1(i int, j char(2), changed timestamp);
create trigger t1_ts_insert after insert on t1 referencing new as ANEW 
for each row mode db2sql update t1 set changed=current_timestamp where 
i=ANEW.i;
insert into t1(i,j) values(1,'aa');
select * from t1;
--
--Update trigger referencing cols i and j of table t1
--
create trigger t1_ts_update after update of i,j on t1 referencing new as 
ANEW for each row mode db2sql update t1 set changed=current_timestamp 
where i=ANEW.i;
insert into t1(i,j) values(2,'cc');
select * from t1;
update t1 set i=3 where i=2;
select * from t1;
disconnect;

Regards,
Rajesh

Alexander Schatten wrote:

> 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
>