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
