db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Trigger question
Date Thu, 10 Jan 2008 12:09:11 GMT
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;
CREATE TRIGGER t1_trig1 AFTER UPDATE OF c1 ON t1 ...

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



-- 
Kristian

> 
> 
> 
> 
> -----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:
>  
> CREATE TRIGGER t1_trig1 AFTER CREATE ON t1
>   referencing new as new
> FOR EACH ROW MODE DB2SQL
>   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 that
> value in t1.c2.   t1.pk_column is the primary key on t1, so I use it to
> 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?
> 
> TIA
> Nick


Mime
View raw message