How to bypass a trigger on SQL Server 2008

Question

I want to bypass the trigger in some cases, can someone help me with this?

I have tried with this link , but am not able to find a solution.

Thanks in advance

Answer 1

you cannot avoid triggering a trigger. What you can do is add conditions to it, for example:

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

Just be careful if you have an INSTEAD OF trigger. If you do not encode the insert, nothing will be inserted into the table.

Answer 2

Step 1 Disable Trigger

 DISABLE TRIGGER Person.uAddress ON Person.Address; 

http://msdn.microsoft.com/en-us/library/ms189748.aspx

Step 2 do the stuff

 UPDATE Person.Address SET HouseNumber = REPLACE(HouseNumber, ' ', ''); 

Step 3 Enable Trigger

 ENABLE Trigger Person.uAddress ON Person.Address; 

http://msdn.microsoft.com/en-us/library/ms182706.aspx

- I must say, use with caution!

Answer 3

@Manish: I don’t think that bypassing the trigger there will be a good prospect of choosing the best practice. Instead, I will evaluate, take into account, and filter out the set of conditions necessary to trigger the trigger.

Answer 4

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

 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