Administering Microsoft SQL Server Trigger Maintenance

The following commands may be helpful when administering triggers.

List All Triggers in a Database

This command lists all triggers in a database:

SELECT name  FROM sysobjects WHERE type = 'TR'

List the Trigger Definition

This command lists the trigger definition:

sp_helptext TRIGGERNAME

List Trigger Information

This command lists trigger information:

sp_helptrigger BASE TABLE NAME

Returns the type or types of triggers that are defined on the specified table for the current database.

sp_help TRIGGERNAME

Reports information about a database object (any object listed in the SYSOBJECTS table), a user-defined data type, or a data type that Microsoft SQL Server supplies.

To Remove a Trigger

To remove a trigger:

drop trigger TRIGGERNAME

To Modify an Existing Trigger

To modify a trigger:

ALTER trigger ...

This alters the definition of a trigger that is created previously by the CREATE TRIGGER statement.

See the full definition in SQL Server Books Online.

See SQL Server Books Online for the full example.

To Disable a Trigger

To disable a trigger:

ALTER TABLE <table> | (ENABLE | DISABLE) TRIGGER	(ALL | trigger_name[,...n])

(ENABLE | DISABLE ) TRIGGER - Specifies that trigger_name is enabled or disabled. When a trigger is disabled, it is still defined for the table; however, when INSERT, UPDATE or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled.

  • ALL: Specifies that all triggers in the table are enabled or disabled.

  • trigger_nam: Specifies the name of the trigger to disable or enable.