SQL Trigger not respecting where clause 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?
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".