I have a trigger:

CREATE TRIGGER [dbo].[UpdateTbl] 
   ON  [dbo].[TableB] 
   AFTER INSERT

AS 
BEGIN

       DECLARE @role varchar(20) 
       select @role = u.[role] from inserted i join users u on u.id = i.creator_id 

       if @role = 'client'
              update TableA
              set updated_date = i.created_date from inserted i where TableA.id = i.trans_id

       if @role = 'tech'
              update TableA
              set updated_date = i.created_date from inserted i where TableA.id = i.trans_id and i.[public] = 'true'



END
GO

But it doesn't work as expected. The updated_date is still updated with the value of i.created_date even if i.[public]='false'. Any idea?

closed as off-topic by sp_BlitzErik, LowlyDBA, Mr.Brownstone, Vérace, hot2use Dec 11 at 8:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – sp_BlitzErik, LowlyDBA, Mr.Brownstone, Vérace, hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 6
    Your trigger has been written as if the INSERT statement would insert only 1 row. – ypercubeᵀᴹ Dec 7 at 2:17
  • 1
    Another problem you're likely going to have... If you do a multi-row insert that has a mix of both "client" and "tech" users, You'll find that the @role will be arbitrarily set to one or the other and the corresponding update will be applied to the entire set. – Jason A. Long Dec 7 at 3:32
  • thank you for your replies. I had an idea that this was the issue based on previous articles, but I'm having a hard time revising the query correctly. any suggestion on how to revise the query? – sqldummy Dec 7 at 5:39

When you DECLARE and SELECT @role= at the beginning of your code, you assume that the trigger will only be applied to single-row inserts. Effectively, the value of @role is not guaranteed, as it is assigned multiple times, and you cannot control the order.

To fix this, you could instead join the users table into your update:

UPDATE a
SET a.updated_date=i.created_date

FROM TableA AS a
INNER JOIN inserted AS i ON a.id=i.trans_id
INNER JOIN users AS u ON u.id=i.creator_id

WHERE u.[role]='client' OR
      u.[role]='tech' AND i.public='true';

In this example, the update is performed if a) the role is "client", or b) the role is "tech" and public is "true".

Not the answer you're looking for? Browse other questions tagged or ask your own question.