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 Trigger question
Date Thu, 10 Jan 2008 11:37:16 GMT
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