Reputation: 1985
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
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