Binaryworld - Conditional Firing of Triggers. ... [ SQL Server -> Trigger ]

This sample shows how you can conditionally fire triggers. This example shows how to suppress the trigger logic while inserting data from a SP & allowing other inserts to go through fine. A SQL6x/70/2000 version of the example is shown first & another one using the SQL70/2000 cursor function is shown next.

Click here to copy the following block

USE tempdb;
go
CREATE TABLE TestTbl( Col1 int );
go
CREATE TRIGGER ConditionalTrigger
ON TestTbl
FOR INSERT
AS
IF OBJECT_ID( 'tempdb..#dont_fire' ) IS NOT NULL
RETURN;

PRINT 'Performing trigger logic...';
go

-- Test SP
CREATE PROCEDURE InsTestTblRow
AS
CREATE TABLE #dont_fire ( DummyCol int );
INSERT INTO TestTbl values ( 11 );
go

INSERT INTO TestTbl values ( 1 );
-- Don't fire trigger when inserting via SP
EXEC InsTestTblRow;
go
DROP PROC InsTestTblRow;
DROP TABLE TestTbl;
GO

-- Another version:
-- SQL70 Example using cursors
CREATE TABLE TestTbl( Col1 int );
go
CREATE TRIGGER ConditionalTrigger
ON TestTbl
FOR INSERT
AS
IF CURSOR_STATUS( 'global' , 'dont_fire') >= 0
RETURN;

PRINT 'Performing trigger logic...';
go

-- Test SP
CREATE PROCEDURE InsTestTblRow
AS
DECLARE dont_fire CURSOR FOR SELECT 1;
OPEN dont_fire;
INSERT INTO TestTbl values ( 11 );
DEALLOCATE dont_fire;
go

INSERT INTO TestTbl values ( 1 );
-- Don't fire trigger when inserting via SP
EXEC InsTestTblRow;
go
DROP PROC InsTestTblRow;
DROP TABLE TestTbl;