SQL trigger management features – ApexSQL Trigger

The main components of ApexSQL Trigger are generated SQL triggers that capture and store changed data by insert, update and delete SQL statements.

ApexSQL Trigger has a variety of interface features to allow viewing and managing of existing triggers including the ability to:

  • Filter or sort in various ways for reviewing by table name, status, type (Insert, Update or Delete statement auditing) or by schema
  • Disable or enable by selection
  • Delete by selection or all at once
  • Generate trigger DDL scripts

Main interface grid trigger management

The main grid can be used to review tables and audit triggers. All tables of a particular database, with or without ApexSQL Trigger generated triggers, are shown on the main grid.

Tables and their trigger types can be filtered and reviewed via the main grid’s Filter row.

Main interface grid

If unaudited tables need reviewing, set the Audited field of the Filter row to false

Main grid filter example

Trigger management module

Similarly, ApexSQL Trigger provides a module which offers a quick trigger overview and basic trigger management functions. To access it select the Home tab on the top ribbon, and click the Manage button from Triggers section:

Manage audit triggers button location

This option opens a dialog showing all triggers generated by ApexSQL Trigger for a particular database. Unaudited tables will not be shown

Manage audit triggers dialog

Triggers can be enabled, disabled or deleted using this dialog. When disabled, a trigger is still an object in SQL Server, but isn’t fired. This provides a quick and easy way to turn auditing On/Off without having to delete objects

The Script option generates a SQL script for all or just a set of selected triggers

The Manage triggers dialog provides an overview of the generated triggers, their type and status. Any grid column can be filtered via the Filter row

To find all triggers that audit INSERT statement on the Person schema set the Filter row like this:

Manage audit triggers filter example

Delete auditing triggers

All ApexSQL Trigger triggers can easily be deleted using the Delete the auditing architecture dialog. To open this dialog select the Advanced tab on the top ribbon, and click the Delete button:

Delete audititing button location

This button opens the Delete the auditing architecture dialog which provides options for deleting both the audit architecture (tables, views and procedures) and all audit triggers. Select the Delete the auditing triggers option to delete all generated triggers and confirm it with the Run button

Delete audititing dialog

This is easier than deleting all triggers using Manage triggers, since they do not have to be loaded for a preview and selected prior to deletion

The script to delete triggers manually

If none of the described UI options work for you, triggers can be deleted manually by executing the following SQL:

USE {DatabaseName};


	,tr.NAME AS trgName
FROM sys.schemas AS sh
INNER JOIN sys.tables AS tab ON sh.schema_id = tab.schema_id
INNER JOIN sys.triggers AS tr ON tab.object_id = tr.parent_id
WHERE tr.type = 'tr'

OPEN cur

FROM cur
INTO @shName

WHILE @@fetch_status = 0
	EXEC ('Drop TRIGGER ' + @shName + '.' + @trgName)

	FROM cur
	INTO @shName


DEALLOCATE cur The ‘ % _AUDIT_ %

The ‘%_AUDIT_%’ string narrows the selection down to ApexSQL Trigger triggers only

February 25, 2013