Rollback in trigger not aborting batch
-
The manual says that if you put a ROLLBACK TRANSACTION in a trigger it will abort the batch as well as rollback the transaction. I am not getting this behaviour - my stored procedure just carries on.
Under what circumstances would the stored procedure carry on?
I am trying to write a general and simple error/exception handling stored procedure that logs the error and then aborts the batch. I have an insert trigger on the log table that has a rollback transaction in so that (in theory) the batch aborts.
The trigger is:
create trigger wch_error_log_i on wch_error_log
for insert
as
/***************************************************************************************************
Description: Rollback and abort the current batch.
Relies on a feature of SQL Server that a rollback transaction in a trigger aborts
the batch. Note that a rollback transaction in a stored procedure does not abort
the batch.
Change History:
Ver Date Developer Change
1 11-Jun-2004 Sean Elliott Created
***************************************************************************************************/
begin
-- Rollback and abort the current batch
rollback transaction
-- Reinsert the error that was just implicitly rolled back
insert wch_error_log select * from inserted
end
go
The error handling procedure is:
create procedure wch_error_check
as
/***************************************************************************************************
Description: This stored procedure checks the value of the global variable
@@error and inserts the error log if it is non-zero.
Change History:
Ver Date Developer Change
1 11-Jun-2004 Sean Elliott Created
***************************************************************************************************/
begin
-- Declarations
declare @error int
-- Initialisations
select @error = @@error
-- If an error has occurred then log it to the table ubk_lib_error_log.
if @error != 0
begin
-- Log the error. This will insert a record in ubk_lib_error_log. The
-- insert trigger on this table will issue a rollback transaction. This
-- will halt the processing of the command batch.
exec wch_error_db_log @error = @error, @error_message = 'SQL Server error trapped.'
-- Processing should never get this far. To be double safe, though, just
-- go into an infinite loop at this point. An sa would have to kill the
-- process but at least there would be no possibility of data corruption.
while 1 = 1
begin
waitfor delay '0:01:00'
end
end
return 0
end
go
grant exec on wch_error_check to public
go
The logging procedure (called by above sp) is:
create procedure wch_error_db_log
(
@error int,
@error_message varchar(255)
)
as
/***************************************************************************************************
Description: This stored procedure raises an error message to the client
and writes a record to the error log.
Change History:
Ver Date Developer Change
1 11-Jun-2004 Sean Elliott Created
***************************************************************************************************/
begin
-- Default null parameters.
if @error is null select @error = 50000
if @error_message is null select @error_message = 'Unknown error.'
-- Raise an error to the client. SQL Server internal errors get raised
-- anyway so don't bother with these.
if @error >= 50000 raiserror @error @error_message
insert wch_error_log
(
error_num,
error_text,
error_date,
user_id,
hostname,
program_name
)
select @error,
@error_message,
getdate(),
suser_name(),
s.hostname,
s.program_name
from master..sysprocesses s
where s.spid = @@spid
return 0
end
go
grant exec on wch_error_db_log to public
go
-
Not sure what I did but it works now!
I reckon this is a great way to exception/error handle. Better than any other I can find on the internet. (If I do say so myself - and I do!
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply