Problem: Trying to use implement a cascading delete trigger and getting a foreign key violation error. When I pass the following SQL: DELETE "record_x" FROM master WHERE master_id = "value_x"; I get the foreign key violation error. Solution: The problem occurs, because you are using the incorrect type of trigger. A BEFORE DELETE trigger must be used instead of an AFTER DELETE trigger. This is an example using an AFTER DELETE trigger. (causes error) ---------------------------------------------------------------------------------------------- CONNECT "mydb.gdb" user "sysdba" password "masterkey"; /* Domain definitions */ CREATE DOMAIN ID AS INTEGER; /* Table definitions */ CREATE TABLE MASTER (MASTER_ID ID NOT NULL, MASTER_DSC CHAR(18), PRIMARY KEY (MASTER_ID)); CREATE TABLE DETAIL (MASTER_ID ID NOT NULL, DETAIL_ID ID NOT NULL, DETAIL_DSC CHAR(18), PRIMARY KEY (MASTER_ID, DETAIL_ID), FOREIGN KEY (MASTER_ID) REFERENCES MASTER(MASTER_ID)); COMMIT; SET TERM ^ ; /* Trigger */ CREATE TRIGGER DEL_DETAIL.MASTER_ID FOR MASTER ACTIVE AFTER DELETE POSITION 0 AS DECLARE VARIABLE numrows INTEGER; BEGIN DELETE FROM Detail WHERE Detail.Master_ID = OLD.Master_ID; END ^ COMMIT WORK ^ SET TERM ; ^ This is an example using the BEFORE DELETE trigger: (Will Work) ----------------------------------------------------------------------------- SET TERM ^ ; /* Trigger */ CREATE TRIGGER DEL_DETAIL.MASTER_ID FOR MASTER ACTIVE BEFORE DELETE POSITION 0 AS DECLARE VARIABLE numrows INTEGER; BEGIN DELETE FROM Detail WHERE Detail.Master_ID = OLD.Master_ID; END ^ COMMIT WORK ^ SET TERM ; ^ NOTE: This issue has been addressed in the most current release of InterBase on WinNT/95 version (WI-V4.2.1). This is specific to the "Super Server" architecture. All versions of "Classic" V4 architecture will exhibit this problem.
Last Modified: 26-OCT-00