We help IT Professionals succeed at work.

sql 2005 cascading delete in trigger

shmz
shmz asked
on
Hi,

I need to do a cascade delete of parent table (supplier table)
I do not want to set on delete cascade on foreign key of child table.

I wrote a trigger this way but it does not work. how can I get it work:

CREATE TRIGGER amr_ValidateSupplierDelete
ON Supplier
Instead Of DELETE
AS
DECLARE  @sdate datetime
SET @sdate = (SELECT sdate FROM deleted)

If (@sdate <= '2011-10-10')
BEGIN
--RAISERROR ('Record can not be deleted.',16,10)
ROLLBACK TRANSACTION
RETURN
END

ELSE
BEGIN

            DECLARE @Supplier_id      INT
            SET @Supplier_id = (SELECT Supplier_id FROM deleted)

            IF EXISTS (SELECT * FROM product WHERE Supplier_ID = @Supplier_id)
                  BEGIN
                  DELETE      FROM      product
                              FROM      product s      
                  INNER      JOIN              Supplier p      ON      s.Supplier_id      = p.Supplier_id
                  WHERE s.Supplier_ID = @Supplier_id
            END
                 
                 
            IF EXISTS (SELECT * FROM supplier WHERE supplier _ID = @supplier _id)
                  BEGIN                  
                  DELETE FROM supplier WHERE supplier _ID = @supplier _id
            END
           
            COMMIT
END
                 

the else section does not go through all queries and just does one at the time, like deletes the product but does not delete supplier....how can I get it work?

Thanks in advance
Comment
Watch Question

Commented:
Hi shmz,

is this the actual code of you trigger? I see some syntax errors in it, for example spaces in "supplier _ID".  Otherwise you code should work as you expect, so maybe you lost actual error when you modified code before posting here.

Also, please note that your trigger does not account on cases, where more than one supplier is deleted in single transaction. I would do it like this:
ALTER TRIGGER amr_ValidateSupplierDelete
ON Supplier
Instead Of DELETE
AS

DELETE p FROM product AS p INNER JOIN deleted AS d ON p.Supplier_id = d.Supplier_id AND d.sdate > '2011-10-10' 
DELETE s FROM Supplier AS s INNER JOIN deleted AS d ON s.Supplier_id = d.Supplier_id AND d.sdate > '2011-10-10' 

Open in new window

Author

Commented:
Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.