blocks|key|421728|text|你无法避免触发器的运行。您可以在其中添加条件，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|421729|CREATE+TRIGGER+trigger_name
+++ON+table
+++AFTER+INSERT+
AS
begin
+++IF+(your+condition)+begin
+++++--code
+++END
end|code-block|syntax|javascript|421730|如果你有一个INSTEAD+OF触发器，要小心。如果不对插入进行编码，则不会在表中插入任何内容。|421731|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

you cant avoid a trigger from being run.
What you can do is add conditions in it, for example:

<pre><code>CREATE TRIGGER trigger_name
 ON table
 AFTER INSERT 
AS
begin
 IF (your condition) begin
 --code
 END
end
</code></pre>

just be careful if you have a INSTEAD OF trigger. If you don't code the insert, nothing will be inserted on the table.

blocks|key|636307|text|@Manish:从最佳实践的角度来看，我不认为绕过触发器是一个好的选择。相反，我会评估、考虑并过滤出触发触发器所需的一组条件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|636308|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

@Manish: I do not think bypassing a trigger would be good option form best practices perspective. Instead, I would evaluate, take into consideration and filter out the set of conditions required to fire the trigger.

blocks|key|636375|text|步骤1禁用触发器|type|unstyled|depth|inlineStyleRanges|entityRanges|data|636376|DISABLE+TRIGGER+Person.uAddress+ON+Person.Address;|code-block|syntax|javascript|636377|http://msdn.microsoft.com/en-us/library/ms189748.aspx|offset|length|636378|第2步做一些事情|636379|UPDATE+Person.Address+SET+HouseNumber+=+REPLACE(HouseNumber,+'+',+'');|636380|Step+3启用触发器|636381|ENABLE+Trigger+Person.uAddress+ON+Person.Address;|636382|http://msdn.microsoft.com/en-us/library/ms182706.aspx|636383|--必须说，小心使用！|636384|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|0|1H|0|0|0|0|0|0|0|1H|1|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|16|8|@]|9|@$I|17|J|18|1|19]]|A|$]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1E|8|@]|9|@$I|1F|J|1G|1|1H]]|A|$]]|$1|U|3|V|5|6|7|1I|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|H]]|13|$5|Z|10|11|A|$12|T]]]]

Step 1 Disable Trigger

<pre><code>DISABLE TRIGGER Person.uAddress ON Person.Address;
</code></pre>

<a href="http://msdn.microsoft.com/en-us/library/ms189748.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms189748.aspx</a>

Step 2 Do stuff

<pre><code>UPDATE Person.Address SET HouseNumber = REPLACE(HouseNumber, ' ', '');
</code></pre>

Step 3 Enable Trigger

<pre><code>ENABLE Trigger Person.uAddress ON Person.Address;
</code></pre>

<a href="http://msdn.microsoft.com/en-us/library/ms182706.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms182706.aspx</a>

--
Must say, use with care!

blocks|key|636488|text|您可以通过检查临时表的存在来禁止触发器。需要取消触发器的代码应该创建一个临时表(比如#suppress_trigger)。在触发器中，检查该临时表是否存在并返回。示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|636489|CREATE+TABLE+[dbo].[dummy](
++++[Id]+[int]+IDENTITY(1,1)+NOT+NULL,
++++[Val]+[char](1)+NULL)+++

--create+a+history+table+which+gets+populated+through+trigger
CREATE+TABLE+[dbo].[dummy_hist](
[Id]+[int]+NULL,
[Val]+[char](1)+NULL)+

CREATE+TRIGGER+[dbo].[trig_Insert]
+++ON++[dbo].[dummy]++++
+++AFTER+INSERT
AS+
BEGIN

++++SET+NOCOUNT+ON;
++++if+OBJECT_ID('tempdb..#Dummy_escape_trig')+is+not+NULL
++++++++RETURN

++++INSERT+INTO+dummy_hist
++++SELECT+*+FROM+inserted

END

--Proc+for+which+trigger+needs+to+be+suppressed
CREATE+PROCEDURE+[dbo].[ins_dummy]
++++++++@val+AS+CHAR(1)
AS
BEGIN

++++SET+NOCOUNT+ON;++++

++++CREATE+TABLE+#Dummy_escape_trig+(id+int)

INSERT+INTO+dummy
++++VALUES(@val)
END|code-block|syntax|javascript|636490|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can suppress the trigger by checking for existence of a temp table. The code for which the trigger needs to be suppressed should create a temp table(say #suppress_trigger). In your trigger check for existence of this temp table and return.
Example:

<pre><code>CREATE TABLE [dbo].[dummy](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Val] [char](1) NULL) 

--create a history table which gets populated through trigger
CREATE TABLE [dbo].[dummy_hist](
[Id] [int] NULL,
[Val] [char](1) NULL) 

CREATE TRIGGER [dbo].[trig_Insert]
 ON [dbo].[dummy] 
 AFTER INSERT
AS 
BEGIN

 SET NOCOUNT ON;
 if OBJECT_ID('tempdb..#Dummy_escape_trig') is not NULL
 RETURN

 INSERT INTO dummy_hist
 SELECT * FROM inserted

END

--Proc for which trigger needs to be suppressed
CREATE PROCEDURE [dbo].[ins_dummy]
 @val AS CHAR(1)
AS
BEGIN

 SET NOCOUNT ON; 

 CREATE TABLE #Dummy_escape_trig (id int)

INSERT INTO dummy
 VALUES(@val)
END
</code></pre>

blocks|key|636554|text|创建不同的用户。并在触发器内检查当前用户并执行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|636555|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Create a different user. And inside the trigger check for the current user and execute.

I want to bypass a trigger on some cases, can any one help me on it ?

I have a try with <a href="http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AuditOrBypassTriggerExecution&amp;referringTitle=Home" rel="noreferrer">this link</a> but not able find out the solution.

Thanks in advance

How to bypass trigger on SQL Server 2008

我想在某些情况下绕过触发器，有人能帮我吗？我尝试了一下，但找不到解决方案。提前感谢

问如何绕过SQL Server 2008上的触发器
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何绕过SQL Server 2008上的触发器EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何绕过SQL Server 2008上的触发器
EN