|
|
|
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
CREATE PROCEDURE InsTestTblRow AS CREATE TABLE #dont_fire ( DummyCol int ); INSERT INTO TestTbl values ( 11 ); go
INSERT INTO TestTbl values ( 1 );
EXEC InsTestTblRow; go DROP PROC InsTestTblRow; DROP TABLE TestTbl; GO
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
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 );
EXEC InsTestTblRow; go DROP PROC InsTestTblRow; DROP TABLE TestTbl; |
|
|
|
Submitted By :
Nayan Patel
(Member Since : 5/26/2004 12:23:06 PM)
|
|
|
Job Description :
He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting. |
View all (893) submissions by this author
(Birth Date : 7/14/1981 ) |
|
|