如何调试 T-SQL 触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22033109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to debug a T-SQL trigger?
提问by bondijct
I have a table t, which has an "after insert" trigger called trgInsAfter. Exactly how do i debug it? i'm no expert on this, so the question and steps performed might look silly.
我有一个表 t，它有一个名为 trgInsAfter 的“插入后”触发器。我究竟如何调试它？我不是这方面的专家，所以执行的问题和步骤可能看起来很愚蠢。
The steps i performed so far are:
1. connect to the server instancevia SSMS(using a Windows Admin account)
到目前为止，我执行的步骤是： 1. 连接到server instancevia SSMS（使用 Windows Admin 帐户）
right click the trigger node from the lefthand tree in SSMS and double click to open it, the code of the trigger is opened in a new query window (call this Window-1) as : blah....,
ALTER TRIGGER trgInsAfter AS .... BEGIN ... ENDopen another query window (call this Window-2), enter the sql to insert a row into table t:
insert t(c1,c2) values(1,'aaa')set a break point in Window-1 (in the trigger's code)
set a break point in Window-2 (the insert SQL code)
click the Debug button on the toolbar while Window-2 is the current window
the insert SQL code's breakpoint is hit, but when I look at Window-1, the break point in the trigger's code has a tooltip saying
'unable to bind SQL breakpoint, object containing the breakpoint not loaded'
在SSMS左侧树中右键单击触发器节点并双击打开它，触发器的代码在一个新的查询窗口（称为Window-1）中打开为：blah....，
ALTER TRIGGER trgInsAfter AS .... BEGIN ... END打开另一个查询窗口（称为Window-2），输入sql向表t中插入一行：
insert t(c1,c2) values(1,'aaa')在 Window-1 中设置断点（在触发器的代码中）
在 Window-2 中设置断点（插入 SQL 代码）
单击工具栏上的调试按钮，而 Window-2 是当前窗口
插入 SQL 代码的断点被命中，但是当我查看 Window-1 时，触发器代码中的断点有一个工具提示说
'unable to bind SQL breakpoint, object containing the breakpoint not loaded'
I can sort of understand issue: how can SSMSknow that the code in Window-1 is the trigger
我可以理解问题：如何SSMS知道 Window-1 中的代码是触发器
I want to debug? i can't see where to tell SSMS that 'hey, the code in this query editor is table t's inssert trigger's code'
我要调试？我看不到在哪里告诉 SSMS '嘿，这个查询编辑器中的代码是表 t 的插入触发器代码'
Any suggestions?
有什么建议？
Thanks
谢谢
回答by Damien_The_Unbeliever
You're actually over-thinking this.
你其实是想多了。
I first run this query in one window (to set things up):
我首先在一个窗口中运行此查询（以进行设置）：
create table X(ID int not null)
create table Y(ID int not null)
go
create trigger T_X on X
after insert
as
insert into Y(ID) select inserted.ID
go
I can then discard that window. I open a new query window, write:
然后我可以丢弃那个窗口。我打开一个新的查询窗口，写：
insert into X(ID) values (1),(2)
And set a breakpoint on that line. I then startthe debugger (Debugfrom menu or toolbar or Alt-F5) and wait (for a while, the debugger's never been too quick) for it to hit that breakpoint. And then, having hit there, I choose to Step Into(F11). And lo (after another little wait) a new window is opened which is my trigger, and the next line of code where the debugger stops is the insert into Y...line in the trigger. I can now set any further breakpoints I want to within the trigger.
并在该行设置断点。然后我启动调试器（Debug从菜单或工具栏或 Alt-F5）并等待（一段时间，调试器从来没有太快）使其达到该断点。然后，打到那里后，我选择Step Into(F11)。然后（又稍等片刻）打开了一个新窗口，这是我的触发器，调试器停止的下一行代码是insert into Y...触发器中的那一行。我现在可以在触发器内设置我想要的任何进一步断点。
回答by Allan S. Hansen
There is a DEBUG menu in SSMS, but you'll likely need to be on the server to be able to debug, so if it is a remote access, it's probably not gonna be set up for it. That debug option will allow you to execute code, and step into your trigger and debug it in that manner (as you'd debug most any other code).
SSMS 中有一个 DEBUG 菜单，但您可能需要在服务器上才能进行调试，因此如果是远程访问，则可能不会对其进行设置。该调试选项将允许您执行代码，并进入触发器并以这种方式调试它（就像您调试大多数其他代码一样）。
If not having access to the debug menu/function, you'll have to debug "manually":
如果无法访问调试菜单/功能，则必须“手动”调试：
First ensure your trigger is running correctly by inserting the input of the trigger into a debug table. Then you can verify that its called correctly. Then you can debug the query of the trigger as you would any other sql query, using the values from the debug table.
首先通过将触发器的输入插入调试表来确保触发器正确运行。然后您可以验证它是否正确调用。然后，您可以使用调试表中的值调试触发器的查询，就像调试任何其他 sql 查询一样。
回答by Fütemire
For whatever reason I couldn't get @Damien_The_Unbeliever's solution to work.
At least, not on a trigger attached to a table. When I did a Step Intoit just executed the query every time without stepping into the trigger.
无论出于何种原因，我都无法让@Damien_The_Unbeliever 的解决方案发挥作用。至少，不是在附加到表的触发器上。当我做一个时，Step Into它每次都只执行查询而不进入触发器。
Then I noticed in the comments of that approach that you can't see any of the table values anyway.
然后我在该方法的评论中注意到无论如何您都看不到任何表值。
So......
所以......
I ended up creating a generic Debug Table.
我最终创建了一个通用的调试表。
USE [Your_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Debug_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](60) NOT NULL,
[Value] [sql_variant] NULL,
[Description] [varchar](max) NULL,
[Captured] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
And then in my trigger I did something like this...
然后在我的触发器中我做了这样的事情......
DECLARE @ItemNum nvarchar(128)
SELECT @ItemNum = Item_Number FROM inserted
DECLARE @debugOn as bit = 1
-- Debug
IF @debugOn = 1
BEGIN
INSERT INTO Debug_Table (Name, Value, Description, Captured)
VALUES ( 'Item Number', @ItemNum, 'The item number from the inserted table in tr_VaultItemIterations_ZCode_Monitor.', GETDATE())
END;
-- End Debug
After the trigger fired from the table I could view any of the variables I inserted to the Debug_Table.
从表中触发触发器后，我可以查看插入到 Debug_Table 中的任何变量。
After you're done debugging you could could easily turn off debugging inserts by changing the @debugOnvariable to 0in case you ever need to debug again in the future; or just remove the debug code altogether.
完成调试后，您可以通过将@debugOn变量更改为 轻松关闭调试插入0，以防将来需要再次调试；或者只是完全删除调试代码。
回答by AlexDev
I also wasn't able to Step Into, it would go straight over my INSTEAD OF INSERTtrigger. So I ended up replacing the trigger with:
我也不能Step Into，它会直接越过我的INSTEAD OF INSERT扳机。所以我最终将触发器替换为：
ALTER TRIGGER [MyView_Instead_Insert]
ON [MyView]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
select * into temp from INSERTED
END
Which created a table called temp with exaclty the column names and values of INSERTED.
它创建了一个名为 temp 的表，其中包含INSERTED.
