An introduction to ApexSQL Trigger

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 ApexSQL 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 this video, we’ll show how to start using ApexSQL Trigger on a sample SQL database.

To start auditing with ApexSQL Trigger means to set up an auditing plan, specifically to pick “sensitive” tables, to be audited, and operations that will be audited as well as to review changes using one of the available reports. For the purpose of this video, we’ll be using Adventure Works database. Let’s see how to set up an auditing plan first.

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 dialog to connect to a SQL Server instance and to pick a specific database that will be audited. In this case, we’ll pick Adventure Works 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 the Views components that are mandatory, at least one reporting stored procedure should be selected, depending on a 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, we’ll 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 ApexSQL Trigger architecture item and clicking the Edit button opens the exact script that is used when the auditing architecture deletion is initiated later through ApexSQL 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, we’ll install the architecture using the Install button:

Once the installation process is finished, we’ll close the dialog in order for 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. It is important to mention that columns can be sorted and each one has its own built-in filter, so the user can easily narrow down the list of tables to the specific set in order to set up an auditing plan faster.

For the purpose of this video, we want to track DML changes for tables that are under HumanResources and Person schemas. In order to bring up these, we can sort the list of tables in descending or ascending order by schema which will group all objects of the same schema:

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 one being shown in the grid, so we can simply select them all. 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. In this way the specific operation can be excluded from the auditing process or certain table columns can be included or excluded from the auditing plan

On the right side of the main grid, a set of tabs is 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, we’ll include all columns for all the selected tables to be audited. In order to achieve that we can use previously mentioned context menu command to check all the columns for all tables in the main grid:

The minimum requirement to create a DML trigger is to have at least one table selected, at least one column from the selected table and at least one operation. Otherwise, the application will not create a DML trigger for you. Now that we have columns selected, we can create DML triggers for the selected 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 initiates the process of generating a SQL script that will create DML triggers on the selected tables and for the selected columns. ApexSQL Trigger creates a separate trigger for each operation. That means if all operations are selected to be audited, three DML triggers will be created. In our case, we have all tables from Person and HumanResources schemas selected including the columns and all operations:

Once this script is executed, we should have three DML triggers created in each table that belongs 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 selected tables and columns, from now on, will fire DML triggers and the auditing trail will be saved in the appropriate tables previously created as the 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 Triggers 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:

USE [AdventureWorks2014]
GO

INSERT INTO [Person].[AddressType]
           ([Name])
     VALUES
           ('Secondary Office')
GO

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 reporting feature in ApexSQL 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. In a situation where there are not so many changes, it is not relevant to filter any changes but to simply show them all. However, a real-life scenario would assume that a SQL database with many tables and columns is being audited for a long period of time. Showing all the results, in this case, would be useless and resource consuming. 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 4 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 re-create a DML trigger for the AddressType 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 re-create triggers after making any changes in the auditing plan.

We’ll unselect all other 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, we’ll re-create DML triggers for the AddressType table:

Let’s make another change in the AddressType table using the following script:

USE [AdventureWorks2014]
GO

UPDATE [Person].[AddressType]
      SET [Name] = 'Secondary Office_changed'
 WHERE Name = 'Secondary Office'
GO

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 who made a change, when and which application is used.

To summarize, we have shown how to install the auditing architecture, which is a pre-requisite for setting up the auditing plan. When the architecture is in place, tables, columns and operations can be selected for auditing. Once an auditing plan has been created and set in motion, all changes can be reviewed using the Reporting feature.

Thanks for watching. For more information please visit apexsql.com