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