Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

Conditional Firing of Triggers.

Total Hit ( 2135)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


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;


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 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.