469,277 Members | 2,085 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

Trigger porting problem

Hi :-)

I'm porting a project from Oracle to DB2 and now I'm trying to avoid
error SQL0746N in a trigger which reads the same table in which the
trigger is defined.

Below is Oracle's workaround for this problem (Oracle raises a similar
error when trying to do this). It uses a package to temporarily store
affected rows in a BEFORE trigger for later processing in an AFTER
trigger. Is there a functionality in DB2 similar to Oracle's packages
(sort of arrays

-- SUPPORTING PACKAGE
create or replace package myTable_pkg as
....type rarray is table of rowid index by binary_integer;
....rids rarray;
....type rMyField1 is table of myType1 index by binary_integer;
....myField1 rMyField1;
....type rMyField2 is table of myType2 index by binary_integer;
....myField1 rMyField2;
.......
.......
....cnt number;
end;
-- INITIALIZE COUNTER BEFORE UPDATE
create or replace trigger myTable_bu before update on myTable
begin
....myTable.cnt := 0;
end;
-- POPULATE ARRAY FOR EACH UPDATED ROW
create or replace trigger myTable_aufer after update on myTable
for each row
begin
....myTable.cnt := myTable.cnt + 1;
....myTable.rids(myTable.cnt) := :new.rowid;
....myTable.myField1(myTable.cnt) := :old.myField1;
....myTable.myField2(myTable.cnt) := :old.myField2;
.......
.......
end;
-- DO ACTIONS FOR EACH UPDATED ROW AFTER UPDATE
create or replace trigger myTable_au after update on myTable
declare
....myRow myTable%rowtype;
begin
....for i in 1 .. myTable.cnt loop
.......select * into myRow from myTable where rowid = myTable.rids(i);
.......procWhichReadsMyTable(myRow.myField1, myRow.myField2, ...);
....end loop;
end;

Nov 12 '05 #1
2 2651
gu*************@yahoo.com.ar wrote:
Hi :-)

I'm porting a project from Oracle to DB2 and now I'm trying to avoid
error SQL0746N in a trigger which reads the same table in which the
trigger is defined.

Below is Oracle's workaround for this problem (Oracle raises a similar
error when trying to do this). It uses a package to temporarily store
affected rows in a BEFORE trigger for later processing in an AFTER
trigger. Is there a functionality in DB2 similar to Oracle's packages
(sort of arrays

-- SUPPORTING PACKAGE
create or replace package myTable_pkg as
...type rarray is table of rowid index by binary_integer;
...rids rarray;
...type rMyField1 is table of myType1 index by binary_integer;
...myField1 rMyField1;
...type rMyField2 is table of myType2 index by binary_integer;
...myField1 rMyField2;
......
......
...cnt number;
end;
-- INITIALIZE COUNTER BEFORE UPDATE
create or replace trigger myTable_bu before update on myTable
begin
...myTable.cnt := 0;
end;
-- POPULATE ARRAY FOR EACH UPDATED ROW
create or replace trigger myTable_aufer after update on myTable
for each row
begin
...myTable.cnt := myTable.cnt + 1;
...myTable.rids(myTable.cnt) := :new.rowid;
...myTable.myField1(myTable.cnt) := :old.myField1;
...myTable.myField2(myTable.cnt) := :old.myField2;
......
......
end;
-- DO ACTIONS FOR EACH UPDATED ROW AFTER UPDATE
create or replace trigger myTable_au after update on myTable
declare
...myRow myTable%rowtype;
begin
...for i in 1 .. myTable.cnt loop
......select * into myRow from myTable where rowid = myTable.rids(i);
......procWhichReadsMyTable(myRow.myField1, myRow.myField2, ...);
...end loop;
end;


Gustavo,

Two options:
1. Try to perform the read inside the trigger instead of inside the
procedure. The trigger itself can deal with these conflicts thanks to
inline SQL PL.
2. Open a PMR. You are not alone with this problem (in fact it was
debated in this group mere weeks ago) and DB2 Development is much more
interested in fixing this one for real than teetering along with
workarounds. We clearly underestimated the likelyhood of mutating table
conflicts.
When you open the PMR. Ask support to get touch with me.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
Serge,

Thanks for the response. I'll open a PMR.

-----------------------------------
Lic. Gustavo J. Randich
Tecnología Informática
ARDISON Software & Consulting
www.ardison.com

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Martin | last post: by
12 posts views Thread by Bob Stearns | last post: by
1 post views Thread by Bill | last post: by
2 posts views Thread by mob1012 via DBMonster.com | last post: by
2 posts views Thread by dean.cochrane | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.