Summary: In this tutorial, you will learn how to use the SQL Server
DISABLE TRIGGER
statement to disable a trigger.Introduction SQL Server DISABLE TRIGGER
Microsoft of recently released a downloaded utility name “Microsoft Kerberos Configuration Manager for SQL Server” which is a diagnostic tool. This tool will help DBAs to troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Analysis Services, and SQL Server Reporting Services. But if i have to implement spell checker in sql server 2005, not using any vd,asp or asp.net script, i want to develop using sql server scrips.
Sometimes, for the troubleshooting or data recovering purpose, you may want to disable a trigger temporarily. To do this, you use the
DISABLE TRIGGER
statement:In this syntax:
- First, specify the name of the schema to which the trigger belongs and the name of the trigger that you want to disable after the
DISABLE TRIGGER
keywords. - Second, specify the table name or view that the trigger was bound to if the trigger is a DML trigger. Use
DATABASE
if the trigger is DDL database-scoped trigger, orSERVER
if the trigger is DDL server-scoped trigger.
SQL Server DISABLE TRIGGER
example
The following statement creates a new table named
sales.members
for the demonstration:The following statement creates a trigger that is fired whenever a new row is inserted into the
sales.members
table. For the demonstration purpose, the trigger just returns a simple message.The following statement inserts a new row into the
sales.members
table:Because of the
INSERT
event, the triggered was fired and printed out the following message:To disable the
sales.trg_members_insert
trigger, you use the following DISABLE TRIGGER
statement:Now if you insert a new row into the
sales.members
table, the trigger will not be fired.It means that the trigger has been disabled.
Note that the trigger definition is still there on the table. If you view the trigger in the SQL Server Management Studio (SSMS), you will notice a red cross icon on the disabled trigger name:
Disable all trigger on a table
To disable all triggers on a table, you use the following statement:
In this statement, you just need to specify the name of the table to disable all triggers that belong to that table.
The following statement creates a new trigger on the
sales.members
table which is fired after delete event:To disable all triggers on the
sales.members
table, you use the following statement:The following picture shows the status of all triggers that belongs to the
sales.members
table:Sql Server Turn Off Messages
Disable all triggers on a database
Turn Off Spell Check Sql Server Error Logs
To disable all triggers on the current database, you use the following statement:
Turn Off Spell Check-in Yahoo Mail
In this tutorial, you have learned how to use the SQL Server
DISABLE TRIGGER
statement to disable a trigger.