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