Triggers Collection (SQL-DMO)

The Triggers collection contains Trigger objects referencing the triggers defined on a Microsoft® SQL Server™ table.

Remarks

SQL Server implements triggers as a special type of stored procedure, automatically invoked based on the trigger definition and modification to table data.

With the Triggers collection, you can:

• Create a SQL Server trigger.
• Remove a SQL Server trigger.

For more information about creating a SQL Server trigger by using the Trigger object and Triggers collection, see Trigger Object.

To remove a trigger

1. Get the Trigger object referencing the targeted trigger from the Triggers collection by using the Item or ItemByID method. When extracting a Trigger object using the name of the referenced trigger, use the owner name to qualify the trigger name, as in:

   Set oTrigger = oTable.Triggers("[trigEmployees_Insert]", "dbo")

2. Use the Remove method of the Trigger object to remove the targeted trigger.

Note Removing a trigger by using the Trigger object completely removes its definition from the SQL Server installation. SQL Server triggers can be disabled but remain defined; that is, the SQL Server installation maintains the trigger text, but the trigger does not fire on data modification. Trigger execution can be enabled or disabled using SQL-DMO by using the Enabled property of the referencing Trigger object. For more information, see Enabled Property.

When using the Item or Remove method, the Triggers collection supports member identification using either name or ordinal reference syntax. For example:

Set oTrigger = oTable.Triggers("[trigEmployees_Delete]")

Or:

Set oTrigger = oTable.Triggers(1)

Additionally, when using name-based item selection, the Item method allows owner name qualification of the targeted SQL Server trigger as shown earlier. When using the Remove method, the Triggers collection does not support qualification of targeted object by owner name. It is suggested that you use the Item method to extract the target, then use the Remove method of the Trigger object to drop a trigger.

Note Creating or removing SQL Server triggers by using the Triggers collection requires appropriate privilege. The SQL Server login used for SQLServer object connection must be the owner of the table on which the trigger is defined, or a member of a role with equal privilege.

Methods

Properties

Count Property