By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,277 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,277 IT Pros & Developers. It's quick & easy.

Reading triggering table data in trigger (without error SQL0746N)

P: n/a
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...

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.