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