cascade delete procedure

[Dani]

Im trying to make a cascade delete procedure for a generic database,
:i.e:

- You want to delete a specific row from a table and, in order to avoid
the errors due to constraints, also want to delete all the tables thata
reference the former. Can this be done in a single procedure? Has anyone
done this yet?
I need help with this as fast asa it's poosible
Thanks in advance

Daniel

[Rob Whall]

Daniel,
I'll need some clarification here. Do you want to delete all ROWS in a child table if a row in the parent table is deleted? Or do you want to delete all TABLES that are referenced by a deleted row?

If you want to automagically delete rows from a child table when a row from a parent table is deleted, you can create the following trigger on the parent table:

CREATE TRIGGER delete_child_rows
ON parent_table
FOR DELETE
AS
DELETE FROM child_table
where record_id IN
(SELECT record_id FROM deleted)

In this example, if a row from the parent_table is deleted, then all referenced
rows from the child_table will be deleted. record_id is the key in the parent_table,
and the foreign key in the child table.

Hope this helps,

Rob Whall
rob....@tumbleweed.com

Dani <dan...@tid.es> wrote in article
<3A0FCCDC...@tid.es> :

_______________________________________________
Submitted via WebNewsReader of http://www.interbulletin.com
Complaint against spamming pls. to: abuse @ interbulletin.com