Problem: How to delete a record from a from both the master table as well the associated records from the detail table. Solution: Example: 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 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 ; ^ Usage: DELETE "record_x" FROM master WHERE master_id = "value_x"; Upon executing the delete statement, the trigger will fire and cause a delete of associated records in the DETAIL table and then delete the record in the MASTER table. NOTE: The above example with the (BEFORE DELETE) syntax is appropriate for InterBase using the "Classic" architecture (V4.0/1). With the "Super Server" architecture (WinNT/95 - V4.2.1) and other future releases you may use either trigger syntax of: (ACTIVE BEFORE/AFTER DELETE)
Last Modified: 29-SEP-00