Solved

SQL Tricky Trigger causing deadlocks

Posted on 2011-09-23
3
385 Views
Last Modified: 2012-05-12
This Trigger works with just 1 device moving through.  It starts to lose it the more you throw at it.

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'AuthCodesCompare1' AND type = 'TR')
DROP TRIGGER  AuthCodesCompare1
GO
CREATE TRIGGER AuthCodesCompare1 ON [AuthCodesCompare]
FOR INSERT
AS

DECLARE @AuthCodesCompareID As INT
DECLARE @DeviceID As INT
DECLARE @RecNum As varchar(50)
DECLARE @OperatorCode As varchar(50)
DECLARE @OpName As varchar(50)
DECLARE @OpType As varchar(50)
DECLARE @OpExpire As varchar(50)

DECLARE @OpNameSup As varchar(50)
DECLARE @OpTypeSup As varchar(50)
DECLARE @OpExpireSup As varchar(50)

SELECT @AuthCodesCompareID = (SELECT AuthCodesCompareID FROM Inserted)
SELECT @DeviceID = (SELECT DeviceID FROM Inserted)
SELECT @RecNum = (SELECT RecNum FROM Inserted)
SELECT @OperatorCode = (SELECT OperatorCode FROM Inserted)
SELECT @OpName = (SELECT OpName FROM Inserted)
SELECT @OpType = (SELECT OpType FROM Inserted)
SELECT @OpExpire = (SELECT OpExpire FROM Inserted)

      If exists
      (      
      select * from [AuthCodes]
      Where      [DeviceID] = @DeviceID AND
                  [OperatorCode] = @OperatorCode AND
                  [OpName] = @OpName AND
                  [OpType] = @OpType AND
                  [OpExpire] = @OpExpire                  
                  
      )
BEGIN
    UPDATE      [AuthCodesCompare]
    SET [State] = 'ADD'
      WHERE [AuthCodesCompareID] = @AuthCodesCompareID  AND [RecNum] = ''
     
END

     --------------------
      If exists
      (      
      select * from [AuthCodesCompare]
      Where      [DeviceID] = @DeviceID AND
                  [OperatorCode] = @OperatorCode AND
                  [RecNum]<>''
      )
BEGIN
    UPDATE      [AuthCodesCompare]
    SET [State] = 'SUPERSEDED'
      WHERE [STATE] LIKE '%ADD%' AND [OperatorCode] = @OperatorCode
END

     --------------------
      If exists
      (      
      select * from [AuthCodesCompare]
      Where      [DeviceID] = @DeviceID AND
                  [OperatorCode] = @OperatorCode AND
                  [OpName] = @OpName AND
                  [OpType] = @OpType AND
                  [OpExpire] = @OpExpire AND
                  [RecNum]=''
      )
BEGIN
    UPDATE      [AuthCodesCompare]
    SET [State] = 'MATCH'
      WHERE [RecNum]<>'' AND [OperatorCode] = @OperatorCode
END

     --------------------
      If exists
      (      
      select * from [AuthCodesCompare]
      Where      [DeviceID] = @DeviceID AND
                  [OperatorCode] = @OperatorCode AND
                  (
                  [OpName] <> @OpName OR
                  [OpType] <> @OpType OR
                  [OpExpire] <> @OpExpire
                  ) AND
                  [RecNum]=''
      )
BEGIN
    SELECT @OpNameSup = (SELECT OpName FROM [AuthCodesCompare] Where [OperatorCode] = @OperatorCode AND [RecNum]='')
      SELECT @OpTypeSup = (SELECT OpType FROM [AuthCodesCompare] Where [OperatorCode] = @OperatorCode AND [RecNum]='')
      SELECT @OpExpireSup = (SELECT OpExpire FROM [AuthCodesCompare] Where [OperatorCode] = @OperatorCode AND [RecNum]='')
   
    UPDATE      [AuthCodesCompare]
    SET [State] = 'EDIT',
    [OpName] = @OpNameSup,
    [OpType] = @OpTypeSup,
    [OpExpire] = @OpExpireSup
      WHERE [RecNum]<>'' AND [OperatorCode] = @OperatorCode
