Drop Trigger

DROP TRIGGER (Transact-SQL)

Removes one or more DML or DDL triggers from the current database.

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

      DROP TRIGGER [schema_name.]trigger_name [ ,...n ] [ ; ]

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE statement (DDL Trigger)

DROP TRIGGER trigger_name [ ,...n ]

ON {DATABASE | ALL SERVER }

[ ; ]

Trigger on a LOGON event (Logon Trigger)

DROP TRIGGER trigger_name [ ,...n ]

ON ALL SERVER

Arguments

schema_name

Is the name of the schema to which a DML trigger belongs. DML triggers are scoped to the schema of the table or view on which they are created.

Schema_name cannot be specified for DDL or logon triggers.

trigger_name

Is the name of the trigger to remove. To see a list of currently created triggers, use sys.server_assembly_modules or sys.server_triggers.

DATABASE

Indicates the scope of the DDL trigger applies to the current database. DATABASE must be specified if it was also specified when the trigger was created or modified.

ALL SERVER

Indicates the scope of the DDL trigger applies to the current server. ALL SERVER must be specified if it was also specified when the trigger was created or modified. ALL SERVER also applies to logon triggers.

Remarks

You can remove a DML trigger by dropping it or by dropping the trigger table. When a table is dropped, all associated triggers are also dropped.

When a trigger is dropped, information about the trigger is removed from the sys.objects, sys.triggers and sys.sql_modules catalog views.

Multiple DDL triggers can be dropped per DROP TRIGGER statement only if all triggers were created using identical ON clauses.

To rename a trigger, use DROP TRIGGER and CREATE TRIGGER. To change the definition of a trigger, use ALTER TRIGGER.

For more information about determining dependencies for a specific trigger, see sys.sql_expression_dependencies, sys.dm_sql_referenced_entities (Transact-SQL), and sys.dm_sql_referencing_entities (Transact-SQL).

For more information about viewing the text of the trigger, see sp_helptext (Transact-SQL) and sys.sql_modules (Transact-SQL).

For more information about viewing a list of existing triggers, see sys.triggers (Transact-SQL) and sys.server_triggers (Transact-SQL).

Permissions

To drop a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

To drop a DDL trigger defined with server scope (ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission in the server. To drop a DDL trigger defined with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

Dropping a DML trigger

USE AdventureWorks2008R2;

GO

IF OBJECT_ID ('employee_insupd', 'TR') IS NOT NULL

   DROP TRIGGER employee_insupd;

GO

Dropping a DDL trigger

Because DDL triggers are not schema-scoped and, therefore do not appear in the sys.objects catalog view, the OBJECT_ID function cannot be used to query whether they exist in the database. Objects that are not schema-scoped must be queried by using the appropriate catalog view. For DDL triggers, use sys.triggers.

USE AdventureWorks2008R2;

GO

IF EXISTS (SELECT * FROM sys.triggers

    WHERE parent_class = 0 AND name = 'safety')

DROP TRIGGER safety

ON DATABASE;

GO

Drop all Triggers belonging to any schema

DECLARE @SQLCmd nvarchar(1000)

DECLARE @Trig varchar(500)

DECLARE @sch varchar(500)

DECLARE TGCursor CURSOR FOR

SELECT ISNULL(tbl.name, vue.name) AS [schemaName]

     , trg.name AS triggerName

FROM sys.triggers trg

LEFT OUTER JOIN (SELECT tparent.object_id, ts.name

                 FROM sys.tables tparent

                 INNER JOIN sys.schemas ts ON TS.schema_id = tparent.SCHEMA_ID)

                 AS tbl ON tbl.OBJECT_ID = trg.parent_id

LEFT OUTER JOIN (SELECT vparent.object_id, vs.name

                 FROM sys.views vparent

                 INNER JOIN sys.schemas vs ON vs.schema_id = vparent.SCHEMA_ID)

                 AS vue ON vue.OBJECT_ID = trg.parent_id

OPEN TGCursor

FETCH NEXT FROM TGCursor INTO @sch,@Trig

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + @sch + '].[' + @Trig + ']'

EXEC sp_executesql @SQLCmd

PRINT @SQLCmd

FETCH next FROM TGCursor INTO @sch,@Trig

END

CLOSE TGCursor

DEALLOCATE TGCursor