SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disabling Trigger on Replicated Table


Disabling Trigger on Replicated Table

Author
Message
Paul A
Paul A
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 305
Hello,

Can someone please help.

I have a job which runs everyday, and has a line of code:

ALTER TABLE DBName..TableName DISABLE TRIGGER TriggerName

Unfortunatelly the Table is part of transactional replication, and I recieve the following message:



Is there anyway of disabling a trigger on a replicated table without dropping the article from the publication??

The Publication exists on a SQL Server 2000 database and is subscribed to from a SQL Server 2005 database, with a SQL 2005 distributor.

Thanks
Mike_D
Mike_D
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 Visits: 615
It would be interesting to know why you need to disable the trigger. I'm guessing that dropping the trigger and then re-creating it is not an option as I'm sure you would have thought of that.

A potentially insane idea would be to create a table and use that to control the behaviour of your trigger. The table would have one column, let's say IsEnabled bit NOT NULL. The column is then set to 1 or 0 depending on whether the trigger is to be enabled or disabled. Then modify your trigger to enclose all the code in a IF EXISTS, i.e.

IF EXISTS (SELECT * FROM TriggerEnabled WHERE IsEnabled = 1)
BEGIN
.................
END

I say this is potentially insane because if the trigger fires too often you may grind everything to a halt as a result of constantly hitting that new table. I've seen it before.

And I guess upgrading your publisher to SQL Server 2005 is not an option?

Mike
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9548 Visits: 2048
Although this is a SQL 2000 issue and you are in a 2005 forum I am going to give you a clue on how to solve it.

Don't disable the trigger just add some "state" table and check it on the trigger code
(if state is ON, proceed with the trigger code else return from it)

HTH


* Noel
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search