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.

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

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:

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

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:

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:

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

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};

DECLARE @trgName VARCHAR(500)
DECLARE @shName VARCHAR(500)

DECLARE cur CURSOR
FOR
SELECT sh.NAME AS shName
	,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'
	AND tr.NAME LIKE '%_AUDIT_%';

OPEN cur

FETCH NEXT
FROM cur
INTO @shName
	,@trgName

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

	FETCH NEXT
	FROM cur
	INTO @shName
		,@trgName
END

CLOSE cur

DEALLOCATE cur The ‘ % _AUDIT_ %

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