Hello,
I'm looking for a DB2 workaround on a topic already solved in Oracle:
the problem of mutating tables (which states that a trigger action
cannot read the triggering table's data). Yes, I know the trivial
answer: "just pass the triggering table's data as parameters to the
stored procedures called in the triggers, etc."; but it's a long story
to tell why I can't.
Below is the mentioned workaround's template I'm using for an UPDATE
trigger in Oracle (basically, it uses a package to temporarily store
affected rows for later processing):
-- 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 number(18,2) index by binary_integer;
myField1 rMyField1;
....
....
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;
Thank you in advance...