SQL Server trigger interfering with select statement

Question

We have a trigger on a table that fires during an update. We use Entity Framework on the front end. So, we don't want to see the intermediate results (the results from the trigger) when we do an update. I have SET NOCOUNT ON in my trigger:

ALTER TRIGGER [dbo].[trgForMedCMDWorkItemsUpdate]
   ON  [dbo].[MedCMDWorkItems]
   AFTER UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

But when we execute the update, we see the intermediate results first and that throws Entity Framework.

Here is the sql command:

Declare @UserID int
set @UserID = 26
declare @WorkItemID int
Declare @UserRoles TABLE ( roleid INT ,parentroleid INT ,rolename VARCHAR(100) )  

INSERT INTO @UserRoles  SELECT R.id,R.parentroleid,R.ROLE FROM userroles UR 
INNER JOIN roles R ON UR.roleid = R.id WHERE UR.userid = @UserID

Update MedCMDWorkItems with (UPDLOCK) Set AssignedTo = @UserID, AssignedAt = 
getdate(), LastUpdatedAt = getdate(), LastUpdatedBy = @UserID, @WorkItemID = 
ID 
where ID = (SELECT TOP 1 PR.ID FROM MedCMDWorkItems PR --with (UPDLOCK) 
JOIN TaskTypes TT on PR.TaskTypeID = TT.ID WHERE PR.AssignedTo IS NULL AND 
PR.ClosedAt IS NULL 
AND Exists (SELECT 1 FROM @UserRoles WHERE roleid in (852,772)) AND  
((PR.WorkItemTypeID = (select 29 FROM @UserRoles UR WHERE UR.RoleID = 852) 
AND TT.Task in (SELECT Substring(UR1.rolename, Charindex('|', UR1.rolename) 
+ 1, Len(UR1.rolename)) FROM @UserRoles UR1 WHERE UR1.parentroleid in (852)) 
) OR 
(PR.WorkItemTypeID = (select 28 FROM @UserRoles UR WHERE UR.RoleID = 772) 
AND TT.Task in (SELECT Substring(UR1.rolename, Charindex('|', UR1.rolename) 
+ 1, Len(UR1.rolename)) FROM @UserRoles UR1 WHERE UR1.parentroleid in (772))  
) ) 
ORDER BY (CASE WHEN PR.IsUrgent IS NULL THEN 'False' ELSE PR.IsUrgent END) 
DESC, ( CASE  WHEN PR.Priority IS NULL THEN 9999 ELSE PR.Priority END ), 
PR.CreatedAt)

Select * From MedCMDWorkItems PRI  Where ID = @WorkItemID

Here is the trigger:

