How to enable/disable trigger in procedure?

Question

I have two databases (firstDB and secondDB) on MS SQL server. There is procedure [dbo].[MaintaineIndexes] in firstDB and DatabaseEventsTrigger in secondDB.

I want to disable secondDB.DatabaseEventsTrigger when I am running [dbo].[MaintaineIndexes].

So I tried:

ALTER PROCEDURE [dbo].[MaintaineIndexes] 
AS
BEGIN
DISABLE TRIGGER DatabaseEventsTrigger ON secondDB; 
...
ENABLE TRIGGER DatabaseEventsTrigger ON secondDB;
END;

When I'm running procedure I'm getting error message: "Cannot find the object "secondDB" because it does not exist or you do not have permissions.";

Ok, then I tryed:

DISABLE TRIGGER DatabaseEventsTrigger ON ALL SERVER;

• same error.

Disabling trigger only working with this commands:

USE [secondDB]
GO
DISABLE TRIGGER DatabaseEventsTrigger ON DATABASE; 

But we can't use USE in procedures! So, how to enable/disable triggers in my situation?

Answer 1

Use Dynamic SQL:

ALTER PROCEDURE [dbo].[MaintaineIndexes] 
AS
BEGIN
   EXEC('DISABLE TRIGGER DatabaseEventsTrigger ON secondDB'); 
   ...
   EXEC('ENABLE TRIGGER DatabaseEventsTrigger ON secondDB');
END;

OR:

ALTER PROCEDURE [dbo].[MaintaineIndexes] 
AS
BEGIN
   EXEC('USE [secondDB]; DISABLE TRIGGER DatabaseEventsTrigger ON DATABASE;'); 
   ...
   EXEC('USE [secondDB]; ENABLE TRIGGER DatabaseEventsTrigger ON DATABASE;'); 
END;