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

Answer 1

Another option would be to include Application Name=MyAppName; in your connection string and in the trigger add a conditional using the SQL APP_NAME function.

IF(APP_NAME() = 'MyAppName')

Answer 2

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

Answer 3

There are some practical problems with disabling a trigger in a production environment. I'm presuming you will be disabling table triggers specifically (as opposed to database or server-wide):

1. You need ALTER permissions on the table operated on. This is generally considered problematic for security reasons. It gets far more problematic at the database and server level.
2. The disabling is not limited to your specific connection/session, but will affect all events that fire the trigger, from whatever sessions, while it is disabled. If you have any expectancy of concurrency in the code in question, then you have to assume the same code is going to be called from other threads. What is missing from the earlier suggestions is the full consideration of the concurrency management that using a semaphore of any kind requires. In particular, that temp table defines a semaphore for a protected section of code (in the multi-threading sense) where the semaphore is the existence of the temp table. IF you actually disable the trigger, then to use that temp table for its intended purpose correctly, there has to be a test just before the protected code creates the temp table, to see if it exists already. This may require a global temp table, or you can do a more clever search of the tempdb to see if it exists in any session. The protected code has to test if the semaphore exists, and enter a blocking state if it intends to wait for the resource (the trigger, in this case) to be available as expected. This get complex, and has little value in exchange for the complexity.

Opinion: While it's easy to disable/enable triggers, there are a lot of considerations to be careful of if you do disable temporarily them as part of some specific use case, as this is engineering what should be a concurrency management strategy.

Unless you're relatively comfortable with the nuts and bolts of concurrency management, or unless you have absolutely no requirement for the code to be reentrant/concurrent, you will (likely sporadically) have problems if you user DISABLE TRIGGER, but don't consider these factors.

The safest path I can see, considering it all, is to not disable the trigger, use a local temp table as a semaphore that affects only the current session, carefully code the exit code so that the temp table is definitely destroyed at the end of the protected code, as already suggested, but it still requires a check/block, even though it will only matter when on the same connection, and particularly in parallel execution on same connection. The absolutely safest way to do this is to create an sproc for the protected code alone. The sproc checks/blocks, then if it proceeds (and you'll need to check for deadlock error after the blocking code exits), creates the temp table. Since temps are destroyed when the sproc returns, any path out of the protected code will handle the semaphore. But temp tables are available throughout the session - not just within the sproc (while the sproc is running), or just within a batch even. SQL Server supports parallel queries on a single session, so the temp table created in the one thread of the session is visible in any others. That means it can be seen OUTSIDE the sproc, in the same session, and in fact, the same code could be run at that time. That's why you STILL need real concurrency management in this scenario.

And finally, my apologies for the convoluted comment. I find just about every discussion of multi-threading and concurrency management turns into that, because while the concepts aren't all that difficult, the coding practices have long been considered delicate and fragile, and prone to developer error.

Answer 4

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!

Answer 5

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

Answer 6

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

Answer 7

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.