Auditing & Implementing Data Change Capture with Triggers in SQL Server

Database Auditing is a process of tracking and logging all events and capturing data changes in database objects, it not only helps businesses but is also a compliance requirement. In most cases when we want to implement capturing of data changes in database objects, we create a log table for that object and track all changes made by DML queries on that table. We can also implement database object changes made by DDL statements. In this post, we will be implementing and creating a log table for auditing and logging DML event data changes with help of triggers. To understand Trigger and its working with examples please refer to the previous post mentioned below. As said earlier we can create a trigger on tables containing critical data to audit and log the data before and after the modification. We can also use a trigger to disable the particular DML operation and can execute something else instead of performing that change. Let's illustrate auditing and capturing data changes with triggers. The following T-SQL codes create a simple sales table seeded with its data and a sales_log table with the required columns. The sales_log table will have all columns of the sales table and a few extra columns for logging the old value and new values of the sales table.

DROP TABLE IF EXISTS [dbo].sales;
CREATE TABLE [dbo].sales(
    sales_id int primary key,
    customer_id int not null,
    sales_person_id int not null,
    order_date date not null,
    total_amount_including_taxes decimal(10,2) not null default(0),
    tax_amount decimal(8,2) not null default(0),
    delivery_date date not null,
    extra_instruction varchar(500),
    updated_by int not null,
    updated_timestamp datetime2(0) not null default current_timestamp
);

INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (1, 4, 2, '2021-09-27', 1704.69, 49.81, '2021-09-29', 'in tempor turpis nec euismod scelerisque quam turpis adipiscing lorem vitae mattis nibh ligula nec sem duis aliquam convallis nunc', 2, '2021-09-27');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (2, 3, 3, '2021-10-25', 3314.02, 99.97, '2021-10-27', 'consectetuer eget rutrum at lorem integer tincidunt ante vel ipsum praesent blandit lacinia erat vestibulum sed magna', 3, '2021-10-25');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (3, 5, 2, '2021-09-25', 9147.42, 54.77, '2021-09-30', 'nisl ut volutpat sapien arcu sed augue aliquam erat volutpat in congue etiam justo etiam pretium iaculis justo', 2, '2021-09-25');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (4, 9, 2, '2022-01-27', 5173.06, 26.01, '2022-01-30', 'ipsum dolor sit amet consectetuer adipiscing elit proin risus praesent', 2, '2022-01-27');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) values (5, 1, 2, '2022-04-27', 8838.68, 33.07, '2022-04-30', 'ac tellus semper interdum mauris ullamcorper purus sit amet nulla quisque arcu libero rutrum ac lobortis', 2, '2022-04-27');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (6, 8, 2, '2021-05-02', 5959.05, 61.41, '2021-05-27', 'ut ultrices vel augue vestibulum ante ipsum primis in faucibus orci luctus et ultrices', 2, '2021-05-02');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (7, 2, 3, '2022-04-15', 1521.54, 78.27, '2021-04-21', 'vel lectus in quam fringilla rhoncus mauris enim leo rhoncus', 3,  '2022-04-15');
insert into sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (8, 9, 1, '2021-08-25', 6301.25, 66.37, '2021-08-30', 'ut ultrices vel augue vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia curae', 1, '2021-08-25');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (9, 5, 1, '2021-09-29', 6894.79, 15.74, '2021-10-29', 'pede ullamcorper augue a suscipit nulla elit ac nulla sed vel', 1, '2021-09-29');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (10, 5, 4, '2021-12-08', 8310.35, 35.54, '2021-12-31', 'mi integer ac neque duis bibendum morbi non quam nec', 4,  '2021-12-08');
 INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (11, 8, 2, '2021-10-13', 5815.76, 95.91, '2021-10-20', 'non mi integer ac neque duis bibendum morbi non quam nec dui luctus rutrum', 2, '2021-10-13');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (12, 3, 3, '2022-04-24', 1860.23, 65.36, '2022-04-24', 'odio porttitor id consequat in consequat ut nulla sed accumsan felis ut at dolor quis odio consequat varius integer', 3, '2022-04-24');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (13, 2, 1, '2022-01-22', 8716.47, 31.77, '2022-01-28', 'nec condimentum neque sapien placerat ante nulla justo aliquam quis turpis eget', 1, '2022-01-22');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (14, 6, 2, '2022-03-26', 9954.56, 29.54, '2022-03-27', 'in purus eu magna vulputate luctus cum sociis natoque penatibus et magnis dis parturient montes nascetur ridiculus', 2,'2022-03-26');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (15, 8, 1, '2021-07-31', 4671.74, 43.64, '2021-08-16', 'nisl nunc rhoncus dui vel sem sed sagittis nam congue risus semper porta', 1, '2021-07-31');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (16, 1, 2, '2022-02-25', 3511.53, 74.65, '2022-02-25', 'pede ac diam cras pellentesque volutpat dui maecenas tristique est et tempus semper est quam pharetra magna ac consequat', 2, '2022-02-25');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (17, 8, 1, '2021-10-01', 8507.02, 59.93, '2021-10-14', 'amet erat nulla tempus vivamus in felis eu sapien cursus vestibulum proin eu mi nulla ac enim in tempor', 1, '2021-10-01');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (18, 6, 4, '2022-01-08', 8008.41, 69.92, '2022-01-28', 'venenatis lacinia aenean sit amet justo morbi ut odio cras mi pede malesuada in imperdiet et commodo vulputate', 4, '2022-01-08');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (19, 1, 2, '2022-02-11', 1291.84, 19.51, '2022-02-15', 'eget elit sodales scelerisque mauris sit amet eros suspendisse accumsan tortor quis turpis sed ante vivamus', 2, '2022-02-11');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (20, 8, 4, '2022-03-25', 2948.43, 22.37, '2022-03-27', 'curae nulla dapibus dolor vel est donec odio justo sollicitudin', 4, '2022-03-25');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (21, 5, 4, '2021-06-12', 5466.84, 35.58, '2021-06-30', 'in faucibus orci luctus et ultrices posuere cubilia curae mauris viverra', 4, '2021-06-12');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (22, 9, 3, '2021-11-30', 8762.52, 22.53, '2021-12-30', 'ultrices vel augue vestibulum ante ipsum primis in faucibus orci luctus et', 3, '2021-06-12');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (23, 5, 1, '2021-05-18', 7894.89, 51.04, '2021-05-18', 'id pretium iaculis diam erat fermentum justo nec condimentum neque', 1, '2021-05-18');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (24, 4, 3, '2022-01-20', 9051.32, 22.23, '2022-01-20', 'nibh in hac habitasse platea dictumst aliquam augue quam sollicitudin', 3, '2022-01-20');
INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) values (25, 7, 3, '2022-03-20', 2537.84, 36.42, '2022-03-30', 'velit nec nisi vulputate nonummy maecenas tincidunt lacus at velit', 3, '2022-03-20');

DROP TABLE IF EXISTS [dbo].sales_log;
CREATE TABLE [dbo].sales_log(
    log_id int identity(1,1),
    sales_id int not null,
    customer_id_old int,
    customer_id_new int,
    sales_person_id_old int,
    sales_person_id_new int,
    order_date_old date,
    order_date_new date,
    total_amount_including_taxes_old decimal(10,2),
    total_amount_including_taxes_new decimal(10,2),
    tax_amount_old decimal(8,2),
    tax_amount_new decimal(5,2),
    delivery_date_old date,
    delivery_date_new date,
    extra_instruction_old varchar(500),
    extra_instruction_new varchar(500),
    updated_by_old int,
    updated_by_new int,
    updated_timestamp_old datetime2(0),
    updated_timestamp_new datetime2(0),
    action_done varchar(6) not null,
    action_time datetime2(0) not null,
    username varchar(128) null
);

The sales_log table inserts both old and new values of the columns, for every write operation in the sales table. It helps in tracking data changes and the columns action_done, action_time, and username in the sales_log table with DML operation which caused the change, timestamp, and database user. Normally we create a different user in the database for the application, so it's necessary to know the database user. Now let's create a trigger that tracks data changes in the sales table.

CREATE TRIGGER TR_audit_sales_table
    ON [dbo].sales
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [dbo].sales_log 
        (sales_id,
        customer_id_old,
        customer_id_new,
        sales_person_id_old,
        sales_person_id_new,
        order_date_old,
        order_date_new,
        total_amount_including_taxes_old,
        total_amount_including_taxes_new,
        tax_amount_old,
        tax_amount_new,
        delivery_date_old,
        delivery_date_new,
        extra_instruction_old,
        extra_instruction_new,
        updated_by_old,
        updated_by_new,
        updated_timestamp_old,
        updated_timestamp_new,
        action_done,
        action_time,
        username
        )
    SELECT
        ISNULL(i.sales_id, d.sales_id) AS sales_id,
        d.customer_id,
        i.customer_id,
        d.sales_person_id,
        i.sales_person_id,
        d.order_date,
        i.order_date,
        d.total_amount_including_taxes,
        i.total_amount_including_taxes,
        d.tax_amount,
        i.tax_amount,
        d.delivery_date,
        i.delivery_date,
        d.extra_instruction,
        i.extra_instruction,
        d.updated_by,
        i.updated_by,
        d.updated_timestamp,
        i.updated_timestamp,
        CASE
            WHEN i.sales_id IS NULL THEN 'DELETE'
            WHEN d.sales_id IS NULL THEN 'INSERT'
            ELSE 'UPDATE'
        END AS action_done,
        SYSUTCDATETIME(),
        SUSER_SNAME()
    FROM inserted i
    FULL JOIN deleted d
    ON i.sales_id = d.sales_id;
END

The above trigger code inserts new rows in the sales_log table for every write operation on the sales table and it can be further modified as per the requirements.

With the trigger created and executed, now we will test the working with some write operations in the sales table. Let us insert a new sale record in the sales table and see the information captured in the sales_log table.

INSERT INTO sales (sales_id, customer_id, sales_person_id, order_date, total_amount_including_taxes, tax_amount, delivery_date, extra_instruction, updated_by, updated_timestamp) VALUES (26, 7, 3, '2022-03-22', 25337.84, 361.42, '2022-05-01', 'velit nec nisi vulputate nonummy maecenas tincidunt lacus at velit', 3, '2022-03-20');

With a new sale record added having sales_id as 26, we can see the captured data in the sales_log table with the action type and database username which made those changes. A few of the columns in the sales_log table will be NULL since old data does not exist in the insert operation. Now let's update the same sale record updating the sales_person_id, total_amount_including_taxes and tax_amount.

UPDATE sales SET
    sales_person_id = 2,
    total_amount_including_taxes = 30000.00,
    tax_amount = 300.00
WHERE sales_id = 26;

When we check the log of the update operation in the sales_log table we can see that the trigger has captured and inserted both old data values and new data values for all columns and also see that the action_done is set to 'UPDATE'; Now let's delete a sale record from the sales table and see its insert in the sales_log table.

DELETE FROM sales WHERE sales_id = 26;

Now when we inspect the sales_log table for the above delete operation, we only see old values for the columns and NULL for the corresponding columns holding new values. This is how we can use the trigger for auditing and implementing data changes capture in SQL Server.

Related Post: Understanding Triggers in SQL Server