触发时回滚会导致Msg 3609 [英] ROLLBACK in TRIGGER causes Msg 3609
问题描述
我有一个Items表&
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
)
Items表的更新在SPROC中执行。 (如下所述,[[modified_by_id]被有意地注释掉了)
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
Items表上有一个触发器,用于在获取旧数据时记录该数据
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
要测试触发器,请注释掉[modified_by_id],以便调用RAISERROR。我回来了2个错误:
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.
因此,根据我在其他地方看到的建议,我尝试做SPROC中的Try ... Catch,以及交易的正式声明和回滚(并将回滚从触发器中取出)。在SPROC中看起来像这样:
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.
我希望我可以简单地打压 Msg 3609。那将使一切都按照我想要的方式工作。但是在这一点上,我将采用几乎所有可行的解决方案。
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.
有人可以帮助我吗?
推荐答案
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
或
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
这篇关于触发时回滚会导致Msg 3609的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!