I’m trying to trigger an update on a specific user in my user table. Basically is someone tries to change that user then I want to rollback whatever query was executed and disable the user(using a SP), but if if is not that user, than proceed with the update commit.

This is the code I created:

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[Users_Trigger_UPDATE]   ON [dbo].[Users]   AFTER UPDATE   AS  BEGIN SET NOCOUNT ON; DECLARE @user_id as nvarchar(30); DECLARE @action as nvarchar(30) = 'UPDATE'; DECLARE @source as nvarchar(30) = (SELECT client_net_address FROM  sys.dm_exec_connections WHERE session_id = @@SPID);     SELECT       @user_id = inserted.UserID     FROM       inserted    IF @user_id = 'Admin'     BEGIN         ROLLBACK TRAN;         EXEC [dbo].[DisableUser] @user_id, @action, @source;         PRINT 'Admin User Triggered';     END ELSE     BEGIN         PRINT 'Other User';         COMMIT;     END END 

But I’m receiving the error “The transaction ended in the trigger. The batch has been aborted.”

I tried to use the TRY…CATCH but the error is the same. I didn’t crated a nested transaction so I’m assuming that when I use the ROLLBACK or COMMIT that will apply to the original Query that triggered the UPDATE.

Thanks in advance.