SQL Server: Capturing errors in proc called by trigger

[trpnbillie]

Hi All! I bet I can stump ya!

I have a trigger on a table and the trigger calls a stored procedure. I would like to capture errors in the stored procedure but for some reason, SQL Server will not continue commands after the error - it simply stops and hence, I cannot capture/raiserror.

What is strange is that if I cut and paste the stored procedure and run it manually in Query Analyzer, it WILL continue commands and I AM able to capture, raiserror.

Here is what I call:
-------
DECLARE @MN as varchar(20), @ID as Int

SELECT @ID = 200
SELECT @MN = master_number from Imp_TestTable where Record_ID = @ID

BEGIN TRANSACTION

UPDATE A_TestTable2
SET [master number] = @MN, [field1] = 'too long'
WHERE [master number] = @MN
/* the above update statement is designed to fail */

IF @@ERROR <> 0
BEGIN
RAISERROR('this sucks', 18,1)
END
ELSE
COMMIT TRANSACTION
GO
--------

It's so strange that it will work in Query Analyzer but not in a sproc called by a trigger.

Is it illegal to call sprocs and capture errors from a trigger?

Any help is greatly appreciated!! Thank you so much!

 

[topher0303]

What happens when you update the table that contains the trigger? Does it hang, return an error, or returns as if it were successful?

Post the code for the trigger. That could be relevant.

Chris.

 

[trpnbillie]

When I update the table with the trigger, it behaves normally - returns successfully. Here is the trigger:

CREATE TRIGGER [test2] ON [dbo].[Imp_DAP_Audit]
FOR INSERT
AS

set nocount on

DECLARE @C as INT, @ID as INT,@MN as varchar(20), @CID as INT, @status as varchar(30), @Title as varchar(100), @ingestdate as datetime, @Next_Seq as INT

SELECT @ID = id FROM inserted
SELECT @MN = master_number from Imp_DAP_Images where Record_ID = @ID
SELECT @C = count(*) FROM a_test where [master number] = @MN
IF @C = 0
BEGIN
EXEC DAP_Add_Images @ID
END
ELSE
BEGIN
EXEC a_test1 @ID
END


It's very strange. Again, it worked in query analyzer...

 

[ITsupport01]

I don't capture the error message from the stored-procedure but instead capture the return error number.

CREATE TRIGGER [test2] ON [dbo].[Imp_DAP_Audit]
FOR INSERT
AS

set nocount on
declare @ReturnError int -- capture error number

DECLARE @C as INT, @ID as INT,@MN as varchar(20), @CID as INT, @status as varchar(30), @Title as varchar(100), @ingestdate as datetime, @Next_Seq as INT

SELECT @ID = id FROM inserted
SELECT @MN = master_number from Imp_DAP_Images where Record_ID = @ID
SELECT @C = count(*) FROM a_test where [master number] = @MN
IF @C = 0
BEGIN
EXEC @returnError = DAP_Add_Images @ID
END
ELSE
BEGIN
EXEC @returnError = EXEC pr_test1 @ID
END
if @ReturnError = 99
begin
RAISERROR('this sucks', 18,1)
end
go

create procedure pr_test1 (@ID Int)
as
DECLARE @MN as varchar(20)
SELECT @ID = 200
SELECT @MN = master_number from Imp_TestTable where Record_ID = @ID

BEGIN TRANSACTION

UPDATE A_TestTable2
SET [master number] = @MN, [field1] = 'too long'
WHERE [master number] = @MN
/* the above update statement is designed to fail */

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 99
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN 0
END
GO

 

[topher0303]

A couple of things to note. First, I see a conditional statement at the end of the trigger. Could it be calling the sproc the wrong sproc (@C not being the value you are expecting?)

Also, in the sproc, if the criteria in the update statement causes it to find no records to update, the error will not occur.

An unrelated thing I will mention is the following statement in your trigger assumes there will only be one record inserted. If multiple records are inserted into the table, you will not get your desired results.

SELECT @ID = id FROM inserted



Chris.