How exactly transactions work in Microsoft SQL Server

I’ve read that creating for insert trigger creates transaction implicitly. So I can rollback transaction from within trigger and “undo” the insert.

Is that so?

create trigger TRIGGER_NAME 
on TABLE_NAME
for insert
as
    declare @someVar = ....

    if @someVar > 0 
    begin
        rollback transaction
    end

So in that case, if @someVar is greater than 0, insert will be cancelled, right?

create trigger TRIGGER_NAME 
on TABLE_NAME
for insert
as
    begin transaction
        declare @someVar = ....

        if @someVar > 0    
        begin
            rollback transaction
        end

        commit transaction

In that case, insert would be also cancelled, right?

What happens when we have two for insert trigger for the same table, and both of them do rollback transaction do they share the same implicit transaction?

I came across some complications with that matter, so I tried creating my own explicit transaction in every trigger, but with begin transaction I just can’t do anything to use inserted properly, I can’t extract any value from it.

Answer

In fact a transaction is the state of the session. Nothing else. So you can be in a transaction sate or not. When you execute a SQL query (which is different from “command”) there is an implicite transaction that encloses the query. When you are inside the trigger code, the transaction state is alive, and you can rollback it or commit. If any of this command (COMMIT or ROLLBACK) is executed in the trigger code, you will raise an execption… But the transacation state no more exists, until you execute any query.

Now if you have several trigger, there is a precedence in the serial execution of the trigger that you can manage with the procedure sp_settriggerorder.

Having multiple triggers for the same action is not recommanded, except when you have to execute them in a specific order. As an example I give in my courses, I limit to three the number of trigger for the same action, with this peculiar precedence :

1. trigger for set based constraint
2. trigger for operational process (client application)
3. trigger for tracking