Learn Sql Server   Learn Sql Theory   Learn Sql

 
Chapter 1
Chapter 2
Chapter 3
Chapter 4

Sql Server Indexes

Create Table Index

Sql Server Drop indexes

Sql Server FREETEXT

Sql Server Contains

Sql Server FREETEXTTABLE

Sql Server Views

Sql Server Return

Sql Server View Encryption

Sql Server Views With Order By

Sql Server WITH CHECK OPTION

Create Indexed view WITH SCHEMABINDING

Sql Server Stored Procedure

Parameterized Stored Procedure

Stored Procedure with Output Parameter

Fetch Stored Procedure

Update Stored Procedure

Delete Stored Procedure

Sql Server Cursor

Sql Server DML Triggers

Sql Server INSTEAD OF Trigger

Sql Server DDL Triggers

Alter Or Modifying Triggers

sql-server-disable-triggers-drop-triggers

Enable triggers in sql server

create index with drop existing

Functions Upper() and Lower()

Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

Disable And drop triggers in Sql Server

Dropping a Trigger

The syntax for dropping a trigger differs by trigger type (DML or DDL). The syntax for dropping a DML trigger is as follows:

DROP TRIGGER trigger_name [ ,...n ]

In the case of both DDL and DML syntax statements, the [ ,...n ] syntax block indicates that more than one trigger can be dropped at the same time.
This example demonstrates dropping a DML and a DDL trigger:

Drop a DML trigger

DROP TRIGGER dbo.trg_i_TestTriggerOrder

Drop multiple DML triggers

DROP TRIGGER dbo.trg_i_TestTriggerOrder2, dbo.trg_i_TestTriggerOrder3

Drop a DDL trigger

DROP TRIGGER db_trg_RestrictINDEXChanges ON DATABASE

How It Works

In this recipe, DML and DDL triggers were explicitly dropped using the DROP TRIGGER command. You will also drop all DML triggers when you drop the table or view that they are bound to. You can also remove multiple triggers in the same DROP command if each of the triggers were created using the same ON clause.

Disable Trigger

Sometimes triggers must be disabled if they are causing problems that you need to troubleshoot, or if you need to import or recover data that shouldn’t fire the trigger. In this recipe, I demonstrate how to disable a trigger from firing using the new SQL Server 2005 DISABLE TRIGGER command, as well as The syntax for DISABLE TRIGGER is as follows:

DISABLE TRIGGER [ schema . ] trigger_name
ON { object_name | DATABASE | SERVER }

Sponsored Links