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.