Home » SQL Server Triggers » SQL Server ENABLE TRIGGER

SQL Server ENABLE TRIGGER

Summary: in this tutorial, you will learn how to use the SQL Server ENABLE TRIGGER statement to enable a trigger.

Introduction to SQL Server ENABLE TRIGGER statement #

The ENABLE TRIGGER statement allows you to enable a trigger so that the trigger can be fired whenever an event occurs.

The following illustrates the syntax of the ENABLE TRIGGER statement:

ENABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]
Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • First, specify the name of the trigger that you want to enable. Optionally, you can specify the name of the schema to which the trigger belongs.
  • Second, specify the table to which the trigger belongs if the trigger is a DML trigger. Use DATABASE if the trigger is a DDL database-scoped trigger or ALL SERVER if the trigger is DDL server-scoped trigger.

SQL Server ENABLE TRIGGER example #

We will use the sales.members table created in the DISABLE TRIGGER tutorial for the demonstration.

To enable the sales.sales.trg_members_insert trigger, you use the following statement:

ENABLE TRIGGER sales.trg_members_insert
ON sales.members;
Code language: SQL (Structured Query Language) (sql)

Once enabled, you can see the status of the trigger via the SQL Server Management Studio as shown in the following picture: