Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Disabling Trigger on Replicated Table Expand / Collapse
Author
Message
Posted Thursday, May 07, 2009 3:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 3:09 AM
Points: 95, Visits: 292
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

Post #711857
Posted Friday, May 08, 2009 7:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
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
Post #712922
Posted Friday, May 08, 2009 7:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
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
Post #712925
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse