Subject Re: [IBO] Need help with proper delete SQL
Author Jason Wharton
Joe,

> Have a master table that is linked to several detail tables. Want to
> have all linked records deleted when master record is deleted.
>
> In the master query DeleteSql I added the following:
>
> DELETE FROM HISTORY
> WHERE EMPNO = :EMPNO;
>
> I assumed all records in the HISTORY table that have an EMPNO that
> matches the master tables EMPNO (tables are linked on EMPNO) would be
> deleted, instead I get an error message that says "MULTIPLE RECORDS
> DELETED". Since this is what I intended to do, delete multiple
> records, I don't underdstand why this is an eror. Is there not a way
> to automatically have all detail records linked to a master deleted
> when the master is deleted?

What you should do is delete the single master record and then in a trigger
perform the delete on the detail records.

This can also be accomplished using referential integrity too. ON DELETE
CASCADE I believe is the clause you would use.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com