473,373 Members | 1,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,373 software developers and data experts.

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 2818
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
4
by: bmccollum | last post by:
I have written a trigger that's supposed to go out and delete corresponding records from multiple tables once I delete a specific record from a table called tblAdmissions. This does not work and...
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
12
by: Bob Stearns | last post by:
I am trying to create a duplicate prevention trigger: CREATE TRIGGER is3.ard_u_unique BEFORE UPDATE OF act_recov_date ON is3.flushes REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN...
4
by: Naeem Bari | last post by:
Hi, I am using postgres 7.4.5 on Redhat Enterprise Linux 3. My background is really on Oracle, and I am porting a largish database over to postgres. Here is my problem: On oracle, I...
1
by: Bill | last post by:
Does anyone know how to fix this problem? On form submittal I'm getting the following error in Visual Studio 2005: The state information is invalid for this page and might be corrupted....
2
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is...
2
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL parent_sample_id int NOT NULL ....lots of other...
1
by: veasnamuch | last post by:
I have a problem while I create a trigger to my table. My objective is getting any change made to my table and record it in to another table . My have thousands records before I add new trigger to...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.