SQL Server trigger locks table

Question

I'm not very versed in writing SQL, but I'm attempting to implement a trigger after INSERT.

The trigger will fire, however, the more complicated this particular SELECT statement gets it seems that it locks the table. If I simplify the statement to only have a couple of argument it runs fine.

If I run the SELECT statement from SSMS, it works, and if I run it from Visual Studio in the VB.net application, it also works. I've tried increasing the command timeout but it still fails. There are only a few rows of data in the table.

Any insight would be greatly appreciated. Please let me know if more detail is required. This is a payroll timeclock table, I'm triggering when a record is inserted and then grabbing the previous days clock out date (so I can compare the previous days payroll hours to the previous days project hours).

I forgot to mention that I'm running the SELECT statement on a view I created that includes two tables from the native application.

SELECT TOP 1
    @LastDateClockOut = datEvent
FROM 
    MyTable.dbo.TimeMTSView
WHERE 
    datEvent < @Today 
    AND strUniqueID = @nUser 
    AND blnEventType = 0 
    AND lngClassificationID = 0
ORDER BY 
    datEvent DESC;

Here is the entire trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trgAfterInsert] 
ON [dbo].[tblTimes]
AFTER INSERT
AS
    DECLARE @trg_lngClassificationID int;

    SELECT @trg_lngClassificationID = i.lngClassificationID 
    FROM inserted i;

    IF (@trg_lngClassificationID = 0) --If the date Classification is 0 this means it is NORMAL time as opposed to VACATION or HOLIDAY

    DECLARE @trg_lngID int;
    DECLARE @trg_lngEmployeeID varchar(20);
    DECLARE @trg_blnEventType bit;
    DECLARE @blnEventChar nvarchar(1);
    DECLARE @trg_datEvent datetime;
    DECLARE @nUser varchar(255);
    DECLARE @cmd varchar(255);
    DECLARE @Today varchar(255);
    DECLARE @LastDateClockOut datetime;
    DECLARE @strLastClock varchar(255);

    SELECT @trg_lngID = i.lngID FROM inserted i;
    SELECT @trg_lngEmployeeID = i.lngEmployeeID FROM inserted i;
    SELECT @trg_blnEventType = i.blnEventType FROM inserted i;
    SELECT @trg_datEvent = i.datEvent FROM inserted i;

    SELECT @nUser = strUniqueID 
    FROM dbo.tblEmployees 
    WHERE lngID = @trg_lngEmployeeID AND blnDeleted = 0

    SELECT @blnEventChar = CONVERT(NVARCHAR, @trg_blnEventType);
    SELECT @Today = CONVERT(VARCHAR, GetDate(), 103);

    SELECT TOP 1 @LastDateClockOut = datEvent 
    FROM MyTable.dbo.TimeMTSView 
    WHERE datEvent < @Today 
      AND strUniqueID = @nUser 
      AND blnEventType = 0 
      AND lngClassificationID = 0 
    ORDER BY datEvent DESC --This is what fails

    SELECT @strLastClock = CONVERT(VARCHAR, @LastDateClockOut, 103);

    -- Grab the path to the TimeMTSTrigger.exe written out by the application itself to a log file:
    DECLARE @FileContents  VARCHAR(MAX)

    SELECT @FileContents = BulkColumn
    FROM OPENROWSET(BULK'C:\MTSPath\MTSPath.sql', SINGLE_BLOB) x;

    SET @cmd = 'Start ' + @FileContents + ' ' + @nUser + ' ' + @blnEventChar + ' ' + @strLastClock
    EXEC xp_cmdshell @cmd

Answer 1

I was able to resolve this by not running the SELECT statement on the View I had created in a separate database.

The View had combined two tables from the TIMECLOCKMTS database just to make it easier to grab data. However, the SELECT statement on the View would not run in the SQL Trigger or the exe itself, both would hang and timeout.

By eliminating the View from both the Trigger and EXE and only referencing tables in the Trigger's database this runs perfectly:

USE [TIMECLOCKMTS]
GO
/****** Object:  Trigger [dbo].[trgAfterInsert]    Script Date: 5/19/2018 2:22:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trgAfterInsert] ON [dbo].[tblTimes]
AFTER INSERT
AS

declare @trg_lngClassificationID int;
SELECT @trg_lngClassificationID=i.lngClassificationID from inserted i;

IF (@trg_lngClassificationID = 0) --If the date Classification is 0 this means it is NORMAL time as opposed to VACATION or HOLIDAY
declare @trg_lngID int;
declare @trg_lngEmployeeID varchar(20);
declare @trg_blnEventType bit;
declare @blnEventChar nvarchar(1);
declare @trg_datEvent datetime;
declare @nUser varchar(255);
declare @cmd varchar(255);
declare @Today varchar(255);
declare @LastDateClockOut datetime;

SELECT @trg_lngID=i.lngID from inserted i;
SELECT @trg_lngEmployeeID=i.lngEmployeeID from inserted i;
SELECT @trg_blnEventType=i.blnEventType from inserted i;
SELECT @trg_datEvent=i.datEvent from inserted i;
SELECT @nUser = strUniqueID FROM dbo.tblEmployees WHERE lngID = @trg_lngEmployeeID AND blnDeleted = 0
SELECT @blnEventChar = CONVERT(NVARCHAR, @trg_blnEventType);

--Grab the path to the TimeMTSTrigger.exe written out by the application itself to a log file:
DECLARE @FileContents  VARCHAR(MAX)
SELECT @FileContents=BulkColumn
FROM   OPENROWSET(BULK'C:\MTSPath\MTSPath.sql',SINGLE_BLOB) x;

SET @cmd = 'Start ' + @FileContents + ' ' + @nUser + ' ' + @blnEventChar + ' ' + @trg_lngEmployeeID
EXEC xp_cmdshell @cmd