DROP TRIGGER (T-SQL)

Removes one or more triggers from the current database.

Syntax

DROP TRIGGER {trigger} [,...n]

Arguments

trigger: Is the name of the trigger(s) to remove. Trigger names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the trigger owner name is optional. To see a list of currently created triggers, use sp_helptrigger.
n: Is a placeholder indicating that multiple triggers can be specified.

Remarks

You can remove a trigger by dropping it or by dropping the trigger table. When a table is dropped, all triggers associated with it are also dropped. When a trigger is dropped, information about the trigger is removed from the sysobjects and syscomments system tables.

Use DROP TRIGGER and CREATE TRIGGER to rename a trigger. Use ALTER TRIGGER to change the definition of a trigger.

For more information about determining dependencies for a specific trigger, see sp_depends.

For more information about viewing the text of the trigger, see sp_helptext.

For more information about viewing a list of existing triggers, see sp_helptrigger.

Permissions

DROP TRIGGER permissions default to the trigger table owner, and is not transferable. However, members of the db_owner and ddl_admin fixed database role or sysadmin fixed server role can drop any object by explicitly specifying the owner in the DROP TRIGGER statement.

Examples

This example drops the employee_insupd trigger.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'employee_insupd' AND type = 'TR')

DROP TRIGGER employee_insupd

GO