You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Williamson, Nick" <ni...@mentor.com> on 2008/01/10 12:37:16 UTC
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
RE: Trigger question
Posted by "Williamson, Nick" <ni...@mentor.com>.
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.
Regards,
Nick
-----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;
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
Re: Trigger question
Posted by Kristian Waagan <Kr...@Sun.COM>.
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
RE: Trigger question
Posted by "Williamson, Nick" <ni...@mentor.com>.
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...)
-----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