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]
INSTEAD OF DELETE
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
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)