community.borland.com

Article #25180: How to implement a cascading delete with a trigger.

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