SQL MSSQL：禁用一个 INSERT 的触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/495629/
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
MSSQL: Disable triggers for one INSERT
提问by BlaM
This question is very similar to SQL Server 2005: T-SQL to temporarily disable a trigger
这个问题和SQL Server 2005非常相似：T-SQL 临时禁用触发器
However I do not want to disable all triggers and not even for a batch of commands, but just for one single INSERT.
但是，我不想禁用所有触发器，甚至不想禁用一批命令，而只想禁用一个 INSERT。
I have to deal with a shop system where the original author put some application logic into a trigger (bad idea!). That application logic works fine as long as you don't try to insert data in another way than the original "administration frontend". My job is to write an "import from staging system" tool, so I have all data ready. When I try to insert it, the trigger overwrites the existing Product Code (not the IDENTITY numeric ID!) with a generated one. To generate the Code it uses the autogenerated ID of an insert to another table, so that I can't even work with the @@IDENTITY to find my just inserted column and UPDATE the inserted row with the actual Product Code.
我必须处理一个商店系统，其中原作者将一些应用程序逻辑放入触发器中（坏主意！）。只要您不尝试以原始“管理前端”以外的其他方式插入数据，该应用程序逻辑就可以正常工作。我的工作是编写一个“从登台系统导入”工具，以便我准备好所有数据。当我尝试插入它时，触发器会用生成的产品代码覆盖现有的产品代码（不是 IDENTITY 数字 ID！）。为了生成代码，它使用插入到另一个表的自动生成的 ID，因此我什至无法使用 @@IDENTITY 来查找我刚刚插入的列并使用实际产品代码更新插入的行。
Any way that I can go to avoid extremly awkward code (INSERT some random characters into the product name and then try to find the row with the random characters to update it).
我可以采取的任何方式来避免极其尴尬的代码（在产品名称中插入一些随机字符，然后尝试找到带有随机字符的行来更新它）。
So: Is there a way to disable triggers (even just one) for just oneINSERT?
那么：有没有一种方法可以为一次INSERT禁用触发器（甚至只有一个）？
采纳答案by Steven Robbins
You can disable triggers on a table using:
您可以使用以下方法禁用表上的触发器：
ALTER TABLE MyTable DISABLE TRIGGER ALL
But that would do it for all sessions, not just your current connection.. which is obviously a very bad thing to do :-)
但这将适用于所有会话，而不仅仅是您当前的连接..这显然是一件非常糟糕的事情:-)
The best way would be to alter the trigger itself so it makes the decision if it needs to run, whether that be with an "insert type" flag on the table or some other means if you are already storing a type of some sort.
最好的方法是更改触发器本身，以便它决定是否需要运行，无论是在表上使用“插入类型”标志还是其他方式（如果您已经存储某种类型）。
回答by kristof
You may find this helpful:
您可能会发现这很有帮助：
Disabling a Trigger for a Specific SQL Statement or Session
But there is another problem that you may face as well. If I understand the situation you are in correctly, your system by default inserts product code automatically(by generating the value). Now you need to insert a product that was created by some staging system, and for that product its product code was created by the staging system and you want to insert it to the live system manually.
但是，您也可能面临另一个问题。如果我正确理解您所处的情况，您的系统默认会自动插入产品代码（通过生成值）。现在您需要插入一个由某个暂存系统创建的产品，并且该产品的产品代码是由暂存系统创建的，您希望手动将其插入到实时系统中。
If you really have to do it you need to make sure that the codes generated by you live application in the future are not going to conflict with the code that you inserted manually - I assume they musty be unique.
如果您真的必须这样做，您需要确保将来由您的实时应用程序生成的代码不会与您手动插入的代码发生冲突 - 我认为它们是唯一的。
Other approach is to allow the system to generate the new code and overwrite any corresponding data if needed.
另一种方法是允许系统生成新代码并在需要时覆盖任何相应的数据。
回答by andynormancx
Rather than disabling triggers can you not change the behaviour of the trigger. Add a new nullable column to the table in question called "insertedFromImport".
您不能更改触发器的行为，而不是禁用触发器。向有问题的表中添加一个新的可为空的列，称为“insertedFromImport”。
In the trigger change the code so that the offending bit of the trigger only runs on rows where "insertedFromImport" is null. When you insert your records set "insertedFromImport" to something non-null.
在触发器中更改代码，以便触发器的违规位仅在“insertedFromImport”为空的行上运行。当您将记录集“insertedFromImport”插入到非空值时。
回答by Clayton Hall
Disable the trigger, insert, commit.
禁用触发器、插入、提交。
SET IDENTITY_INSERT Test ON
GO
BEGIN TRAN
DISABLE TRIGGER trg_Test ON Test
INSERT INTO Test (MyId, MyField)
VALUES (999, 'foo')
ENABLE TRIGGER trg_Test ON Test
COMMIT TRAN
SET IDENTITY_INSERT Test OFF
GO
回答by HLGEM
I see many things that could create a problem. First change the trigger to consider multiple record imports. That may probably fix your problem. DO not turn off the trigger as it is turned off for everyone not just you. If you must then put the database into single user user mode before you do it and do your task during off hours.
我看到很多事情可能会造成问题。首先更改触发器以考虑多个记录导入。这可能会解决您的问题。不要关闭触发器，因为它对每个人都关闭，而不仅仅是你。如果您必须在执行此操作之前将数据库置于单用户用户模式并在下班时间执行您的任务。
Next, do not under any circumstances ever use @@identity to get the value just inserted! USe scope_identity instead. @@identity will return the wrong value if there are triggers onthe table that also do inserts to other tables with identity fields. If you are using @@identity right now through your system (since we know your system has triggers), your abosolute first priority must be to immediately find and change all instances of @@identity in your code. You can have serious data integrity issues if you do not. This is a "stop all work until this is fixed" kind of problem.
接下来，在任何情况下都不要使用@@identity 来获取刚刚插入的值！使用 scope_identity 代替。如果表上的触发器也对具有标识字段的其他表进行插入，@@identity 将返回错误的值。如果您现在正在通过您的系统使用 @@identity（因为我们知道您的系统有触发器），那么您绝对的首要任务必须是立即找到并更改代码中 @@identity 的所有实例。如果不这样做，您可能会遇到严重的数据完整性问题。这是一个“在修复之前停止所有工作”的问题。
As far as getting the information you just inserted back, consider creating a batchid as part of you insert and then adding a column called batchid (which is nullable so it won't affect other inserts)to the table. Then you can call back what you inserted by batchid.
至于获取您刚刚插入的信息，请考虑创建一个 batchid 作为插入的一部分，然后向表中添加一个名为 batchid 的列（它可以为空，因此不会影响其他插入）。然后你可以回调你通过batchid插入的内容。
回答by Justin Wignall
Can you check for SUSER_SNAME() and only run when in context of the administration frontend?
您可以检查 SUSER_SNAME() 并且仅在管理前端的上下文中运行吗？
回答by John
If you insert using BULK INSERT, you can disable triggers just for the insert.
如果您使用 BULK INSERT 插入，则可以仅为插入禁用触发器。
I'm pretty sure bulk insert will require a data file on the file system to import so you can't just use T-SQL.
我很确定批量插入需要文件系统上的数据文件才能导入，因此您不能只使用 T-SQL。
To use BULK INSERT you need INSERT and ADMINISTRATOR BULK OPERATION permissions. If you disable triggers or constraints, you'll also need ALTER TABLE permission.
要使用 BULK INSERT，您需要 INSERT 和 ADMINISTRATOR BULK OPERATION 权限。如果禁用触发器或约束，则还需要 ALTER TABLE 权限。
If you are using windows authentication, your windows user will need read access from the file. if using Mixed Mode authentication, the SQl Server Service account needs read access from the file.
如果您使用 Windows 身份验证，您的 Windows 用户将需要对文件进行读取访问。如果使用混合模式身份验证，SQl 服务器服务帐户需要对文件进行读取访问。
When importing using BULK IMPORT, triggers are disabled by default.
使用 BULK IMPORT 导入时，默认情况下禁用触发器。
More information: http://msdn.microsoft.com/en-us/library/ms188365.aspx
