How to Create an Audit Table in SQL Server

Audit tables are an essential component of any database system. They allow you to track transactions and changes made to important tables, providing valuable information for auditing and compliance purposes. In this article, we will walk through the process of setting up an audit table for an example Inventory table in SQL Server.

Step 1: Create the Inventory Database

First, we need to create a database to hold our Inventory table, audit table, and auditing triggers. Here is the script to create the database:

USE MASTER
GO

IF NOT EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'InventoryDB')
    CREATE DATABASE InventoryDB
GO

USE InventoryDB
GO

Step 2: Create the Inventory Table

Next, we create the Inventory table that we want to audit. Here is the script to create the table:

USE InventoryDB
GO

IF EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'InventoryMasters')
    DROP TABLE InventoryMasters
GO

CREATE TABLE [dbo].[InventoryMasters] (
    [InventoryID] [int] IDENTITY(1,1) NOT NULL,
    [ItemNO] varchar(10),
    [InventoryDate] Datetime null,
    [InventorySeq] int,
    [InventoryType] char(1),
    [Qty] int
)

Step 3: Create the Audit Table

Now, we need to create an audit table to store our audit information. Here is the script to create the audit table:

CREATE TABLE [dbo].[Audit_InventoryMasters] (
    [Audit_InventoryID] [int] IDENTITY(1,1) NOT NULL,
    [InventoryID] [int],
    [ItemNO] varchar(10),
    [RemainingQty] int,
    [InQty] int,
    [OutQty] int,
    [ResultQty] int,
    [AuditDate] Datetime null,
    [AuditbyUser] int,
    [AuditType] char(1),
    CONSTRAINT [PK_Audit_InventoryMasters] PRIMARY KEY CLUSTERED (
        [Audit_InventoryID] ASC
    ) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY]

Step 4: Creating the Triggers

Now that we have created our tables, we can proceed to create triggers for auditing transactions on our Inventory table. We will create triggers for Insert, Update, and Delete operations.

Insert Trigger

The Insert trigger will capture the In/Out quantity of every transaction and insert it into the audit table. Here is the script to create the Insert trigger:

USE InventoryDB
GO

