We help IT Professionals succeed at work.
Get Started

sql 2005 cascading delete in trigger

shmz
shmz asked
on
233 Views
Last Modified: 2012-06-27
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:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE