Hi,

I have found a bug in my program where a few database tables are not
updated properly due to Firebird unable to access certain data from
the trigger.
I do not know whether this is the expected behaviour or not, and if it
is configurable (I really hope so).

I have 2 tables, primary & secondary. Secondary table references
a column in primary with ON DELETE CASCADE, i.e. when one deletes
a row from the primary table, all corresponding rows from the secondary
table are automatically deleted by Firebird.

Unfortunately, by the time the BEFORE DELETE trigger in the secondary
table starts, the corresponding row from the primary table is
already gone and there is no way to access its data from the trigger.

I include the code which demonstrates this problem. The last query
returns "not found".

Question: Is there any way to configure FireBird (or declare
trigger/constraint) in such a way that I could access the row from
the primary table from the BEFORE DELETE trigger of the secondary
table, when one deletes a row from the primary table?

Thanks,

Valentin Samko
http://val.samko.info


=============================================================
create table primary_table
(
id integer not null,
CONSTRAINT PK_primary_id primary key (id),
myvalue integer
);

create table log_table (msg CHAR (16));

create table secondary_table
(
id integer not null,
CONSTRAINT PK_secondary_id primary key (id),
primary_id INTEGER NOT NULL REFERENCES primary_table (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

CREATE TRIGGER ON_DELETE FOR SECONDARY_TABLE ACTIVE
BEFORE DELETE POSITION 0
AS
declare variable tmp integer;
BEGIN
select id from PRIMARY_TABLE where PRIMARY_TABLE.id = old.primary_id into :tmp;
if (tmp is NULL) then
insert into log_table (msg) values('not found');
else
insert into log_table (msg) values('found');
END

insert into primary_table (id, myvalue) values (1, 111);

insert into secondary_table (id, primary_id) values (5, 1);

delete from primary_table where id = 1;

select msg from log_table;
=============================================================