CREATE TRIGGER trInventoryInsert ON [dbo].[InventoryMasters] FOR INSERT AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Count int = 0;
    DECLARE @DateCount int = 0;
    DECLARE @InventoryID int = 0;
    DECLARE @InventoryDate datetime;
    DECLARE @InventorySeq int;
    DECLARE @InventoryType char(1);
    DECLARE @ItemNO varchar(10);
    DECLARE @Qty int;
    DECLARE @Result_Qty int = 0;
    DECLARE @FinalInventoryType char(1);
    DECLARE @Unit_Qty int = 0;
    DECLARE @New_QtyCheck int = 0;
    DECLARE @New_OUT_QtyCheck int = 0;

    SELECT @InventoryID = i.InventoryID FROM inserted i;
    SELECT @InventoryDate = i.InventoryDate FROM inserted i;
    SELECT @InventorySeq = i.InventorySeq FROM inserted i;
    SELECT @InventoryType = i.InventoryType FROM inserted i;
    SELECT @ItemNO = i.ItemNO FROM inserted i;
    SELECT @Qty = i.Qty FROM inserted i;

    SELECT @Count = COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO;

    IF (@Count > 0)
    BEGIN
        SELECT @DateCount = COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;

        IF (@DateCount > 0)
        BEGIN
            SELECT TOP 1 @New_QtyCheck = InQty FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
            SELECT TOP 1 @New_OUT_QtyCheck = OutQty FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;

            IF (@InventoryType = 'I')
            BEGIN
                UPDATE Audit_InventoryMasters SET InQty = @Qty + @New_QtyCheck WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;
            END
            ELSE
            BEGIN
                UPDATE Audit_InventoryMasters SET OutQty = @Qty + @New_OUT_QtyCheck WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;
            END
        END
        ELSE
        BEGIN
            IF NOT EXISTS (SELECT * FROM Audit_InventoryMasters WHERE AuditDate < @InventoryDate)
            BEGIN
                SET @Unit_Qty = 0;
            END
            ELSE
            BEGIN
                SELECT TOP 1 @Unit_Qty = ResultQty FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate < @InventoryDate;
            END

            IF (@InventoryType = 'I')
            BEGIN
                SET @Result_Qty = @Unit_Qty + @Qty;

                INSERT INTO Audit_InventoryMasters (InventoryID, ItemNO, RemainingQty, InQty, OutQty, ResultQty, AuditDate, AuditbyUser, AuditType)
                VALUES (@InventoryID, @ItemNO, @Unit_Qty, @Qty, 0, @Result_Qty, @InventoryDate, 101, 'I');

                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;
            END
            ELSE
            BEGIN
                SET @Result_Qty = @Unit_Qty - @Qty;

                INSERT INTO Audit_InventoryMasters (InventoryID, ItemNO, RemainingQty, InQty, OutQty, ResultQty, AuditDate, AuditbyUser, AuditType)
                VALUES (@InventoryID, @ItemNO, @Unit_Qty, 0, @Qty, @Result_Qty, @InventoryDate, 101, 'I');

                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;
            END
        END
    END
    ELSE
    BEGIN
        IF (@InventoryType = 'I')
        BEGIN
            INSERT INTO Audit_InventoryMasters (InventoryID, ItemNO, RemainingQty, InQty, OutQty, ResultQty, AuditDate, AuditbyUser, AuditType)
            VALUES (@InventoryID, @ItemNO, 0, @Qty, 0, @Qty, @InventoryDate, 101, 'I');
        END
        ELSE
        BEGIN
            INSERT INTO Audit_InventoryMasters (InventoryID, ItemNO, RemainingQty, InQty, OutQty, ResultQty, AuditDate, AuditbyUser, AuditType)
            VALUES (@InventoryID, @ItemNO, 0, 0, @Qty, -@Qty, @InventoryDate, 101, 'I');
        END
    END
END

Update Trigger

The Update trigger will capture any changes made to the Inventory table and update the audit table accordingly. Here is the script to create the Update trigger:

USE InventoryDB
GO

