db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Williamson, Nick" <nick_william...@mentor.com>
Subject RE: Trigger question
Date Thu, 10 Jan 2008 14:00:55 GMT
Thanks Christian. I'm sure that limiting the trigger to fire only on the
update of certain columns, as you suggest, will fix the recursion
problem. I need to monitor changes to c1 and then update c2 accordingly,
so firing only on update of c1 should fix it. 


-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM] 
Sent: 10 January 2008 12:09
To: Derby Discussion
Subject: Re: Trigger question

Williamson, Nick wrote:
> Sorry... it's obviously a create trigger in the example, although I 
> referred to an update trigger (I've been experimenting with both 
> insert and update triggers, and pasted the wrong one in to the 
> mail...)

Hi Nick,

If you can get away with update triggers (instead of insert), does it
help you to specify a subset of the columns in the table in the trigger?

For instance, something like;

That way, the trigger won't fire again. However, I must admit I haven't
taken the time to fully understand how many different triggers you need.
I fear that you might end up in the situation where you still get a
ping-pong effect, i.e. the actions of one trigger fires another trigger
and so on. Maybe you can get creative with procedures or a helper table,
I don't know how much room you have for changes in your app and how
important performance is in this case. The trick above should at least
make the updates work in one direction.

This is just a shot from the hip...


> -----Original Message-----
> From: Williamson, Nick
> Sent: 10 January 2008 11:37
> To: Derby Discussion
> Subject: Trigger question
> Hi all,
> I'm migrating an Oracle schema to Derby. I have a number of tables 
> that are read and written by two different apps. Let's say that one 
> such table has columns c1 and c2 and one application will only 
> populate column c1, and the other will only populate column c2. In 
> order to make records created in the first app visible / usable to the

> other app, both columns have to be populated and so in the Oracle DB, 
> I have PL/SQL triggers that fire on insert and update, and they fill 
> in the gaps when one of the apps writes to these tables. It's usually 
> the case that c1 will hold the UID of another record in the database, 
> and c2 will hold the value of another column in the same "other" 
> record. So... if the first app creates a row in the table and 
> populates c1 with a UID, I can go looking for the other record by it's

> UID, I can get the value of the other column and I can go back to the 
> new row and populate the c2 column with it.
> I've been trying stuff like this in Derby to replicate what I have in
> Oracle:
>   referencing new as new
>   UPDATE t1 SET c2 = (SELECT c2 FROM t2 WHERE t2.c1=NEW.c1)
>   WHERE t1.pk_column = NEW.pk_column);
> In this example, I have an update trigger on the t1 table. When a user

> adds a row to t1 and specifies a value for t1.c1 - which is a UID - I 
> go off to the t2 table and find the record there that has that UID as 
> it's primary key. Having found it, I get the value of t2.c2 and put
> value in t1.c2.   t1.pk_column is the primary key on t1, so I use it
> find and update the record for which the trigger is firing.
> Now, here's the thing. I'm unable - so far - to do this in Derby 
> because I need to update the row for which the trigger is firing. If I

> use a BEFORE trigger then I'm not allowed to have an UPDATE in the 
> trigger, but if I use an AFTER trigger, I can't specify NO CASCADE and

> the update statement in the trigger causes the trigger to fire again 
> and I end up exceeding the maximum permissible trigger recursion!
> Putting to one side all the stuff about the wrongs of the table and 
> application design, is it possible to do what I want to do? In other 
> words, can I get a trigger to update columns in the row for which the 
> trigger is firing?
> Nick

View raw message