This video shows how to start using ApexSQL Trigger by creating a repository database to hold all the audited data as well as the auditing architecture. In addition to this, a connection to a sample SQL database is established in which a set of tables is selected for auditing, thus DML triggers are created. Sample changes are made in a database after DML triggers are created showing an auditing trail in a report.
Transcript
Hello and welcome to this Apex SQL Trigger general overview video. ApexSQL Trigger is a tool used to track data aka DML changes caused by operations such as an insert, update or delete. These operations are detected by DML triggers that ApexSQL Trigger creates on the selected tables.
In order to set up an auditing plan for a single SQL database we’ll click the New button in the projects group under the Home tab of the main application form. This initiates a dialogue to connect to a SQL Server instance and to pick a specific database that will be audited. In this case we’ll pick AdventureWorks 2014.
If this is the first time ApexSQL Trigger is connected to a SQL database, like it is in this case, the application requires a specific architecture of underlying SQL objects to be installed prior to setting up an auditing plan. An auditing architecture assumes a set of additional SQL database objects that includes tables, stored procedures and functions which are used to properly archive and process all the audited data.
There are two options for installing the auditing architecture. Either a current database will be used or any other SQL database from the connected SQL Server instance. At this point, even a blank database can be created to keep the auditing architecture just by specifying its name in the repository database field. This is a good option in case multiple databases from a single SQL Server instance should be audited.
Although we are going to show the auditing of a single database, we’ll specify a new database called ApexSQLTriggerRepository. Once a repository database is specified, the next step is to choose the auditing architecture components that will be installed. Besides the internal DDL and views components that are mandatory, at least one reporting stored procedures should be selected depending on the type of the auditing report that will be used later. Other items will create stored procedures used to delete the auditing architecture, reports and data if needed through the user interface, although this can be done manually. For the purpose of this video will utilize the following selection.
It is important to mention that any of the auditing components can be edited before installing. For instance, highlighting the delete the Apex SQL Trigger architecture item and clicking the Edit button opens the exact script that is used when the auditing architecture deletion is initiated later through the Apex SQL user interface. This allows reviewing and eventually editing of each auditing component before it is created.
We won’t make any changes for the purpose of this video, therefore the default auditing architecture will be used. If the auditing architecture is modified or if the specific selection of components should be used on multiple databases, the auditing architecture including eventual modifications can be saved and reused later at any point.
After all needed items are checked will install the architecture using the Install button. Once the installation process is finished we’ll close the dialog in order for the SQL database tables to load in the main application grid.
Tables are shown as grid rows while DML operations are shown as grid columns. By default, all DML operations including insert, update and delete operations are automatically checked for all tables. Tables themselves are not checked and it is up to the user to select specific tables that will be audited.
By default, all listed tables are grouped by schema and sort by name in the ascending order. For the purpose of this video we want to track DML changes for tables that are under human resources and person schemas.
In order to show tables that belong to the requested schemas only we can use the custom filtering option. As shown in the main grid tables from the requested schemas are the only ones being shown in the grid. This is just an example of how filtering can be utilized to narrow down a list of tables.
At this point we can also manage our audit plan using the All items or the Checked items context menu options.
On the right side of the main grid a set of tabs are shown. The main one is the Columns tab, which shows a list of columns for a SQL table selected in the main grid. Let’s highlight the Address table, for example. The Columns tab will show all the columns from the Address table. By default, no columns are checked for auditing and it is up to the user to make a selection. For the purpose of this video will include all columns for the selected tables to be audited.
In order to achieve that we can use the previously mentioned context menu command to check all the columns for all tables in the main grid.
Now, we can create DML triggers for the checked set of tables and for the checked columns, in this case all columns. To do so we’ll click the Create button in the Home tab under the triggers group. This indicates the process of generating a SQL script that will create DML triggers on the checked tables and for the checked columns. ApexSQL Trigger creates a separate trigger for each operation. That means if all operations are checked to be audited three DML triggers will be created.
Once this script is executed we should have three DML triggers created in each table that belong to Person or HumanResources schemas. Before executing the script, it can be reviewed and modified as needed. In addition to this the script can be saved for later usage.
To execute the script, we’ll click the button. Once we have created the DML triggers we have finished with setting up the auditing plan. That means any data changes made against the checked tables and columns from now on will fire DML triggers and the auditing trail will be saved in the appropriate tables previously created as part of the auditing architecture. If we go back to the main grid and select any of the tables that are included in the auditing process, we can see a list of DML triggers that is created in the trigger section.
Now that we have an auditing plan created, let’s make some data changes in one of the tables being audited.
We’ll execute the following insert script on the AddressType table. Now let’s review the auditing report to see how this change is being tracked by ApexSQL Trigger. In order to review any changes made after DML triggers are being created we’ll use the reporting feature in Apex SQL Trigger. In this case we’ll use the Standard report which can be initiated by clicking the Standard button in the Reports group.
When the Standard report is opened it does not show all the results immediately. This is because we’ll need to filter results that we want to review. Using the simple and advanced filtering options auditing results can be fine-tuned to show specific changes only.
Since we made a single change we’ll click the Apply button which actually applies all filtering conditions. We won’t set any of them, so all the changes are shown. These four records represent a change we have introduced by executing an insert statement. Although we have specified only the value for the name column which is the secondary office values for other columns are generated due to default constraints. No matter if the user explicitly specified some value or it is being generated by SQL server itself it will be captured by ApexSQL Trigger and shown later in the report.
It is important to mention that ApexSQL Trigger shows the exact value that existed before a change is made as well as the exact value that was introduced by a change. In case we have checked only the name column to be audited there will be no other records.
Let’s recreate a DML trigger for the address type table to include only the name column in the auditing process. This means that any changes in the auditing plan can be performed at any point after it is initially set. The only requirement is to recreate triggers after making any changes in the auditing plan. We will uncheck all of their tables and columns in order to make sure we will change the auditing for the AddressType table.
After that we’ll check the AddressType table and the Name column only. We still want all operations being audited. By clicking the Create button, in the triggers group, will recreate DML triggers for the AddressType table.
Let’s make another change in the AddressType table using the following script. Starting the report again and showing results gives only a single line. That means even if other columns are changed such changes will be ignored by ApexSQL Trigger and not shown in a report. In any case the exact before and after values are shown along with the information about who made the change, when and which application was used.
Thanks for watching. For more information please visit apexsql.com
