如何设置基于触发器的审核?

hc8w905p  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(341)

这个问题在这里已经有答案了

在审核表中记录sql server中的更改(4个答案)
10个月前关门了。
我有一张table Employee :

CREATE TABLE [dbo].[Employee]
(
    [EmployeeCode] [int] NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [Position] [varchar](30) NULL
)

我要记录更改 Employee_Audit 每次table有变化 Position (以及 Old Position , New Position 以及 Timestamp )在 Employee table。

CREATE TABLE [dbo].[Employee_Audit]
(
    [EmployeeCode] [int] NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [PositionOld] [varchar](30) NULL,
    [PositionNew] [varchar](30) NULL,
    [Date] [datetime] NULL
 )

我如何做到这一点?

iszxjhcz

iszxjhcz1#

除了trigger option@marc\s提到的,如果您不想只考虑位置,并且考虑所有列的更改审核,下面的选项提供给您做审核,无需任何特定的编程需求。你可以看看它是否适合你的需要。
您可以想到sqlserver时态表。读取msdn上的sql server时态表。它们提供了审核行更改的透明方式。
您还可以使用更改数据捕获选项来跟踪列的历史更改。更改msdn上的数据捕获

s4n0splo

s4n0splo2#

你基本上需要一个 UPDATE 触发器检查 Position 值已更改-如果已更改,请将详细信息记录到 Employee_Audit :

CREATE OR REPLACE trgEmployeeUpdate
ON dbo.Employee 
AFTER UPDATE 
AS
BEGIN
    -- based on the Inserted and Deleted pseudo tables, join the rows
    -- that were updated and look for changes in "Position"
    INSERT INTO dbo.Employee_Audit (EmployeeCode, FirstName, LastName, Email, 
                                    PositionOld, PositionNew, [Date])
        SELECT
            i.EmployeeCode, i.FirstName, i.LastName, i.Email,
            d.Position, i.Position, SYSDATETIME()
        FROM
            Inserted i
        INNER JOIN
            Deleted d ON i.EmployeeCode = d.EmployeeCode
        WHERE
            i.Position <> d.Position    -- Position has changed
END

相关问题