troubleshooting Question

Please review my instead of trigger

Avatar of stankstank
stankstank asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
5 Comments2 Solutions209 ViewsLast Modified:
I have a table which has a FK which references the PK in the same table.  This is because the table stores contacts and each contact can refer more contacts.  The pk is named Contact_Auto_ID and the foreign key (in the same table) is Referred_By.  Yes, the names aren't hte best, I would have preferred PK_Contact_ID and FK_ReferredBy_ID or something, but this is what I have to work with...

Having said that, if we delete a contact who has referred one or more contacts we need to set all of the child records Referred_By field to 0 - which is the default value.  I had to do this using an instead of trigger so I could make the changes (set to 0) and then delete the record which needed to be deleted.  

I am looking for advice on this, please let me know if I have done right or not!

By the way, I couldn't do this with the constraint's set default property, it was greyed out and wouldn't let me.

ALTER TRIGGER [AppContacts].[ReleaseReferrals_D]
   ON  [AppContacts].[Contacts]
   INSTEAD OF DELETE
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- Insert statements for trigger here
   UPDATE  [AppContacts].Contacts
   SET Referred_By = 0
   WHERE Referred_By in    
      (  
          SELECT  Contact_Auto_ID FROM deleted -- (from deleted (upd & delete))
        )
        AND  (Referred_By <> 0)

   -- Delete the contact
   DELETE FROM [AppContacts].Contacts WHERE Contact_Auto_ID IN (select Contact_Auto_ID
        FROM deleted) AND (Contact_Auto_ID <> 0)

END
ASKER CERTIFIED SOLUTION
appari

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros