Cascade Deleted in SQL

[sc5502]

Background: Front end MS Access 2010/VBA; Back end MS SQL Server 2008

I have a database that has many relationships in it (see attached). This is the Access view. The back end SQL Database has the exact relationships.

Can I cascade delete by just deleting the Supplier table entry and if so how do I do it?

Thanks.

[jforbes]

In my experience there are two ways of accomplishing this: A Foreign Key Constraint or a Delete Trigger.

You can use Foreign Key Constraints to perform Cascading Deletes. Kinda like this:

ALTER TABLE SupplierNotes
ADD CONSTRAINT FK_SupplierNotes_Supplier_Cascade
FOREIGN KEY (ID_Supplier) REFERENCES Supplier(ID_Supplier) ON DELETE CASCADE

A Delete Trigger is a bit trickier but can give you more control over the process. This is a basic one:

CREATE TRIGGER SupplierDel
ON Supplier
FOR DELETE
AS
BEGIN
    DELETE FROM SupplierNotes
    WHERE ID_Supplier IN (SELECT ID_Supplier FROM deleted )
   --...
   --place additional tables to delete from here
   --...
END

[zmbd]

Personally,
I code my deletes, J's second suggestion, instead of using the cascade delete at the table level adding a few bells and whistles to force the user to confirm the delete then work backwards on each child table to the parent. For my work it's too easy to have an accidental erasure of data using the table level cascade delete.