Posted by DennBen on 06/25/07 12:57

I am doing an update to set a field value = anothe field value (in the
same table) where it is not supplied. I'm handling this in the
trigger, but am getting deadlocks.


Do you see anything wrong with this that would cause deadlocking?


ALTER TRIGGER [trg_myTable_UPDATE]
ON [dbo].[myTable]
AFTER UPDATE,INSERT
AS

SET NOCOUNT ON
BEGIN TRANSACTION
UPDATE A
SET A.MarketID = A.SiteID
FROM myTable A
INNER JOIN INSERTED B
ON A.UID = B.UID
WHERE B.MarketID IS NULL;

IF (@@ERROR <> 0)
BEGIN -- if...then for error handling
RAISERROR 20000 'trg_myTable_UPDATE Update Trigger Failed.
Transaction aborted.'
PRINT 'Unexpected Error Occurred!'
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION

[Back to original message]