CODEWITHSUNDEEP
sql-servert-sqltriggers
Donald Jansen
Donald Jansen

Reputation: 1985

SQL Trigger Silently Fails

I have this following Trigger

CREATE TRIGGER [dbo].[LastActionTrigger] on [dbo].[PlanningAction]
AFTER INSERT AS

BEGIN

DECLARE @ActionID BIGINT
DECLARE @ActionNameID BIGINT
DECLARE @MilestonePlanningID BIGINT
DECLARE @Name VARCHAR(MAX)
DECLARE @log as varchar(max)
DECLARE @cmdtxt as varchar(255)

SELECT @ActionID = i.ActionID, @ActionNameID = i.ActionNameID, @MilestonePlanningID = i.MilestonePlanningID
FROM Inserted i


IF @ActionNameID NOT IN (10,11,12)
    BEGIN

    UPDATE MilestonePlanning SET LastPlanningActionID = @ActionID WHERE MilestonePlanningID = @MilestonePlanningID;

    --LOG
    SELECT @Name = AN.ActionName FROM ActionName AN WHERE AN.ActionNameID = @ActionNameID
    SET @log = 'Milestone['+CAST(@MilestonePlanningID AS VARCHAR(MAX))+'] : Last Action Changed To ' + @Name    
    EXEC master..xp_cmdshell @cmdtxt, no_output

    SELECT @cmdtxt = 'echo ' + @log + ' >> c:\database_logs\'+DB_NAME(DB_ID())+'_'+CAST(@MilestonePlanningID AS VARCHAR(MAX))+'.txt'
    SET @log = 'UPDATE MilestonePlanning SET LastPlanningActionID = '+CAST(@ActionID AS VARCHAR(MAX))+' WHERE MilestonePlanningID = ' + CAST(ISNULL(@MilestonePlanningID,0) AS VARCHAR(MAX))
    EXEC master..xp_cmdshell @cmdtxt, no_output

    SELECT @cmdtxt = 'echo ' + @log + ' >> c:\database_logs\'+DB_NAME(DB_ID())+'_'+CAST(@MilestonePlanningID AS VARCHAR(MAX))+'.txt'
    EXEC master..xp_cmdshell @cmdtxt, no_output
    EXEC(@log)
    END  

END

Our system works with play pause Finnish and other actions, with all of the actions they all work with this trigger, however Sometimes the Update statement silently fails or never happens, however the log file gets created (for testing purposes I wrote the log file to see what is going on)

EDIT: I tried the following, but still doesn't work, MilestonePlanning rows get updated and some don't

ALTER TRIGGER [dbo].[LastActionTrigger] on [dbo].[PlanningAction]
AFTER INSERT AS

BEGIN


UPDATE MilestonePlanning SET LastPlanningActionID = i.ActionID
FROM Inserted i
WHERE MilestonePlanning.MilestonePlanningID = i.MilestonePlanningID AND i.ActionNameID NOT IN (10,11,12)

END

EDIT: The above Trigger Worked, the main issue was in code behind where a certain action updated the MilestonePlanning After the trigger fired, this caused that the previous LastPlanningActionID was reset

Upvotes: 1

Views: 688

Answers (1)

marc_s
marc_s

Reputation: 755541

Your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo tables Inserted and Deleted might (and will!) contain multiple rows.

Given that that table might contain multiple rows - which one do you expect will be selected here??

SELECT @ActionID = i.ActionID, @ActionNameID = i.ActionNameID, @MilestonePlanningID = i.MilestonePlanningID
FROM Inserted i

It's undefined - you might get the values from arbitrary rows in Inserted - and all other rows are ignored.....

You need to rewrite your entire trigger with the knowledge the Inserted WILL contain multiple rows! You need to work with set-based operations - don't expect just a single row in Inserted!

Also: I would strongly recommend not to do any time consuming processing in a trigger - most definitely don't call external dependencies like xp_cmdshell. A trigger is executed in the context of a running transaction, and any lengthy processing will extend the execution time of that transaction. Triggers ought to be really nimble and not do lots of processing!

Upvotes: 2

Related Questions