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;

This page was last updated on May 01, 2006 04:28 PM.