We help IT Professionals succeed at work.

Please review my instead of trigger

205 Views
Last Modified: 2010-03-19
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
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
>>By the way, I couldn't do this with the constraint's set default property, it was greyed out and wouldn't let me.
did you try dropping the constraint and recreate it with "on delete set default".
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
vinodpthmn:
i think he already have a record with Contact_Auto_ID as 0 because if you see the last delete statement see the Contact_Auto_ID <> 0  condition.

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

Author

Commented:
>> did you try dropping the constraint and recreate it with "on delete set default". <<
Appari:  yeah, I tried it two times, but it won't let me change the values for some reason.  Every other table works fine, but this constraint has the options greyed out so I can't change on delete to cascade or set default...it's just disabled!  

I am glad to see that the trigger is correct, I did it off the top of my head so I wasn't sure if it was or not.  Now that you experts have judged it all should be fine.  :)  

Thanks for the help guys!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.