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;
