# 3348 Cascading Triggers Can Cause Invalid Old Row Values to be Passed into a Trigger

---

In my database, three tables exist that are triggering a strange behavior:

1. The first table is a lookup table. For the sake of simplicity, it's structure has only two fields:

table_lookup--
id: Integer;
item_name: varchar(100);

2. The second is a log table, for keeping track of where in the database the lookup table is used:

table_log--
id: Integer;
table_name: varchar(100);
table_id: Integer;

3. The third is a self-referencing table that also contains a reference to the lookup table (#1 above). The purpose of the self-reference on this table is that of a template: If a parent record is updated, those same changes will be cascaded to any child records using triggers. The table definition is as follows:

table_cascade--
id: Integer;
parent_id: Integer;
lookup_id: Integer;

The problem is occurring in the "After Delete" triggers. There are two "After Delete" triggers defined for this table. The first one, which seems to be working fine, deletes any child records:
EXECUTE IMMEDIATE
'DELETE FROM table_cascade WHERE parent_id = ?'
USING
oldrow.id;

The second deletes any log entries. This is where the problem occurs. Although both the parent and the child records are successfully deleted from the first trigger, oldrow.id happens to return the wrong value for the child record when it is deleted. It returns the id value of the parent record for both deletes:
EXECUTE IMMEDIATE
'DELETE FROM table_log' + #13 + #10 +
'WHERE table_name = ?' + #13 + #10 +
'AND table_id = ?;'
USING
'table_cascade',
oldrow.id;