tommyh (10/1/2010)

---

However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?

select @s = somedata, @o = otherdata from inserted
if @s < 0 and @o = 1 then
   rollback transaction

if update(pk)
begin
   raiserror('Update of primary key is not allowed',11,1)
   rollback transaction
end

tommyh (10/1/2010)

---

Also good that Microsoft change the way that SQL handles this. In 2000 you wouldnt have a clue that your insert just got shafted. Atleast in 2005 and 2008 you get an error back.

However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?

Hugo Kornelis (10/1/2010)

---

Exactly what change are you refering to? As far as I know, the effect of ROLLBACK in a trigger in current versions is the same as it was in SQL Server 2000 (and probably even versions before that).


I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.

(1 row(s) affected)

Server: Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

(1 row(s) affected)

Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

tommyh (10/1/2010)
Running the code under 2000 all i get is

(1 row(s) affected)

under 2005 and 2008 i get

Server: Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

(1 row(s) affected)

Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

The effect is as you say the same the rollback performed.

/T

Hugo Kornelis (10/1/2010)
I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.

[b]tommyh.
Running the code under 2000 all i get is

(1 row(s) affected)

under 2005 and 2008 i get

Server: Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

(1 row(s) affected)

Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

The effect is as you say the same the rollback performed.

/T