Writing SQL Audit Triggers for Compliance

By Tom Nonmacher

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.

Check out the latest articles from all our sites:

Privacy Policy for sqlsupport.org

Last updated: Feb 03, 2026

sqlsupport.org respects your privacy and is committed to protecting any personal information you may provide while using this website.

This Privacy Policy document outlines the types of information that are collected and recorded by sqlsupport.org and how we use it.

Information We Collect

  • Internet Protocol (IP) addresses
  • Browser type and version
  • Pages visited
  • Time and date of visits
  • Referring URLs
  • Device type

Cookies and Web Beacons

sqlsupport.org uses cookies to store information about visitors preferences and to optimize the users experience.

How We Use Your Information

  • Operate and maintain our website
  • Improve user experience
  • Analyze traffic patterns
  • Prevent fraudulent activity

Contact

Email: admin@sqlsupport.org




713B46
Please enter the code from the image above in the box below.