473,396 Members | 1,971 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,396 software developers and data experts.

Trigger problem DB2LUW

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 cols...)

DELETEs on all tables are handled by the front end code, which just
issues a DELETE on the row in question, and DELETE of child rows are
handled by a trigger:

CREATE TRIGGER DELETE_SAMPLE
AFTER DELETE ON sample_table
REFERENCING OLD AS O
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM sample_table WHERE parent_sample_id = o.sample_id;
END;

This works.

However, the new requirement is that there can be three levels in the
hierarchy, and the new trigger code submitted to me by the developer
goes something like this:

DELETE FROM sample_table WHERE sample_id IN
(SELECT sample_id FROM sample_table WHERE parent_sample_id IN
(SELECT sample_id FROM sample_table WHERE parent_sample_id =
O.sample_id)
OR parent_sample_id = O.sample_id)

I cannot use this logic, first because I'm not dead sure that it
works, but second and most importantly because it is hugely expensive,
and performance is unacceptable.

So, I have tried several things, and called them from the trigger:

1. Create a table valued function which returns a list of all child
sample_ids, issue the delete in the trigger. -746, access level
violation.
2. Call a stored procedure, delete all child sample_ids. -746, access
level violation.

I understand both of these errors, although the error with the UDF
surprised me, as I would have expected it to be expanded inline and
thus to share the access level of the calling routine.

So, next I tried:

3. Create a query with a series of self-joins that returns a list of
all child sample_ids. In the trigger, DELETE rows whose sample_ids are
IN this list, like this:

CREATE TRIGGER DELETE_SAMPLE
AFTER DELETE ON sample_table
REFERENCING OLD AS O
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM sample_table WHERE sample_id IN (
select d2.sample_id as sample_id
from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
where d2.sample_id IS NOT NULL
AND d1.sample_id = o.sample_id
UNION ALL
select d3.sample_id as sample_id
from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
left join sample_table d3
on d2.sample_id = d3.parent_sample_id
where d3.sample_id IS NOT NULL
AND d1.sample_id = o.sample_id)

END;

4. Wrap that query in a view, call the view from the trigger, delete
rows whose sample_ids are IN the list, like this:

DELETE FROM sample_table
WHERE sample_id IN (
select sample_id
from vw_sample
where root_sample_id = o.sample_id)

This is the view containing the query:

CREATE VIEW vw_sample AS
select d2.sample_id as sample_id,
d1.sample_id as root_sample_id

from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
where d2.sample_id IS NOT NULL
UNION ALL
select d3.sample_id as sample_id,
d1.sample_id as root_sample_id
from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
left join sample_table d3
on d2.sample_id = d3.parent_sample_id
where d3.sample_id IS NOT NULL

Here is the problem: in both cases 3 and 4, the original row is
deleted, and no other row. It is like the trigger cannot see any data
returned by the either the subquery or the view. There are no errors
generated.

I have of course confirmed that the queries/views return the correct
values, and that if I run the delete as a standalone statement (not
inside the trigger), it works properly.

First, what am I missing? Why doesn't this delete work? This seems
heavily counterintuitive to me.

Second, what are my options? Current constraints are that I cannot
change the data structure, and the delete must use triggers.

Any help at all would be much appreciated.

Apr 19 '07 #1
2 2362
de***********@gmail.com wrote:
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 cols...)

DELETEs on all tables are handled by the front end code, which just
issues a DELETE on the row in question, and DELETE of child rows are
handled by a trigger:

CREATE TRIGGER DELETE_SAMPLE
AFTER DELETE ON sample_table
REFERENCING OLD AS O
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM sample_table WHERE parent_sample_id = o.sample_id;
END;

This works.

However, the new requirement is that there can be three levels in the
hierarchy, and the new trigger code submitted to me by the developer
goes something like this:
OK this is where I get confused...
The trigger above is recursive it should already handle your three
levels. In fact it should handle 16 levels.

Having that said why don't you define an RI constraint with ON DELETE
CASCADE and forget about the trigger.

Which version of DB2 are you on? In DB2 V8 recent fixpack
you can get rid of the -746 by following this
technote:http://tinyurl.com/2j7m9h
On DB2 9 you shouldn't see -746 from CALL in triggers.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 19 '07 #2
if
why not use referential integrity?

create table a(id int not null primary key, parent int);
insert into a(id, parent) values (0 , null), (1, 0), (2,0), (11, 1),
(12,2), (111, 11), (112, 11), (1111, 111);
alter table a add constraint fk foreign key(parent) references a(id)
on delete cascade;
delete from a where id=1;