CREATE TRIGGER trInventoryUpdate ON [dbo].[InventoryMasters] FOR UPDATE AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Count int = 0;
    DECLARE @DateCount int = 0;
    DECLARE @InventoryID int = 0;
    DECLARE @InventoryDate datetime;
    DECLARE @InventorySeq int;
    DECLARE @InventoryType char(1);
    DECLARE @ItemNO varchar(10);
    DECLARE @Qty int;
    DECLARE @Result_Qty int = 0;
    DECLARE @FinalInventoryType char(1);
    DECLARE @Unit_Qty int = 0;
    DECLARE @New_QtyCheck int = 0;
    DECLARE @New_OUT_QtyCheck int = 0;
    DECLARE @result_IN_Qty int = 0;
    DECLARE @result_OUT_Qty int = 0;
    DECLARE @result_IN_Qty_CHK int = 0;
    DECLARE @result_IN_Qty_CHK_1 int = 0;
    DECLARE @result_OUT_Qty_CHK int = 0;
    DECLARE @result_OUT_Qty_CHK_1 int = 0;

    SELECT @InventoryID = d.InventoryID FROM deleted d;
    SELECT @InventoryDate = d.InventoryDate FROM deleted d;
    SELECT @InventorySeq = d.InventorySeq FROM deleted d;
    SELECT @InventoryType = d.InventoryType FROM deleted d;
    SELECT @ItemNO = d.ItemNO FROM deleted d;
    SELECT @Qty = d.Qty FROM deleted d;

    SELECT @Count = COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO;

    IF (@Count > 0)
    BEGIN
        SELECT @DateCount = COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;

        IF (@DateCount > 0)
        BEGIN
            SELECT TOP 1 @New_QtyCheck = InQty FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
            SELECT TOP 1 @New_OUT_QtyCheck = OutQty FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;

            SELECT @result_IN_Qty = SUM(Qty) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'I';
            SELECT @result_OUT_Qty = SUM(Qty) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'O';

            IF (@InventoryType = 'I')
            BEGIN
                SELECT @result_IN_Qty_CHK = SUM(InQty) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;

                UPDATE Audit_InventoryMasters SET InQty = @result_IN_Qty WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;

                SELECT @ROWINCOUNT = COUNT(*) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'I';

                IF (@ROWINCOUNT > 1)
                BEGIN
                    IF (@Qty = 0)
                    BEGIN
                        UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - (@result_IN_Qty_CHK - @result_IN_Qty) WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                    END
                    ELSE
                    BEGIN
                        IF (@result_IN_Qty_CHK >= @result_IN_Qty)
                        BEGIN
                            SET @result_IN_Qty_CHK_1 = @result_IN_Qty_CHK - @result_IN_Qty;
                            UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - @result_IN_Qty_CHK_1 WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                        END
                        ELSE
                        BEGIN
                            SET @result_IN_Qty_CHK_1 = @result_IN_Qty - @result_IN_Qty_CHK;
                            UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + @result_IN_Qty_CHK_1 WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                        END
                    END
                END
                ELSE
                BEGIN
                    IF (@Qty >= @New_QtyCheck)
                    BEGIN
                        UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + (@Qty - @New_QtyCheck) WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                    END
                    ELSE
                    BEGIN
                        UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - (@New_QtyCheck - @Qty) WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                    END
                END
            END
            ELSE
            BEGIN
                SELECT @result_OUT_Qty_CHK = SUM(OutQty) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;

                UPDATE Audit_InventoryMasters SET OutQty = @result_OUT_Qty WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;

                SELECT @ROWINCOUNT = COUNT(*) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'O';

                IF (@ROWINCOUNT > 1)
                BEGIN
                    IF (@Qty = 0)
                    BEGIN
                        UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + (@result_OUT_Qty_CHK - @result_OUT_Qty) WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                    END
                    ELSE
                    BEGIN
                        IF (@result_OUT_Qty_CHK >= @result_OUT_Qty)
                        BEGIN
                            SET @result_OUT_Qty_CHK_1 = @result_OUT_Qty_CHK - @result_OUT_Qty;
                            UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + @result_OUT_Qty_CHK_1 WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                        END
                        ELSE
                        BEGIN
                            SET @result_OUT_Qty_CHK_1 = @result_OUT_Qty - @result_OUT_Qty_CHK;
                            UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + @result_OUT_Qty_CHK_1 WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                        END
                    END
                END
                ELSE
                BEGIN
                    IF (@Qty >= @New_OUT_QtyCheck)
                    BEGIN
                        UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - (@Qty - @New_OUT_QtyCheck) WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                    END
                    ELSE
                    BEGIN
                        UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + (@New_OUT_QtyCheck - @Qty) WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                    END
                END
            END
        END
        ELSE
        BEGIN
            IF (@InventoryType = 'I')
            BEGIN
                INSERT INTO Audit_InventoryMasters (InventoryID, ItemNO, RemainingQty, InQty, OutQty, ResultQty, AuditDate, AuditbyUser, AuditType)
                VALUES (@InventoryID, @ItemNO, 0, @Qty, 0, @Qty, @InventoryDate, 101, 'I');
            END
            ELSE
            BEGIN
                INSERT INTO Audit_InventoryMasters (InventoryID, ItemNO, RemainingQty, InQty, OutQty, ResultQty, AuditDate, AuditbyUser, AuditType)
                VALUES (@InventoryID, @ItemNO, 0, 0, @Qty, -@Qty, @InventoryDate, 101, 'I');
            END
        END
    END
