Transact-SQL Reference

DROP TRIGGER

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 about rules for identifiers, 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 associated triggers 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" in this volume.

For more information about viewing the text of the trigger, see "sp_helptext" in this volume.

For more information about viewing a list of existing triggers, see "sp_helptrigger" in this volume.

Permissions

DROP TRIGGER permissions default to the trigger table owner, and are not transferable. However, members of the db_owner and db_dlladmin 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

See Also

ALTER TRIGGER

CREATE TRIGGER

sp_help

syscomments

sysobjects