473,881 Members | 1,545 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_i d 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_i d = 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_i d IN
(SELECT sample_id FROM sample_table WHERE parent_sample_i d =
O.sample_id)
OR parent_sample_i d = 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_sampl e_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_sampl e_id
left join sample_table d3
on d2.sample_id = d3.parent_sampl e_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_sampl e_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_sampl e_id
left join sample_table d3
on d2.sample_id = d3.parent_sampl e_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 counterintuitiv e 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 2403
de***********@g mail.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_i d 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_i d = 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_i d 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_i d = 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_i d IN
(SELECT sample_id FROM sample_table WHERE parent_sample_i d =
O.sample_id)
OR parent_sample_i d = 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_sampl e_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_sampl e_id
left join sample_table d3
on d2.sample_id = d3.parent_sampl e_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_sampl e_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_sampl e_id
left join sample_table d3
on d2.sample_id = d3.parent_sampl e_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 counterintuitiv e 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
6567
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 to: SELECT * FROM INSERTED
6
7151
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 the triggers are fired, various other operations are performed on other tables in the database. The triggers are not created on these other tables because other programs perform updates to these tables and we do not want the triggers to fire...
0
7152
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 TRIGGER statement.) I've also defined a SQL stored proc, and the trigger is set to call this SP. I've posted the simplified source below. I can manually call the stored proc, and the external trigger is created without any errors. However, when I do...
0
2485
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 I've found it educational. Note: - I build this for use in a JDEdwards OneWorld environment. I'm not sure how generic others find it but it should be fairly generic. - I use a C stored procedure GETJOBNAME to get some extra audit data,
9
9318
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, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate this - by iterating through the collection of tables and passing the tablename to something that...
11
7891
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' does not exist drop table log_errors_tab;
2
1436
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 name="joe" version="123"> <phone>123456789</phone> ...
3
1811
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 taken the value from the table BARCODE. I have write this but doesnt work ==========================================
8
2056
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 creates a number of functions during its installation. A later version of the application introduces changes to some of these functions and needs to recreate those that were affected. Now, I cannot drop some of these since they are used by
0
10716
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10812
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10400
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.