Sql-server – UPDATE Trigger with condition

sql servertriggerupdate

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.

Best Answer

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:

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
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).
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.
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.
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:

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.
If there are nested Triggers, then ending the Transaction in the Trigger will could lead to unexptected behavior.
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:

Begin Transaction A
Do something
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
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.

Related Solutions

Sql-server – Why TRY CATCH does not suppress exception in trigger

The transaction is doomed with pretty much any exception and must be rolled back.

From "Using TRY...CATCH in Transact-SQL" on MSDN

Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.

A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.

This example shows why. XACT_STATE() doesn't register an implied transaction (eg no explicit BEGIN TRAN)

CREATE TABLE TrgTest (gbn int NOT NULL);
GO
CREATE TRIGGER TRG_TrgTEst_I ON TrgTest AFTER INSERT
AS
BEGIN TRY
     SELECT '1', @@TRANCOUNT, XACT_STATE();
     RAISERROR('Test error', 16, 2);
END TRY
BEGIN CATCH
     SELECT '2', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

BEGIN TRANSACTION
INSERT TrgTest VALUES (1)
SELECT 'will not run'
GO
SELECT 'next batch'
GO


BEGIN TRY
    BEGIN TRANSACTION
    SELECT 'a', @@TRANCOUNT, XACT_STATE();
    INSERT TrgTest VALUES (1)
    SELECT 'b', @@TRANCOUNT, XACT_STATE();
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    SELECT 'c', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

DROP TABLE TrgTest;

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Best Answer

Related Solutions

Sql-server – Why TRY CATCH does not suppress exception in trigger

Sql-server – Oracle GoldenGate add trandata errors

Related Question