2020年6月16日 星期二

[研究] SQL Server 2019 Audit 資料庫稽核 - 用 Trigger 觸發程序記錄歷史資料

[研究] SQL Server 2019 Audit 資料庫稽核 - 用 Trigger 觸發程序記錄歷史資料

2020-06-16

參考
CREATE TRIGGER (Transact-SQL)
https://docs.microsoft.com/zH-tw/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

架設有個資料表 Table1，內容結構如下


USE [TestDB]

GO

/****** Object:  Table [dbo].[Table1]    Script Date: 2020/6/16 上午 10:24:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1](
 [BookId] [int] IDENTITY(1,1) NOT NULL,
 [BookName] [nvarchar](50) NOT NULL,
 [BookDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
 [BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO   
 











修改前者，建立 Trigger 用的資料表 Table1Log






USE [TestDB]
GO

/****** Object:  Table [dbo].[Table1]    Script Date: 2020/6/16 上午 10:24:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1Log](
        [AuditLogId] [int] IDENTITY(1,1) NOT NULL,
 [AuditLogCmd] [nvarchar](50) NOT NULL,
 [AuditLogDate] [datetime] Default (GetDate()),
 [BookId] [int] NOT NULL,
 [BookName] [nvarchar](50) NOT NULL,
 [BookDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Table1Log] PRIMARY KEY CLUSTERED 
(
 [AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO










注意 BookID 那行的 IDENTITY(1,1) 必須拿掉，否則會出現錯誤








針對資料表 'Table1Log' 指定了多個識別欄位。每個資料表只允許有一個識別欄位。








建立 Trigger 時要執行的工作 (存放資料表可以相同，也可以不同)






-- Insert Trigger
CREATE TRIGGER [dbo].[TRG_Table1_INSERT] ON [dbo].[Table1]
AFTER INSERT
AS
    SET NOCOUNT ON;
 INSERT INTO [dbo].[Table1Log]
           ([AuditLogCmd]
           ,[AuditLogDate]
           ,[BookId]
           ,[BookName]
           ,[BookDate])
 SELECT  'INSERT'
           ,GetDate()
           ,[BookId]
           ,[BookName]
           ,[BookDate]
    FROM inserted;

GO

 
-- Delete Trigger
CREATE TRIGGER [dbo].[TRG_Table1_DELETE] ON [dbo].[Table1]
AFTER DELETE
AS
    SET NOCOUNT ON;
 INSERT INTO [dbo].[Table1Log]
           ([AuditLogCmd]
           ,[AuditLogDate]
           ,[BookId]
           ,[BookName]
           ,[BookDate])
 SELECT  'INSERT'
           ,GetDate()
           ,[BookId]
           ,[BookName]
           ,[BookDate]
    FROM inserted;

GO


 
--Update Trigger
CREATE TRIGGER [dbo].[TRG_Table1_UPDATE] ON [dbo].[Table1]
AFTER UPDATE
AS
    SET NOCOUNT ON;
    INSERT INTO [Table1Log]  ([AuditLogCmd]
           ,[AuditLogDate]
           ,[BookId]
           ,[BookName]
           ,[BookDate])

SELECT 'UPDATE-Ins'
           ,GetDate()
           ,[BookId]
           ,[BookName]


,[BookDate] FROM inserted 
        UNION ALL
        SELECT 'UPDATE-Del'
           ,GetDate()
           ,[BookId]
           ,[BookName]




,[BookDate] FROM deleted 
GO















以後對 Table1 做 INSERT, DELETE, UPDATE 時，Table1Log 就會有資料產生。


(完)

相關

[研究] SQL Server 2019 Audit 資料庫稽核 - 登入成功或失敗紀錄檢視

[研究] SQL Server 2019 Audit 資料庫稽核 - 用 Trigger 觸發程序記錄歷史資料











張貼者：



於






















標籤：


















沒有留言:















張貼留言


























        

      









訂閱：
張貼留言 (Atom)