db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <karth...@gmail.com>
Subject Re: Update Trigger Issues
Date Mon, 04 Dec 2006 04:52:45 GMT
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
>


Mime
View raw message