在审计表中传递新/旧参数

Question

因此，据我所知，审计表是某种跟踪表(即捕获基表中的更改)

INSERT/UPDATE op如何处理旧值和新值?不知道怎么写

我需要写AddressLine1 FROM Person的旧值和新值。地址

这是我所有的:

USE AdventureWorks2012;
GO
    -- AL1 is AddressLine1
-- also what about primary key???
CREATE TABLE AuditTable
(
    AL1Old      nvarchar(60)    not null,
    AL1New      nvarchar(60)    not null,
);
GO
-- should I update AuditTable there? 
-- I don't know how to insert Old and New value by trigger...
CREATE TRIGGER triggy
ON Person.Address
AFTER INSERT, UPDATE 
AS
-- ???
GO

Answer 1

如果你想在触发器中插入旧数据和新数据，那么只能在UPDATE触发器中工作- INSERT触发器显然没有任何"旧"数据。

基本上，您需要从Deleted伪表中获取旧数据，从Inserted伪表中获取新数据。

还有:要注意，每个语句调用一个触发器一次，并且两个伪表都可以(也将会)包含多行-因此相应地编程您的触发器。

要处理Addressline1列—尝试这样做(是的，我还建议包含主键来标识该数据用于哪一行!)

-- AL1 is AddressLine1
-- also what about primary key???
CREATE TABLE AuditTable
(
    PrimaryKey INT NOT NULL,
    AL1Old      nvarchar(60)    not null,
    AL1New      nvarchar(60)    not null,
);
GO
-- I don't know how to insert Old and New value by trigger...
CREATE TRIGGER addressUpdateTrigger
ON Person.Address
AFTER UPDATE 
AS
    INSERT INTO dbo.AuditTable(PrimaryKey, AL1Old, AL1New)
       SELECT
          Inserted.AddressID,      -- primary key
          Deleted.AddressLine1,    -- old data
          Inserted.AddressLine1    -- new data
       FROM 
          Inserted
       INNER JOIN
          Deleted on Inserted.AddressID = Deleted.AddressID
GO