error handling and logging within a trigger - how much of this would work / would be

Hi,
a little bit of background info; the T-SQL I'm writing is against a
proprietary database schema, I have triggers which are being called
upon through events in a client application over which I have no
control and the schema can change because of patches/new functionality
The pseudo code below is a collation of things I'm contemplating on
doing in my code. The reason being that I want to provide our
application specialists with the possibility to determine what went
wrong where in our production environment, if and when it does, and the
possibility to test patches / new functionality of the properiaty
system by testing in the user interface against a copy of the
production database.
I'd appreciate any feedback on how much of this pseudo code would work
/ would be required?
create trigger trInserrorHandlingInTrigger on propTable for insert as
declare @.returnStatus int, @.aVar int, @.err int
if @.@.rowcount = 0 return
if @.@.rowcount > 1 return
set @.aVar = (select aVal from aTable)
insert into logTable (Nme, sqlStm, aBool, Val) values
('trInserrorHandlingInTrigger', '', 1, @.aVar)
if @.aVar is null return
save transaction lastResort
EXEC @.returnStatus = sp_1_InsertRecords @.aVar = @.aVar
EXEC @.returnStatus = sp_2_InsertRecordsOrRollbackSpOne @.aVar = @.aVar,
@.returnStatus = @.returnStatus
EXEC @.returnStatus = sp_3_UpdateRecordsOrRollback @.aVar = @.aVar,
@.returnStatus = @.returnStatus
select @.err = @.@.error
if @.err <> 0 rollback transaction lastResort
if @.returnStatus <> 0 rollback transaction lastResort
commit transaction
go
create procedure sp_1_InsertRecords
@.aVar int
as
declare
@.err int
insert into wTable (aVal) values (@.aVar)
select @.err = @.@.error
if @.err <> 0
begin
insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_2_InsertRecordsOrRollbackSpOne','lb
l One', 0, @.aVar)
return 1
end
else insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_2_InsertRecordsOrRollbackSpOne','lb
l One', 1, @.aVar)
go
create procedure sp_2_InsertRecordsOrRollbackSpOne
@.aVar int, @.returnStatus int
as
declare
@.err int
if @.returnStatus = 0
begin
-- lbl One
insert into xTable (aVal) values (@.aVar)
select @.err = @.@.error
if @.err <> 0
begin
insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_2_InsertRecordsOrRollbackSpOne','lb
l One', 0, @.aVar)
return 1
end
else insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_2_InsertRecordsOrRollbackSpOne','lb
l One', 1, @.aVar)
end
else
-- lbl Two
delete from wTable where aVal = @.aVar
select @.err = @.@.error
if @.err <> 0
begin
insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_2_InsertRecordsOrRollbackSpOne','lb
l Two', 0, @.aVar)
return 1
end
else insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_2_InsertRecordsOrRollbackSpOne','lb
l Two', 1, @.aVar)
go
create procedure sp_3_UpdateRecordsOrRollback
@.aVar int, @.returnStatus int
as
declare
@.err int
if @.returnStatus = 0
begin
-- lbl One
update yTable set aVal = @.aVar
select @.err = @.@.error
if @.err <> 0
begin
insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_3_UpdateRecordsOrRollback','lbl One', 0, @.aVar)
return 1
end
else insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_3_UpdateRecordsOrRollback','lbl One', 1, @.aVar)
end
else
-- lbl Two
delete from aTable where aVal = @.aVar
select @.err = @.@.error
if @.err <> 0
begin
insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_3_UpdateRecordsOrRollback','lbl Two', 0, @.aVar)
return 1
end
else insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_3_UpdateRecordsOrRollback','lbl Two', 1, @.aVar)
-- lbl Three
delete from wTable where aVal = @.aVar
select @.err = @.@.error
if @.err <> 0
begin
insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_3_UpdateRecordsOrRollback', 'lbl Three', 0, @.aVar)
return 1
end
else insert into logTable (Nme, sqlStm, aBool, Val) values
('sp_3_UpdateRecordsOrRollback','lbl Three', 1, @.aVar)
go
regards,
GerardGerard (g.doeswijk@.gmail.com) writes:
> create trigger trInserrorHandlingInTrigger on propTable for insert as
> declare @.returnStatus int, @.aVar int, @.err int
>
> if @.@.rowcount = 0 return
> if @.@.rowcount > 1 return
> set @.aVar = (select aVal from aTable)
Would be from inserted, not just any table.

> save transaction lastResort
> EXEC @.returnStatus = sp_1_InsertRecords @.aVar = @.aVar
> EXEC @.returnStatus = sp_2_InsertRecordsOrRollbackSpOne @.aVar = @.aVar,
> @.returnStatus = @.returnStatus
> EXEC @.returnStatus = sp_3_UpdateRecordsOrRollback @.aVar = @.aVar,
> @.returnStatus = @.returnStatus
>
> select @.err = @.@.error
> if @.err <> 0 rollback transaction lastResort
> if @.returnStatus <> 0 rollback transaction lastResort
>
> commit transaction
First, the sp_ prefix is reserved for system procedures, don't use it
for your own code. There could be nasty surprises.
This appears to be a bit overkill. And flat out wrong as for the COMMIT
part.
This is the scoop: if an error occurs when you are in a trigger, including
a stored procedure called by a trigger, the entire batch is aborted, and
the current transaction is rolled back. So this savepoint thing is not
likely to work.
And what's wrong with the COMMIT? If a trigger exits with a lower trancount
that it was entered with, that too aborts the batch, and rolls back any
transaction. The idea is that a rollback in a trigger should roll back
the batch, but if you COMMIT the same thing happens.|||Thanks for the feedback, BOL was not particularly clear for me on the
SAVE TRAN and COMMIT PART. I had sort of thought that there was some
possibility to persist some data outside the scope of the trigger by
giving this commit. I think we just have to start living with the idea
in any case.