2

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 create 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.

I tried an INSTEAD OF trigger, but the result is the same.

The result of the trigger is correct, it's doing what I want on the DB side, the problem is that there is software using this table that probably has a Try...Catch and as the trigger returns an error, even if the result is correct, the software gives a Login error because the trigger at the end has an error.

  • Besides the error issue (which seems like an application issue to me), this part doesn't look right: SELECT @user_id = inserted.UserID FROM inserted IF @user_id = 'Admin' What will happen if an UPDATE tries to update 2 or more rows? – ypercubeᵀᴹ Feb 6 '18 at 9:10
3

While I am not advocating the approach being attempted here (i.e. manipulating the Transaction within the Trigger), I at least wanted to explain what was going on so that there was a clearer understanding of how Triggers work.

Here are some basic behaviors of Triggers:

  1. When a Trigger is executed, it is always within a Transaction. Individual DML statements will execute within a system-initiated Transaction (known as auto-commit) if a Transaction was not already active at the time that the DML statement was executed. Meaning:
    • If @@TRANCOUNT == 0 before the DML statement, then @@TRANCOUNT will be 1 in the Trigger
    • If @@TRANCOUNT > 0 before the DML statement, then @@TRANCOUNT will be the same value in the Trigger
  2. Unlike Stored Procedures where @@TRANCOUNT needs to be the same before and after execution, Triggers allow for @@TRANCOUNT being higher or lower after their execution, with one exception (see next item).
  3. If the Trigger ends with @@TRANCOUNT being 0 (before the Trigger exits), then
    1. you will get an error stating that the Transaction ended in the Trigger and that the batch is being aborted
    2. the batch being aborted cannot undo / revert whatever ended the Transaction:
      • If the Transaction was committed, the data will still be committed.
      • If the Transaction was rolled back, those changes are gone.
  4. Once the Transaction ends, either by COMMIT or ROLLBACK, the inserted and deleted tables no longer have any rows in them. If you need anything from them after the Transaction ends, then you need to capture it into variables and/or a temp table (or table variable) before the Transaction ends.
  5. Triggers execute with an implied XACT_ABORT ON. This might complicate things, even if you have a TRY...CATCH construct. In some cases you might need to explicitly set XACT_ABORT OFF

That said, you certainly can COMMIT / ROLLBACK the Transaction within a Trigger. Simplistically, if you do either of those actions, you just need to issue a BEGIN TRAN; before the Trigger ends so that you don't get the error about it aborting the batch. This assumes, in the case of ROLLBACK, that you want to mask the fact that this happened instead of allowing ROLLBACK to cancel the entire operation, which is how it is normally used within Triggers.

However, reality is not so simple:

  1. If there are multiple Triggers, the order in which they fire could result in different behavior. Assuming there were no more than 3 Triggers, you can control the order by setting the "first" and "last" Triggers. More than 3 Triggers will likely not be able to control the ordering of all of the Triggers.
  2. If there are nested Triggers, then ending the Transaction in the Trigger will could lead to unexptected behavior.
  3. If there is an active Transaction before the Trigger executes, then ending the Transaction in the Trigger will take control away from whatever is happening after the Trigger.

To account for the possibility of an active Transaction already existing prior to the Trigger firing, you would need to capture @@TRANCOUNT at the top and then issue the correct number of BEGIN TRAN;, and possibly also COMMIT TRAN;, statements:

DECLARE @TranCount INT = @@TRANCOUNT,
        @Index INT = 0;

...

-- For an actual COMMIT, do the following:
WHILE (@Index < @TranCount)
BEGIN;
   COMMIT TRAN;
   SET @Index += 1;
END;

....

-- If either COMMIT or ROLLBACK occurs, do the following:
SET @Index = 0; -- reset in case COMMIT was processed above
WHILE (@Index < @TranCount)
BEGIN;
   BEGIN TRAN;
   SET @Index += 1;
END;

Again, this is not recommended as it changes the nature of any existing Transaction to behave in an unexpected, non-intuitive way. The flow would end up being:

  1. Begin Transaction A
  2. Do something
  3. Insert into this Table
    1. Transaction A might be committed or rolled-back, in which case:
      1. Actions performed as "Do something" above are either committed or rolled-back
      2. Transaction B will be started
    2. Transaction A might be left alone, in which case there is no Transaction B
  4. After the Trigger exists
    1. If Transaction A was committed or rolled-back, it won't be possible to change that action. Transaction B will now exist, and changes made at this point will be completely disconnected from changes made previously in Transaction A.
    2. If Transaction A was left as active:
      1. Actions taken previously are still available to COMMIT or ROLLBACK
      2. Actions taken after the Trigger ends are grouped along with actions taken before the Trigger fired as an atomic unit (this is how things usually work and hence the expected behavior).

The definitive reference on all this is Error and Transaction Handling in SQL Server by Erland Sommarskog.

2
CREATE TABLE USERS(ID INT, NAME VARCHAR(10), ROLE VARCHAR(10), ENABLED INT);
INSERT INTO USERS VALUES
(1, 'USER1', 'ADMIN', 1),
(2, 'USER2', 'USER', 1);
GO
2 rows affected
CREATE TRIGGER TRG_INS_OF_UPD ON USERS
INSTEAD OF UPDATE
AS
BEGIN

    IF EXISTS (SELECT 1
               FROM   inserted
               WHERE  ROLE = 'ADMIN')
    BEGIN
        --ROLLBACK TRANSACTION;
        UPDATE USERS
        SET    ENABLED=0
        WHERE  ID IN (SELECT ID FROM inserted WHERE ROLE = 'ADMIN');
    END

    -- commit other updates
    UPDATE u
    SET    ID = i.ID,
           NAME = i.NAME,
           ROLE = i.ROLE,
           ENABLED = i.ENABLED
    FROM   USERS u
    JOIN   inserted i
    ON     i.ID = u.ID
    WHERE  i.ROLE <> 'ADMIN';

END
GO
UPDATE USERS SET NAME='ME';
GO
4 rows affected
SELECT * FROM USERS;
GO
ID | NAME  | ROLE  | ENABLED
-: | :---- | :---- | ------:
 1 | USER1 | ADMIN |       0
 2 | ME    | USER  |       1

dbfiddle here

0

So the COMMIT in the ELSE statement was useless, if I don't rollback then as I used the AFTER UPDATE, the Update will be executed.

Removing the COMMIT, in a normal situation the trigger will not raise any error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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