DISABLE/ENABLE TRIGGER across a Linked Server

[Richard Kirk]

Hi there,

I need to be able to disable a trigger in an SP whilst I perform an update
and then re-enable it, but the trigger is on a table on a Linked Server.

What permissions will need to be on the remote database to allow DISABLE
TRIGGER?

Does anyone know the syntax for DISABLE TRIGGER across a Linked Server?

If I use :
DISABLE TRIGGER CONTACT_dateedit ON [srvb].[db1].[dbo].contacts

I get :

The object name 'srvb.db1.dbo.contacts' contains more than the maximum
number of prefixes. The maximum is 2.

Many thanks,

Larry.

[Erland Sommarskog]

Most likely, you should reconsider what you are doing entirely. You can
achieve what you want with EXEC() AT, but what is some other user is
performing an operation on the table in the meanwhile? What if your batch
is aborted and never renables the trigger?

The best way to disable a trigger from a certain operation is to write the
trigger like:

IF object_id('tempdb..#stopit') IS NOT NULL
RETURN

and then create the temp in the operation that needs to sneak by the
trigger. To do this with a linked server, you would have to everything
with EXEC() AT, which may not be feasible if the update includes local
data.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Uri Dimant]

Richard
Are you using SQL Server 2005?
I have not tested it by my self, but
EXEC ('ALTER TABLE tbl DISABLE TRIGGER CONTACT_dateedit') AT LinkedServer

"Richard Kirk" <desirepa...@ntlworld.com> wrote in message
news:%23pVIPAL...@TK2MSFTNGP05.phx.gbl...

[Richard Kirk]

Thanks guys,

The whole thing is a mess so I can't make it worse!

I don't have any control over the remote server, so can't go about rewriting
their triggers, etc to do this more sensibily.

I also need to request a change in permissions from their DBA, which is why
I need to know what permissions to request to allow me to EXEC ALTER TABLE.

Thanks again,
Larry.

[Erland Sommarskog]

If you need to know which permission that are required for a certain
command, all entries in the T-SQL Reference of Books Online, has a
Permissions section where you can look this up.

In this case, it is the login on the remote server that your local
login is mapped that needs the permissions.