May 7, 2009 at 3:45 am
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
May 8, 2009 at 7:51 am
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
May 8, 2009 at 7:53 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply