Free SQL Help for Devs, DBAs, and the Curious

In today's data-driven business world, it is more crucial than ever to ensure that your data is secure and compliance is maintained. SQL Server, as one of the most popular database platforms, offers various features to help you achieve this. One such feature is SQL Audit Triggers. These triggers are used to track changes in data and can be used in combination with a security policy to ensure compliance with various regulations and standards. In this blog, we will delve into writing SQL Audit Triggers for compliance using SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL.

Audit triggers in SQL Server 2016 and SQL Server 2017 can be implemented using DDL triggers. DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements.

CREATE TRIGGER safety
ON DATABASE 
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS 
    PRINT 'A table create, alter, or drop event has occurred.';
GO

MySQL 5.7 also provides support for triggers that can be utilized for audit purposes. Unlike SQL Server, MySQL triggers are associated with a particular table and a particular event – INSERT, UPDATE, or DELETE.

CREATE TRIGGER audit_trigger 
AFTER UPDATE ON employees 
FOR EACH ROW 
INSERT INTO audit_table VALUES (NEW.id, NOW(), 'UPDATE');

DB2 11.1 can also be configured to use audit triggers. In DB2, triggers can be set to fire before or after an INSERT, UPDATE, DELETE, or SELECT statement. The following example shows how to create a trigger that records changes to a table:

CREATE TRIGGER audit_trigger
AFTER UPDATE ON employees
REFERENCING NEW AS n OLD AS o
FOR EACH ROW MODE DB2SQL
INSERT INTO audit_table VALUES (n.id, CURRENT TIMESTAMP, 'UPDATE');

For those working with Azure SQL, the process is similar to SQL Server. However, Azure SQL also provides the option of using Azure SQL Auditing, a feature that tracks database events and writes them to an audit log in your Azure storage account.

CREATE TRIGGER audit_trigger
ON employees
AFTER UPDATE
AS
INSERT INTO audit_table SELECT getdate(), 'UPDATE', user, @@SPID;

Implementing SQL Audit Triggers is a crucial step towards ensuring data security and compliance. Whether you're using SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, or Azure SQL, these examples should provide a solid foundation for you to start writing your audit triggers. Be sure to tailor these triggers to fit the specific needs and requirements of your organization. Remember, the goal is to maintain data integrity, security, and compliance.