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?
Rolling back a transaction inside a trigger is used to avoid inconsistent data. Eg.
select @s-2 = somedata, @o = otherdata from inserted
if @s-2 < 0 and @o = 1 then
rollback transaction
Or you can avoid change in some column
if update(pk)
begin
raiserror('Update of primary key is not allowed',11,1)
rollback transaction
end