How to manage triggers in ApexSQL Trigger (4214923)

Applies to ApexSQL Trigger

Summary This article explains how to add, modify, and remove triggers in ApexSQL Trigger

Description When creating DML triggers, all operation types: INSERT, UPDATE, and DELETE are automatically selected in the ApexSQL Trigger grid:

If you don’t want to audit all three operation types, deselect its check box before you create the trigger

To modify the existing triggers for a specific table, use the Manage option in the menu:

It provides a list of all created triggers on the database and an easy way to enable, disable, script and delete them:

Note that a DML trigger will never have the Insert, Update, and Delete column checked at the same time, as a trigger is created only for one operation type. The triggers listed can be grouped and sorted by multiple columns.

When to disable a trigger The most common situations when a trigger should be disabled are when maintenance or administrative tasks and planned mass inserts, updates, deletes, creates, alter or drops are performed. Disabled triggers are not fired until enabled again. Disabling triggers in such situation prevents a large number of inserts into the audit tables and thus reduces the noise in captured events and performance degradation

Depending on a situation, you can disable all triggers, or just some.

Quick tip: To temporary stop triggers are fired when the audited event occurs, disable the trigger. This option will not delete the trigger and it will remain in the database. The effect is the same as when a DISABLE TRIGGER T-SQL statement, or the Disable option in the SQL Server Management Studio trigger context menu is used.

When to enable a trigger By default, a trigger is enabled when it’s created. The Enable option has effect only on the previously disabled triggers. If you want to continue to audit events after the mass changes or maintenance tasks are completed, enable the triggers.

When to script a trigger The Script option provides the same script that was used to create the DML trigger using the Create option in the Triggers group on the menu or the same script that was used to create the DDL trigger by installing the DDL reporting stored procedures from the auditing architecture file. You can create a script for multiple DML triggers at the same time

The script creates trigger, as well as watches and lookups if they exist, and populates the auditing AUDIT_LOG_DATA, AUDIT_LOG_DDL and AUDIT_LOG_TRANSACTIONS tables with the audited changes.

Both enabled and disabled triggers are scripted. When a disabled trigger is scripted, the script will contain the following:

DISABLE TRIGGER <trigger name>
	ON <table name>

You can use these trigger scripts to create triggers on table and database copies, or modify the trigger scripts first and create new or re-create existing triggers on the same database.

Quick tip: Manage triggers manages only existing triggers. To create additional triggers (e.g. a trigger for DELETE) on the table where some of the triggers already exist (e.g. for INSERT and UPDATE), use the Create option in the ApexSQL Trigger menu.

When to delete a trigger If you want to permanently remove a trigger form the database, delete it. If you’re not certain that you will not need the trigger again, you can disable it first, and delete it later.

The Manage triggers grid context menu provides the Script to file option. It saves the trigger script as a .sql file, instead of opening the trigger script in the ApexSQL Trigger Script editor first.