Replicating Triggers

By Chris Miller on 14 August 2000 | 0 Comments | Tags: Replication, Triggers


Miles writes "An associate of mine and I were attempting to include database triggers that we have developed in the replication of one of our databases. I had the idea of creating a trigger on the syscomments table so that whenever a trigger was updated on the master dB, all the other replicated databases would be updated with the newest version of the trigger. To our dismay, we were not allowed to create triggers on the system table. Is there any other way to do this? Thanks in advnace for your help!

At this time, in this version of SQL Server (7.0) you're not allowed to replicate triggers. Sorry, not the answer you were looking for.

Now...here's one way to do it. I'd suggest using a polling-application, or manually triggering the replication, and then using a remote call to xp_cmdshell to ISQL. Set the polling stored proc up to query something that contains the checksums from syscomments. Grab the actual trigger text from syscomments and build an ISQL query string, and then do a remote call via linked server to xp_cmdshell:

exec @retval = FooServer.master.dbo.xp_cmdshell 'isql -Usa -P -Q "create trigger blah blah blah"'

You can use the value in @RetVal to make sure the query actually ran. Books Online has a list of the return codes and other parameters (integrated security).

This is pretty typical of my 'sledgehammer-based' problem solving technique, and is overall a pretty evil way to do a lot of really nasty things. Be careful.


rocketscientist.

Discuss this article: 0 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using DDL Triggers in SQL Server 2005 to Capture Schema Changes (13 August 2007)

Replicating SQL Server 2000 across Heterogeneous Databases (24 August 2003)

Horizontal and Vertical Partitioning in Replication (30 January 2003)

Database Journal - SQL Server section (18 December 2002)

Audit Triggers for SQL Server (8 May 2002)

Code to find out the statement that caused the trigger to fire! (16 April 2002)

An Introduction to Triggers -- Part II (4 November 2001)

An Introduction to Triggers -- Part I (30 April 2001)

Other Recent Forum Posts

Update values in two tables via stored procedure (3 Replies)

Update rows with increment date (19 Replies)

sqlit, transaction (1 Reply)

Return a row with null values (1 Reply)

Exporting SQL table to text from in vs2010 (2 Replies)

transaction (2 Replies)

Multiple criteria Rating (2 Replies)

t-sql call stored procedure with 3 parameters (4 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -