Wow, it's unbelievable what I found.

ALTER TRIGGER [dbo].[trg_PersonDelete] 
   ON  [dbo].[People]
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	DELETE FROM FriendTimes WHERE FriendID IN (SELECT PersonID FROM INSERTED WHERE Deleted = 1)
	DELETE FROM VolunteerTimes WHERE PersonID IN (SELECT PersonID FROM INSERTED WHERE Deleted = 1)
	DELETE FROM AtHomeMatches WHERE FriendID IN (SELECT PersonID FROM INSERTED WHERE Deleted = 1)
	DELETE FROM AtHomeMatches WHERE VolunteerID IN (SELECT PersonID FROM INSERTED WHERE Deleted = 1)

END

Why on earth this trigger is set for UPDATE? BTW, for deleted should not we used DELETED table instead?

http://msdn.microsoft.com/en-us/library/ms191300.aspx

I guess we need to drop both triggers, I checked the code in them and we don't need it at all anymore.

We may change the trigger to be BEFORE DELETE instead.

Actually, I see that this code attempts to delete records only after we set the deleted flag to 1, so it's correct to be AFTER UPDATE. But we may still need to re-think the logic here.


>Hi everybody,
>
>I'm trying to "delete" by actually updating a table with this statement
>
>exec sp_executesql N'UPDATE People SET Deleted=1 WHERE PersonID=@PersonID',N'@PersonID int,@LetterCode nvarchar(1)',@PersonID=873,@LetterCode=N'V' (got from Profiler).
>
>I could not figure out why I'm getting this error
>

>The DELETE statement conflicted with the
>REFERENCE constraint "FK_AtHomeSessions_AtHomeMatches".
>The conflict occurred in database "FCCMS", table "dbo.AtHomeSessions".
>The statement has been terminated.
>

>
>How come UPDATE command causes this strange error?
>
>Any ideas?
>
>Thanks a lot in advance.
>
>I'm guessing now that I may have UPDATE trigger that causes this error. How can I see table's UPDATE triggers?
>
>The other strange thing is where the second parameter @LetterCode is coming from. It is not defined as a DeleteParameter.
>
>Ok, this code gives me list of all triggers for a database select tablename = object_name(parent_obj),* from sysobjects
>where type = 'tr'
>
>I see I have two triggers defined for the People table
>

>select tablename = object_name(parent_obj),* from sysobjects
>where type = 'tr'