We have an old product that has moved forward slowly over the years from originally being built in SQL Server 2000 and the problem ralates to how SQL bubbles up the Errors from RaiseError calls.
For instance I have a trigger on a table that raises an error similar to this:
ALTER TRIGGER [dbo].[Rotation_UTrig]
ON [dbo].[Rotation] FOR UPDATE AS
BEGIN
SET NOCOUNT ON
.
.
.
/* * VALIDATION RULE FOR FIELD 'RotationYear' */
RAISERROR ('Invalid value entered for RotationYear, must be >=0', 44444, 1)
goto fatalerror
.
.
fatalerror:
END
This has the required effect in the calling procedure that the if statement has a value for @@Error:
update Rotation
set OrderedQuantity = @OrderedQuantity,
DespatchedQuantity = @DespatchedQuantity
where ID = @RotationNo
if @@Error != 0
begin
if @AlreadyInTrans = 0 begin rollback transaction end
raiserror ('Error on update of Rotation.', 16, 1)
goto fatalerror
end
.
.
.
fatalerror:
but the error seen by the parent calling application is:
Error Severity levels greater than 18 can only be specified by members of the sysadmin role, using WITH LOG option
This is because the error level is 44444 in the trigger, if I add
With LOG
to the trigger code for Raiserror then this closes the connection (which we do NOT want). If I lower the error level to 16 in the trigger then the
if @@Error !=0
line does not hit as @@Error is set to zero.
Other than using a
Try... Catch
around the update statement in the parent procedure, is there any other way to bubble up the @@Error value into the procedure which does the update so that the procedure code (the update statement and the following if @@Error != 0) can remain unchanged? If not then this is a massive change to my application as I'll need to change all the locations where the impacted tables are being updated as well as to all the triggers.
Found it! The issue is that when another trigger comes along and works through with no problems, @@Error is being reset to 0.