END

     --------------------
      If not exists
      (      
      select * from [AuthCodesCompare]
      Where      [DeviceID] = @DeviceID AND
                  [OperatorCode] = @OperatorCode AND
                  [RecNum]=''
      )
BEGIN
    UPDATE      [AuthCodesCompare]
    SET [State] = 'DELETE'
      WHERE [RecNum]<>'' AND [OperatorCode] = @OperatorCode
END
GO
0
Comment
Question by:MBoy
3 Comments
 
LVL 3

Author Comment

by:MBoy
ID: 36586907
Any help would be appreciated
0
 
LVL 2

Expert Comment

by:marappan
ID: 36588044
Does this trigger involved in another process for deadlock to occur? (or) Is a different call to the same trigger involves in deadlock?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36590971
This is a more rational way to use TRIGGERs and it supports INSERTs with multiple rows.  To be honest some of your code does not make sense, but I have translated it exactly.
CREATE TRIGGER AuthCodesCompare1 ON AuthCodesCompare
    FOR INSERT

AS

IF EXISTS ( SELECT  1
            FROM    AuthCodes a
                    INNER JOIN INSERTED i ON a.DeviceID = i.DeviceID
                                             AND a.OperatorCode = i.OperatorCode
                                             AND a.OpName = i.OpName
                                             AND a.OpType = i.OpType
                                             AND a.OpExpire = i.OpExpire ) 
    BEGIN
        UPDATE  a
        SET     [State] = 'ADD'
        FROM    AuthCodesCompare a
                INNER JOIN INSERTED i ON a.AuthCodesCompareID = i.AuthCodesCompareID
        WHERE   a.RecNum = ''
     
    END

     --------------------
IF EXISTS ( SELECT  1
            FROM    AuthCodesCompare a
                    INNER JOIN INSERTED i ON a.DeviceID = i.DeviceID
                                             AND a.OperatorCode = i.OperatorCode
            WHERE   a.RecNum <> '' ) 
    BEGIN
        UPDATE  a
        SET     [State] = 'SUPERSEDED'
        FROM    AuthCodesCompare a
                INNER JOIN INSERTED i ON a.OperatorCode = i.OperatorCode
        WHERE   a.[STATE] LIKE '%ADD%'

    END

     --------------------
IF EXISTS ( SELECT  1
            FROM    AuthCodesCompare a
                    INNER JOIN INSERTED i ON a.DeviceID = i.DeviceID
                                             AND a.OperatorCode = i.OperatorCode
                                             AND a.OpName = i.OpName
                                             AND a.OpType = i.OpType
                                             AND a.OpExpire = i.OpExpire
            WHERE   a.RecNum = '' ) 
    BEGIN
        UPDATE  a
        SET     [State] = 'MATCH'
        FROM    AuthCodesCompare a
                INNER JOIN INSERTED i ON a.OperatorCode = i.OperatorCode
        WHERE   a.RecNum <> ''

    END

     --------------------
IF EXISTS ( SELECT  1
            FROM    AuthCodesCompare a
                    INNER JOIN INSERTED i ON a.DeviceID = i.DeviceID
                                             AND a.OperatorCode = i.OperatorCode
                                             AND (a.OpName <> i.OpName
                                                  OR a.OpType <> i.OpType
                                                  OR a.OpExpire <> i.OpExpire
                                                 )
            WHERE   a.RecNum = '' ) 
    BEGIN
        UPDATE  a
        SET     [State] = 'EDIT',
                OpName = a.OpName,
                OpType = a.OpType,
                OpExpire = a.OpExpire
        FROM    AuthCodesCompare a
                INNER JOIN INSERTED i ON a.OperatorCode = i.OperatorCode
        WHERE   a.RecNum <> ''
    END

     --------------------
IF NOT EXISTS ( SELECT  1
                FROM    AuthCodesCompare a
                        INNER JOIN INSERTED i ON a.DeviceID = i.DeviceID
                                                 AND a.OperatorCode = i.OperatorCode
                WHERE   a.RecNum = '' ) 
    BEGIN
        UPDATE  a
        SET     [State] = 'DELETE'
        FROM    AuthCodesCompare a
                INNER JOIN INSERTED i ON a.OperatorCode = i.OperatorCode
        WHERE   a.RecNum <> ''
    END
GO

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now