|Subject||Missing system triggers for foreign key constraints|
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.