USE [CombinedWorkflow] 
GO
/****** Object:  Trigger [dbo].[trgForMedCMDWorkItemsUpdate]    Script Date: 
4/3/2018 5:20:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trgForMedCMDWorkItemsUpdate]
ON  [dbo].[MedCMDWorkItems]
AFTER UPDATE
AS 
BEGIN
SET NOCOUNT ON;

declare @NewAssignedAt DateTime;
declare @OldAssignedAt DateTime;
declare @NewPulledAt DateTime;
declare @ID int;

select @NewPulledAt = i.PulledAt from inserted i;
select @NewAssignedAt = i.AssignedAt from inserted i;
select @OldAssignedAt = d.AssignedAt from deleted d;
select @ID = i.ID from inserted i;

--If work item is being un-assigned
if(@OldAssignedAt is NOT NULL and @NewAssignedAt is NULL)
BEGIN
SET NOCOUNT ON;
Update MedCMDWorkItems 
Set PulledAt = NULL
Where  ID = @ID
END

--if work item is being assinged
ELSE if(@NewAssignedAt IS NOT NULL)
BEGIN
SET NOCOUNT ON;
Update MedCMDWorkItems 
Set FirstAssignedAt = @NewAssignedAt
Where FirstAssignedAt is NULL and ID = @ID

END


-- if work item is being pulled
ELSE if(@NewPulledAt IS NOT NULL)
BEGIN
SET NOCOUNT ON;
Update MedCMDWorkItems 
Set AssignedAt = @NewPulledAt
Where AssignedAt is NULL and ID = @ID
SET NOCOUNT ON;
Update MedCMDWorkItems 
Set FirstAssignedAt = @NewPulledAt
Where FirstAssignedAt is NULL and ID = @ID
END


Select 1
END

When I execute in SSMS with the trigger on under results, I get 2 results, first indicating 1 row was updated, then a second indicating the results of the query.

If I disable the trigger and execute the command I don't get the first result, and that is my desired behavior with the trigger enabled.

How do I set this trigger up so it fires but does not cause the additional results?

Thanks, Sammer

What do you mean by intermediate results? And what do you mean by a select with an embedded update? That makes no sense. You need to give us some more context here. — Sean Lange, Apr 04 '18 at 13:20
Sample data, sample code, expected results? What does the trigger do? What kind of statement is run from code? What kind of results are you getting? — Nenad Zivkovic, Apr 04 '18 at 13:25
can you show us these intermediate results ? Because I dont have a clue what they are or could be. Your question is not clear at all, please explain more of your problem — GuidoG, Apr 04 '18 at 13:25
Your EF query may be using the wrong `isolation level`. See e.g. [this answer](https://stackoverflow.com/a/12795274/1220550) for more info. — Peter B, Apr 04 '18 at 13:28
Your trigger is not correct, you fetch a value from the `inserted`table into a variable, but the `inserted` table can have more than one row. You need to use `inserted` and `deleted`as tables, not as variables — GuidoG, Apr 04 '18 at 13:43
There is also no need for more then one ´set nocount on` Just one as the very first statement in your trigger is enough — GuidoG, Apr 04 '18 at 13:44
remove the `select 1`at the bottom of your trigger. It has no use at all and might even be the cause of your `intermediate results` you think you are getting — GuidoG, Apr 04 '18 at 13:46
To quote one of marc_s's common comments: Your trigger has **MAJOR** flaw in that you seem to assume it'll be called **once per row** - that is **not** the case. The trigger will fire **once per statement**, so if your INSERT statement that causes this trigger to fire inserts 25 rows, you'll get the trigger fired once, but then Inserted pseudo table will contain 25 rows — Zohar Peled, Apr 04 '18 at 13:46
And you should **NEVER** attempt to return a resultset from a trigger - which is what "select 1" does. — SMor, Apr 04 '18 at 13:56
The `ORDER BY` is also redundant in your original update statement. There are a lot of issues with this code. I agree - there is no reason for the `select 1` in your trigger, and it's probably causing your issue. — Nick.Mc, Apr 04 '18 at 14:03

score 0 · Answer 1 · answered Apr 04 '18 at 13:59

you need to use inserted and deleted as tables, they can hold more than one row so you never can fill a variable from it.

In stead you have to build it like this example :

your code (first part of it)

ALTER TRIGGER [dbo].[trgForMedCMDWorkItemsUpdate]
ON  [dbo].[MedCMDWorkItems]
AFTER UPDATE AS 
BEGIN
  SET NOCOUNT ON;

  declare @NewAssignedAt DateTime;
  declare @OldAssignedAt DateTime;
  declare @NewPulledAt DateTime;
  declare @ID int;

  select @NewPulledAt = i.PulledAt from inserted i;
  select @NewAssignedAt = i.AssignedAt from inserted i;
  select @OldAssignedAt = d.AssignedAt from deleted d;
  select @ID = i.ID from inserted i;

  --If work item is being un-assigned
  if(@OldAssignedAt is NOT NULL and @NewAssignedAt is NULL)
  BEGIN
    SET NOCOUNT ON;
    Update MedCMDWorkItems 
    Set PulledAt = NULL
    Where  ID = @ID
  END

can be replaced by something like this (not tested because i dont have your database)

ALTER TRIGGER [dbo].[trgForMedCMDWorkItemsUpdate]
ON  [dbo].[MedCMDWorkItems]
AFTER UPDATE AS 
BEGIN
  SET NOCOUNT ON;

  update MedCMDWorkItems
  Set PulledAt = NULL
  where ID in ( select i.Id 
                from   Inserted i
                  left join deleted d on i.Id = d.Id
                where  d.AssignedAt is not null
                and    i.AssignedAt is null
              )

Once you get this it will not be so hard to figure out how to adjust the rest of your trigger

I made the suggested changes to the trigger: update MedCMDWorkItems Set PulledAt = NULL where ID in ( select i.Id from Inserted i left join deleted d on i.Id = d.Id where d.AssignedAt is not null and i.AssignedAt is null ) .... update MedCMDWorkItems Set FirstAssignedAt = (select i.AssignedAt from inserted i where i.id = ID and i.AssignedAt is not null) where ID in ( select i.Id from Inserted i where i.AssignedAt is not null ) — Sammer, Apr 04 '18 at 17:32
have you removed the select 1 from the bottom ? And did you leave only that code in your trigger for testing, nothing else ? — GuidoG, Apr 05 '18 at 06:37

score 0 · Answer 2 · answered Apr 04 '18 at 17:57

Thanks everyone for the input. I figured out the issue. The trigger had this at the end:

... END

-- if work item is being pulled
ELSE if(@NewPulledAt IS NOT NULL)
BEGIN
SET NOCOUNT ON;
Update MedCMDWorkItems 
Set AssignedAt = @NewPulledAt
Where AssignedAt is NULL and ID = @ID
SET NOCOUNT ON;
Update MedCMDWorkItems 
Set FirstAssignedAt = @NewPulledAt
Where FirstAssignedAt is NULL and ID = @ID
END


Select 1
END

No idea what that last line (Select 1) is supposed to be for (I didn't write the trigger :)), but it was causing the unwanted results being displayed.

Sammer

your trigger will FAIL if there is more than one row in the table inserted. You really need to look at `inserted`and `deleted`as tables not as variables. It might work for now when testing with just one update, but will fail in production when there is more than one update — GuidoG, Apr 05 '18 at 06:39

SQL Server trigger interfering with select statement

2 Answers2