END

Delete Trigger

The Delete trigger will capture the deletion of records from the Inventory table and update the audit table accordingly. Here is the script to create the Delete trigger:

USE InventoryDB
GO

CREATE TRIGGER trInventoryDelete ON [dbo].[InventoryMasters] FOR DELETE AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Count int = 0;
    DECLARE @DateCount int = 0;
    DECLARE @InventoryID int = 0;
    DECLARE @ROWINCOUNT int = 0;
    DECLARE @InventoryDate datetime;
    DECLARE @InventorySeq int;
    DECLARE @InventoryType char(1);
    DECLARE @ItemNO varchar(10);
    DECLARE @Qty int;
    DECLARE @Result_Qty int = 0;
    DECLARE @FinalInventoryType char(1);
    DECLARE @Unit_Qty int = 0;
    DECLARE @New_QtyCheck int = 0;
    DECLARE @New_OUT_QtyCheck int = 0;
    DECLARE @result_IN_Qty int = 0;
    DECLARE @result_OUT_Qty int = 0;
    DECLARE @result_IN_Qty_CHK int = 0;
    DECLARE @result_IN_Qty_CHK_1 int = 0;
    DECLARE @result_OUT_Qty_CHK int = 0;
    DECLARE @result_OUT_Qty_CHK_1 int = 0;

    SELECT @InventoryID = d.InventoryID FROM deleted d;
    SELECT @InventoryDate = d.InventoryDate FROM deleted d;
    SELECT @InventorySeq = d.InventorySeq FROM deleted d;
    SELECT @InventoryType = d.InventoryType FROM deleted d;
    SELECT @ItemNO = d.ItemNO FROM deleted d;
    SELECT @Qty = d.Qty FROM deleted d;

    SELECT @Count = COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO;

    IF (@Count > 0)
    BEGIN
        SELECT @DateCount = COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;

        IF (@DateCount > 0)
        BEGIN
            IF (@InventoryType = 'I')
            BEGIN
                SELECT @ROWINCOUNT = COUNT(*) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'I' AND InventorySeq = @InventorySeq;

                IF (@ROWINCOUNT <= 0)
                BEGIN
                    SELECT @Qty = InQty - (SELECT ISNULL(SUM(Qty), 0) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'I') FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                END

                UPDATE Audit_InventoryMasters SET InQty = InQty - @Qty WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty - @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;
            END
            ELSE
            BEGIN
                SELECT @ROWINCOUNT = COUNT(*) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'O' AND InventorySeq = @InventorySeq;

                IF (@ROWINCOUNT <= 0)
                BEGIN
                    SELECT @Qty = OutQty - (SELECT ISNULL(SUM(Qty), 0) FROM InventoryMasters WHERE ItemNO = @ItemNO AND InventoryDate = @InventoryDate AND InventoryType = 'O') FROM Audit_InventoryMasters WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                END

                UPDATE Audit_InventoryMasters SET OutQty = OutQty - @Qty WHERE ItemNO = @ItemNO AND AuditDate = @InventoryDate;
                UPDATE Audit_InventoryMasters SET RemainingQty = RemainingQty + @Qty WHERE ItemNO = @ItemNO AND AuditDate > @InventoryDate;
                UPDATE Audit_InventoryMasters SET ResultQty = RemainingQty + InQty - OutQty WHERE ItemNO = @ItemNO AND AuditDate >= @InventoryDate;
            END
        END
    END
END

Conclusion

In this article, we have learned how to create an audit table in SQL Server to track transactions and changes made to an Inventory table. By implementing triggers for Insert, Update, and Delete operations, we can capture and store the necessary audit information in the audit table. This audit data can be used for auditing, compliance, and historical analysis purposes.

Remember to adapt the scripts provided to fit your specific table structure and requirements. With the knowledge gained from this article, you can now implement audit tables in your own SQL Server databases.

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on