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.

dean.cochrane@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

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.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:
>
> 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.