Bryan Fritchie Bryan Fritchie - 2 months ago 11
SQL Question

ROLLBACK in TRIGGER causes Msg 3609

I have an Items table & an Items_Log table to log all changes to the Item table.

CREATE TABLE [dbo].[Items] (
[item_id] [int] IDENTITY(1,1) NOT NULL,
[item_name] [varchar](50) NOT NULL,
[item_desc] [varchar](250) NULL,
[modified_by_id] [int] NOT NULL,
[modified_date] [datetime] NOT NULL
)

CREATE TABLE [dbo].[Items_Log] (
[item_log_id] [int] IDENTITY(1,1) NOT NULL,
[item_id] [int] NOT NULL,
[item_name] [varchar](50) NOT NULL,
[item_desc] [varchar](250) NULL,
[modified_by_id] [int] NOT NULL,
[modified_date] [datetime] NOT NULL
)


Updates to the Items table are performed in a SPROC. ([modified_by_id] intentionally commented out, as noted below)

CREATE PROCEDURE [dbo].[pUpdateItem]
@ItemID INT,
@Name VARCHAR(100),
@ByID INT
AS
UPDATE [Items] SET
item_name = @Name,
--modified_by_id = @ByID,
modified_date = GETDATE()
WHERE item_id = @ItemID;
GO


There is a Trigger on the Items table that logs the old data when it gets updated in any way.

ALTER TRIGGER [dbo].[tItemsUpdate]
ON [dbo].[Items]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

IF (UPDATE(modified_by_id) AND UPDATE(modified_date))
BEGIN
INSERT INTO [Items_Log]
SELECT * FROM Deleted
END
ELSE
BEGIN
RAISERROR ('[modified_by_id] and [modified_date] must be updated.', 16, 1)
ROLLBACK TRANSACTION
END
END


To test the trigger, [modified_by_id] is commented out so that RAISERROR gets called. I am getting 2 errors back:

Msg 50000, Level 18, State 1, Procedure tItemsUpdate, Line 15
[modified_by_id] and [modified_date] must be updated.
Msg 3609, Level 16, State 1, Procedure pUpdateItem, Line 5
The transaction ended in the trigger. The batch has been aborted.


The 1st error is obviously the error that I want to see, and the transaction correctly rolls back. But I really want it to exit without throwing the 2nd error, because it displays for the user like this.

So, according to suggestions I have seen elsewhere, I tried doing a Try...Catch in the SPROC, along with a formal declaration of the Transaction and the Rollback there (and taking the Rollback out of the Trigger). It looked like this in the SPROC:

BEGIN TRY
BEGIN TRANSACTION
UPDATE [Items] SET
item_name = @Name,
--modified_by_id = @ByID,
modified_date = GETDATE()
WHERE item_id = @ItemID;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH


Now the Update does not occur, but I get no error message at all. Not even the one from RAISERROR.

I wish I could simply surpress "Msg 3609". That would make everything work the way I want it too. But at this point I'll take almost any solution that works.

Can anyone help me with this?

Answer
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage; 
    ROLLBACK TRANSACTION;
END CATCH;  

https://msdn.microsoft.com/en-us/library/ms190358.aspx

or

BEGIN CATCH  
    DECLARE @ErrorSeverity INT, @ErrorState INT, @ErrorMessage VARCHAR(100); 
    SELECT @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorMessage = ERROR_MESSAGE(); 
    ROLLBACK TRANSACTION; 
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
Comments