Applies to
ApexSQL Trigger
Summary
This article explains how to configure and use DDL/schema auditing in ApexSQL Trigger
Description
What is DDL/schema change auditing and how does it work?
DDL/schema change auditing is implemented using a DDL trigger
A DDL trigger is a trigger that fires in response to Data Definition Language (DDL) statements
A DDL trigger, like a DML trigger, executes SQL in response to an event. However, unlike DML triggers, they do not fire in response to data UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP
What are the differences between DML and DDL triggers?
DDL triggers fire only after the DDL statement has been executed. This is different from DML triggers that fires before the triggering statement
DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and enforce data integrity when data is modified in tables or views
DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements and stored procedures that perform DDL-like operations. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server
DML triggers and DDL triggers are created, modified, and dropped by using similar Transact-SQL syntax, and share other similar behavior
How to enable DDL/schema auditing?
In order to use the DDL auditing feature, two stored procedures from the architecture file must be created: Standard reporting stored procedures and DDL reporting stored procedures. To create/install them, choose Manage button from the Architecture section under the Advanced tab:
In the Manage architecture window, click install.
In the case that a valid architecture is already installed on the selected database install only the DDL reporting stored procedures architecture script. To do that, clear out the checkboxes next to the other architecture scripts, press the Install button and confirm to run the selected installation script:
After the architecture script is executed, a confirmation status appears in the Script execution results window:
Once the script operation is completed, ApexSQL Trigger is ready to audit DDL changes in the selected database.
Information that DDL auditing is enabled will be displayed in the application’s main status bar
Can I turn DDL/schema change auditing on/off at the database level?
Yes. To do so, click on Manage button under Triggers group in the Home tab
This opens Manage triggers window. From here you can manipulate with all DML/DDL triggers installed in the database.
To turn DDL/schema change auditing off, check the DDL trigger and click Disable button
This will disable DDL trigger in the database therefore no new DDL/schema changes will be collected afterwards.
After closing Manage triggers dialog, information in the application’s status bar will be updated to display the current state of DDL auditing
DDL auditing can be turned on again by selecting the DDL trigger and clicking Enable button in the Manage triggers window.
What new objects are installed in the database and where are they installed?
After a successful installation of the DDL auditing stored procedures scripts in the previous step, three new objects are created in database:
- tr_ddl_AUDIT – a DDL trigger used for catching DDL changes in the database
- AUDIT_LOG_DDL – a table to store DDL changes captured by the DDL trigger
- AUDIT_prc_DDLReport – a stored procedure used by the application to generate DDL reports based on collected data in the AUDIT_LOG_DDL table
Which version of the application supports DDL auditing/reporting?
The DDL auditing/reporting feature is introduced in ApexSQL Trigger 2015
What is the difference between DDL report and Schema changes features?
Schema changes feature tracks DDL changes only on the tables in audited database. This means that Schema changes feature can capture CREATE, ALTER and DROP table operations
DDL reporting feature provides more advanced way to track DDL changes on the database comparing to Schema changes. That means that DDL reporting captures all DDL_DATABASE_LEVEL_EVENTS for all database objects (views, indexes, constrains, stored procedures, etc.) not only table changes
DDL reporting allows creating and exporting customized reports based on collected changes. Schema changes doesn’t allows creating reports
Can I use DML auditing without DDL?
Yes. DML and DDL auditing are completely independent
Do I need to reinstall the architecture to use DDL reporting if I already have auditing plan set up from previous version of ApexSQL Trigger?
No you don’t. After upgrading to ApexSQL Trigger 2015, DDL auditing will be off by default. You can continue to use you’re already set up architecture file, and the application will continue to track DML changes based on the already installed architecture. If you plan to use DDL auditing, you will need to install DDL auditing stored procedures scripts from the newest architecture file. This will not affect previously set DML auditing
Can I run DDL reports unattended?
Yes, DDL report generation can be easily scheduled using the CLI. Following example will generate DDL report in PDF output file:
For the complete list of available CLI switches, please refer to this article
