Greetins all,
I'm dealing with a customer database (FB 2.5.2), which seems to be missing
several system triggers for foreign key constraints.
He has been receiving the ISC ERROR CODE:335544466 with the message
--------------------------
violation of FOREIGN KEY constraint "XXX" on table "YYY"
Foreign key references are present for the record
--------------------------
when trying to delete some records.
The same operation is working fine on the database I'm shipping with the
application.
When doing some research, I've found a similar post and the reason seemed to
be missing
system triggers for foreign key constraints.
I've used the suggested statement (slightly modified as it was not about a
check contraint in my case) for a check
select trim(cc.rdb$constraint_name), trim(cc.rdb$trigger_name),
rc.rdb$relation_name, trg.rdb$trigger_source
from rdb$relation_constraints rc
join rdb$check_constraints cc on rc.rdb$constraint_name =
cc.rdb$constraint_name
join rdb$triggers trg on cc.rdb$trigger_name = trg.rdb$trigger_name
where rc.rdb$relation_name = 'table_with_constraint'
and indeed received no results on the "broken" database, while getting 2
entries on the original database.
A further look into RDB$TRIGGERS using "select * from rdb$triggers order by
RDB$RELATION_NAME" shows me, that there are 1220 entries in the original
database and only 196 in the customer database.
The number of entries with rdb$system_flag = 0 is the same in both databases,
so my own trigger seems to be fine.
When I drop the foreign key contraint in question and add it again, the error
when deleting the records, is gone, and I'm receiving 2 entries from the joint
query above (was none before) and also the number from RDB$TRIGGERS gets up by
2 to 198, so it looks that for some foreign key constraints the system triggers
are missing, right?
What would be the best was to find out which ones are missing and to re-create
them or to drop and re-add the foreign key constraints, which seems to create
system triggers automatically. Ideally a script that would fix, what's missing,
and ignore what it's there as it should.
Best regards,
Patrick
