Bypassing A Trigger

Several ways to bypass a trigger can be found in an excellent book "Inside MQ SQL Server 2005: T_SQl Querying". Here is yet another one.

Suppose you have a trigger that records all the changes against a table, as follows:

CREATE TABLE Party(PartyID INT NOT NULL PRIMARY KEY, LastName VARCHAR(20),
FirstName VARCHAR(20), Salutation VARCHAR(20))
go

CREATE TABLE PartyHistory(PartyID INT NOT NULL, LastName VARCHAR(20), FirstName
VARCHAR(20), Salutation VARCHAR(20),
ModifiedBy VARCHAR(100), ModifiedAt DATETIME)
go

CREATE TRIGGER PartyUpd ON Party
FOR UPDATE
AS
PRINT 'Entering Trigger PartyUpd'
INSERT INTO [dbo].[PartyHistory]([PartyID], [LastName], [FirstName],
[Salutation], [ModifiedBy], [ModifiedAt])
SELECT [PartyID], [LastName], [FirstName], [Salutation], SYSTEM_USER,
CURRENT_TIMESTAMP
FROM Deleted
go

INSERT dbo.[Party]([PartyID], [LastName], [FirstName], [Salutation])
VALUES(1, 'Doe', 'John', 'Junior')
INSERT dbo.[Party]([PartyID], [LastName], [FirstName], [Salutation])
VALUES(2, 'Doe', 'John', 'Junior')
go
UPDATE dbo.[Party] SET [LastName] = 'Cooper', [FirstName] = 'Stanley'
WHERE partyID = 1
go
SELECT * FROM PartyHistory
/*
PartyID LastName FirstName Salutation
ModifiedBy
ModifiedAt
----------- -------------------- -------------------- --------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------
1 Doe John Junior
US\akuznetsov
2006-12-04 16:42:12.480

(1 row(s) affected)
*/

Suppose you want to modify Salutation column, as follows

UPDATE Party SET Salutation = 'Jr.' WHERE Salutation = 'Junior'

And you do not want the trigger to fire. Make sure that RECURSIVE_TRIGGERS is disabled (which is the default):

SELECT DATABASEPROPERTYEX('SANDBOX','IsRecursiveTriggersEnabled')
----
0

Retrieve your spid:

SELECT @@spid
/*
------
58

(1 row(s) affected)

*/

And wrap your update in a trigger:

CREATE TRIGGER Bypass_PartyUpd ON Party
FOR UPDATE
AS
PRINT 'Entering Trigger Bypass_PartyUpd'
PRINT @@SPID
IF @@spid = 58
UPDATE Party SET Salutation = 'Jr.' WHERE Salutation = 'Junior'
go

Now all you need to do is run an update which does not modify any rows

UPDATE Party Set LastName ='asfdsdf' WHERE PartyID<0
go

The new trigger will fire and update Salutation. Because RESURSIVE_TRIGGERS is turned off, the old trigger will not fire:

SELECT * FROM Party
/*
PartyID LastName FirstName Salutation
----------- -------------------- -------------------- --------------------
1 Cooper Stanley Jr.
2 Doe John Jr.
*/
go
SELECT * FROM PartyHistory
/*
--- still only one row in PartyHistory
PartyID LastName FirstName Salutation
ModifiedBy
ModifiedAt
----------- -------------------- -------------------- --------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------
1 Doe John Junior
US\akuznetsov
2006-12-04 16:51:49.257
*/
go

If you try an update which clearly will not update any rows no matter what:

UPDATE Party Set LastName ='asfdsdf' WHERE (1=0)

The optimizer will be smart enough to not execute the update, so the trigger will not run at all.

The advantage of this approach is that you do not need to touch the original trigger at all. The disadvantage is that you must not forget to drop the new trigger.