Как создать таблицу аудита в SQL Server

Таблицы аудита являются неотъемлемой частью любой базы данных. Они позволяют отслеживать транзакции и изменения, внесенные важными таблицами, предоставляя ценную информацию для целей аудита и соответствия требованиям. В этой статье мы рассмотрим процесс создания таблицы аудита для примера таблицы Inventory в SQL Server.

Шаг 1: Создание базы данных Inventory

Сначала нам нужно создать базу данных, в которой будут храниться наша таблица Inventory, таблица аудита и триггеры аудита. Вот скрипт для создания базы данных:

USE MASTER
GO

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

USE InventoryDB
GO

Шаг 2: Создание таблицы Inventory

Затем мы создаем таблицу Inventory, которую хотим аудитировать. Вот скрипт для создания таблицы:

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
)

Шаг 3: Создание таблицы аудита

Теперь нам нужно создать таблицу аудита для хранения информации об аудите. Вот скрипт для создания таблицы аудита:

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]

Шаг 4: Создание триггеров

Теперь, когда мы создали наши таблицы, мы можем приступить к созданию триггеров для аудита транзакций в нашей таблице Inventory. Мы создадим триггеры для операций Insert, Update и Delete.

Триггер Insert

Триггер Insert будет захватывать входящее/исходящее количество каждой транзакции и вставлять его в таблицу аудита. Вот скрипт для создания триггера Insert:

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

Триггер Update будет захватывать любые изменения, внесенные в таблицу Inventory, и соответствующим образом обновлять таблицу аудита. Вот скрипт для создания триггера Update:

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

Триггер Delete будет захватывать удаление записей из таблицы Inventory и соответствующим образом обновлять таблицу аудита. Вот скрипт для создания триггера Delete:

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

Заключение

В этой статье мы узнали, как создать таблицу аудита в SQL Server для отслеживания транзакций и изменений, внесенных в таблицу Inventory. Реализуя триггеры для операций Insert, Update и Delete, мы можем захватывать и сохранять необходимую информацию об аудите в таблице аудита. Эти данные аудита могут использоваться для целей аудита, соответствия требованиям и исторического анализа.

Не забудьте адаптировать предоставленные скрипты под свою конкретную структуру таблицы и требования. С полученными знаниями из этой статьи вы теперь можете реализовать таблицы аудита в своих собственных базах данных SQL Server.

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