Triggers Collection

SQL-DMO

Methods

Add Method	ItemByID Method
Item Method	Remove Method (Collections)

Remarks

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

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 the Trigger Object section.

To remove a trigger

1. Get the Trigger object referencing the targeted trigger from the Triggers collection 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")

Use the Remove method of the Trigger object to remove the targeted trigger.
Note  Removing a trigger using the Trigger object completely removes its definition from an instance of SQL Server. SQL Server triggers can be disabled but remain defined; that is, an instance of SQL Server maintains the trigger text, but the trigger does not fire on data modification. Trigger execution can be enabled or disabled using SQL-DMO 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 recommended 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 or view on which the trigger is defined, or a member of a role with equal privilege.