Trigger Deadlock

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

Re: Trigger Deadlock

DennBen (dbenedett@hotmail.com) writes:
> 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

First, don't include BEGIN or COMMIT TRANSACTION in a trigger. A trigger
always runs in the context of the transaction defined by the statement
that fired it, so there is no need for user-defined transaction. But
by adding them, and doing things a bit wrong can cause unexpected
consequences.

ROLLBACK TRANSACTION in case you detect a violation of a business rule,
is of course OK.

Checking for @@error is a bit over-the-mark, since an error in a trigger
aborts the entire batch on the spot.

As for why you are getting deadlocks, there is too little information
to tell. Do you know what the trigger is deadlocking with?

There are two trace flags you can enable in the startup options for
SQL Server. When these are active, a deadlock trace is written to the
SQL Server error log. While cryptic, this information can be helpful
to understand why the deadlock is happening. On SQL 2000, you need to
add -T1204 and -T3605 to the startup options. On SQL 2005, use -T1222 and
-T3605. (1222 gives more information than 1204.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx