Trigger: After Delete ... for each row behaviour

[ChrisC]

Hello,

I am attempting to create a trigger to keep track of changes to a
table, mainly about specific changes to the data in the table. I had
hoped that triggers defined as for each row would actually run as each
row was deleted, but it appears that it is fired, once for each row,
after the entire delete statement has completed. Is this expected?

Here is some DDL/DML that will demonstrate what I'm trying to do - and
what the triggers are doing. If you can spot errors I'm doing or
suggestions for how to get the results I want, I'd appreciate it. Or
just confirming that this is as DB2 is supposed to work.

create table test_delete_trigger (col1 integer, col2 integer);

insert into test_delete_trigger (col1, col2)
values (1, 1), (1, 2), (1, 3), (2, 4), (2, 5);

create table tdt_history (col1 integer, col2 integer, action varchar
(8), time timestamp);

CREATE TRIGGER test_del_trig AFTER DELETE ON test_delete_trigger
REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO tdt_history
(col1, col2, time, action)
VALUES (1, 2, current timestamp,
CASE WHEN 0 = (select count(*) From (select 1
from test_Delete_trigger
where col1 = o.col1 fetch first 1 row only)x )
then 'FULL_DEL'
WHEN 0 = (select count(*) From (select 1
from test_Delete_trigger
where col1 = o.col1
and col2 = o.col2 fetch first 1 row only)x )
THEN 'PART_DEL'
ELSE 'HMM-????'
END);
END
;
--This trigger is supposed to mark deletes from the table where there
are still rows in the database with the same value for COL1 as
PART_DEL, but where there are no longer any rows in the table with the
same value for COL1 as PART_DEL.

delete from test_delete_trigger where (col1, col2) in (values (2, 4));
-- This statement insert a row into TDT_HISTORY stating that a
PART_DEL happened.

delete from test_delete_trigger where (col1, col2) in (values (2, 5));
-- This statement insert a row into TDT_HISTORY stating that a
FULL_DEL happened.

delete from test_delete_trigger where col1 = 1;
-- This statement inserts 3 rows into TDT_HISTORY stating that a
FULL_DEL happened for the same COL1 at the same time.
-- I'd prefer this last one to have 2 PART_DEL, and 1 FULL_DEL,
although I wouldn't really care which was which.

Thanks,
Chris

[Serge Rielau]

ChrisC wrote:

Hello,

I am attempting to create a trigger to keep track of changes to a
table, mainly about specific changes to the data in the table. I had
hoped that triggers defined as for each row would actually run as each
row was deleted, but it appears that it is fired, once for each row,
after the entire delete statement has completed. Is this expected?

Yes, that is how it is defined in ANSI.
Before the DELETE statement has been completed the database is in an
inconsistent state.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab