Icon View Incident Report

Serious Serious
Reported By: Brandon Rock
Reported On: 11/14/2010
For: Version 2.04 Build 4
# 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;


Resolution Resolution
Fixed Problem on 11/16/2010 in version 2.05 build 1


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image