On Apr 19, 1:29 pm, "dean.cochr...@gmail.com"
<dean.cochr...@gmail.comwrote:
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 cols...)

DELETEs on all tables are handled by the front end code, which just
issues a DELETE on the row in question, and DELETE of child rows are
handled by a trigger:

CREATE TRIGGER DELETE_SAMPLE
AFTER DELETE ON sample_table
REFERENCING OLD AS O
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM sample_table WHERE parent_sample_id = o.sample_id;
END;

This works.

However, the new requirement is that there can be three levels in the
hierarchy, and the new trigger code submitted to me by the developer
goes something like this:

DELETE FROM sample_table WHERE sample_id IN
(SELECT sample_id FROM sample_table WHERE parent_sample_id IN
(SELECT sample_id FROM sample_table WHERE parent_sample_id =
O.sample_id)
OR parent_sample_id = O.sample_id)

I cannot use this logic, first because I'm not dead sure that it
works, but second and most importantly because it is hugely expensive,
and performance is unacceptable.

So, I have tried several things, and called them from the trigger:

1. Create a table valued function which returns a list of all child
sample_ids, issue the delete in the trigger. -746, access level
violation.
2. Call a stored procedure, delete all child sample_ids. -746, access
level violation.

I understand both of these errors, although the error with the UDF
surprised me, as I would have expected it to be expanded inline and
thus to share the access level of the calling routine.

So, next I tried:

3. Create a query with a series of self-joins that returns a list of
all child sample_ids. In the trigger, DELETE rows whose sample_ids are
IN this list, like this:

CREATE TRIGGER DELETE_SAMPLE
AFTER DELETE ON sample_table
REFERENCING OLD AS O
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM sample_table WHERE sample_id IN (
select d2.sample_id as sample_id
from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
where d2.sample_id IS NOT NULL
AND d1.sample_id = o.sample_id
UNION ALL
select d3.sample_id as sample_id
from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
left join sample_table d3
on d2.sample_id = d3.parent_sample_id
where d3.sample_id IS NOT NULL
AND d1.sample_id = o.sample_id)

END;

4. Wrap that query in a view, call the view from the trigger, delete
rows whose sample_ids are IN the list, like this:

DELETE FROM sample_table
WHERE sample_id IN (
select sample_id
from vw_sample
where root_sample_id = o.sample_id)

This is the view containing the query:

CREATE VIEW vw_sample AS
select d2.sample_id as sample_id,
d1.sample_id as root_sample_id

from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
where d2.sample_id IS NOT NULL
UNION ALL
select d3.sample_id as sample_id,
d1.sample_id as root_sample_id
from sample_table d1
left join sample_table d2
on d1.sample_id = d2.parent_sample_id
left join sample_table d3
on d2.sample_id = d3.parent_sample_id
where d3.sample_id IS NOT NULL

Here is the problem: in both cases 3 and 4, the original row is
deleted, and no other row. It is like the trigger cannot see any data
returned by the either the subquery or the view. There are no errors
generated.

I have of course confirmed that the queries/views return the correct
values, and that if I run the delete as a standalone statement (not
inside the trigger), it works properly.

First, what am I missing? Why doesn't this delete work? This seems
heavily counterintuitive to me.

Second, what are my options? Current constraints are that I cannot
change the data structure, and the delete must use triggers.

Any help at all would be much appreciated.

Apr 19 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Scott CM | last post by:
I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed...
6
by: Mary | last post by:
We are developing a DB2 V7 z/OS application which uses a "trigger" table containing numerous triggers - each of which is activated by an UPDATE to a different column of this "trigger" table. When...
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...
0
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as...
9
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger,...
11
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG'...
2
by: Rajesh Kumar Joshi | last post by:
I have a test table //--------------------------- name varchar(32) version varchar(16) profile XML //--------------------------- The content of the XML file is <sampleprofile ...
3
by: Andrea MF | last post by:
Hi all, i have a problem when creating a trigger on DB2 ver 7.2 on WINDOWS. I have two tables MOVIMAG and BARCODE, the trigge when INSERT INTO MOVIMAG will be UPDATE a MOVIMAG field BARCODE...
8
by: mryangza | last post by:
Hi all, Is there a mechanism for manually marking routine packages as inoperative in DB2LUW? The problem I'm trying to solve is one of recreating interdependent functions. My application...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
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: 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
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

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.