How to bypass trigger on SQL Server 2008

Question

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

I have a try with this link but not able find out the solution.

Thanks in advance

Solution 1

you cant avoid a trigger from being run. What you can do is add conditions in 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 a INSTEAD OF trigger. If you don't code the insert, nothing will be inserted on the table.

Solution 2

Step 1 Disable Trigger

DISABLE TRIGGER Person.uAddress ON Person.Address;

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

Step 2 Do 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

-- Must say, use with care!

Solution 3

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:

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

Solution 4

@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.

View more solutions