Syntax to disable and re-enable SQL Server level trigger?

Question

Hello, what is the TSQL syntax to disable SQL Server Level (not DB level) trigger and then re-enable it for both SQL 2014 & SQL 2016?

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

You can using below T-SQL to disable and enable SQL Server Level trigger.

Disable server lever trigger;

------one trigger  
DISABLE TRIGGER TriggerName ON SERVER;  
------All trigger  
DISABLE TRIGGER ALL ON SERVER;  

Enable server lever trigger;

------one trigger  
ENABLE TRIGGER TriggerName ON SERVER;  
------All trigger  
ENABLE Trigger ALL ON ALL SERVER;    

To enable( or disable) a DDL trigger with server scope (ON ALL SERVER) or a logon trigger, a user needs CONTROL SERVER permission on the server.

Please refer to DISABLE TRIGGER (Transact-SQL) and ENABLE TRIGGER (Transact-SQL) to get more information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks bunch Cathyji-msft .