在审计表中记录 SQL 服务器中的记录更改
表格 :
CREATE TABLE GUESTS (
GUEST_ID int IDENTITY(1,1) PRIMARY KEY,
GUEST_NAME VARCHAR(50),
GUEST_SURNAME VARCHAR(50),
ADRESS VARCHAR(100),
CITY VARCHAR(50),
CITY_CODE VARCHAR(10),
COUNTRY VARCHAR(50),
STATUS VARCHAR(20),
COMMENT nvarchar(max);
用于记录 :
CREATE TABLE AUDIT_GUESTS (
ID int IDENTITY(1,1) PRIMARY KEY,
GUEST_ID int,
OLD_GUEST_NAME VARCHAR(50),
NEW_GUEST_NAME VARCHAR(50),
OLD_GUEST_SURNAME VARCHAR(50),
NEW_GUEST_SURNAME VARCHAR(50),
OLD_ADRESS VARCHAR(100),
NEW_ADRESS VARCHAR(100),
OLD_CITY VARCHAR(50),
NEW_CITY VARCHAR(50),
OLD_CITY_CODE VARCHAR(10),
NEW_CITY_CODE VARCHAR(10),
OLD_COUNTRY VARCHAR(50),
NEW_COUNTRY VARCHAR(50),
OLD_STATUS VARCHAR(20),
NEW_STATUS VARCHAR(20),
OLD_COMMENT nvarchar(max),
NEW_COMMENT nvarchar(max),
AUDIT_ACTION varchar(100),
AUDIT_TIMESTAMP datetime);
我想在 GUESTS
表上创建一个触发器,记录 AUDIT_GUESTS
表中的所有更改。如何在 SQL Server 2014 Express 中做到这一点?
我试过 :
create TRIGGER trgAfterUpdate ON [dbo].[GUESTS]
FOR UPDATE
AS
declare @GUEST_ID int;
declare @GUEST_NAME varchar(50);
declare @GUEST_SURNAME VARCHAR(50);
declare @ADRESS VARCHAR(100);
declare @CITY VARCHAR(50);
declare @CITY_CODE VARCHAR(10);
declare @COUNTRY VARCHAR(50);
declare @STATUS VARCHAR(20);
declare @COMMENT nvarchar(max);
declare @AUDIT_ACTION varchar(100);
declare @AUDIT_TIMESTAMP datetime;
select @GUEST_ID=i.GUEST_ID from inserted i;
select @GUEST_NAME=i.GUEST_NAME from inserted i;
select @GUEST_SURNAME=i.GUEST_SURNAME from inserted i;
select @ADRESS=i.ADRESS from inserted i;
select @CITY=i.CITY from inserted i;
select @CITY_CODE=i.CITY_CODE from inserted i;
select @COUNTRY=i.COUNTRY from inserted i;
select @STATUS=i.STATUS from inserted i;
select @COMMENT=i.COMMENT from inserted i;
if update(GUEST_NAME)
set @audit_action='Updated Record -- After Update Trigger.';
if update(GUEST_SURNAME)
set @audit_action='Updated Record -- After Update Trigger.';
if update(ADRESS)
set @audit_action='Updated Record -- After Update Trigger.';
if update(CITY)
set @audit_action='Updated Record -- After Update Trigger.';
if update(CITY_CODE)
set @audit_action='Updated Record -- After Update Trigger.';
if update(COUNTRY)
set @audit_action='Updated Record -- After Update Trigger.';
if update(STATUS)
set @audit_action='Updated Record -- After Update Trigger.';
if update(COMMENT)
set @audit_action='Updated Record -- After Update Trigger.';
insert into AUDIT_GUESTS
(GUEST_ID,GUEST_NAME,GUEST_SURNAME,ADRESS,CITY,CITY_CODE,COUNTRY,STATUS,COMMENT,audit_action,AUDIT_TIMESTAMP)
values(@GUEST_ID,@GUEST_NAME,@GUEST_SURNAME,@ADRESS,@CITY,@CITY_CODE,@COUNTRY,@STATUS,@COMMENT,@audit_action,getdate());
GO
效果还可以,但我希望看到新旧值。
在 SQLite 中,我有 :
CREATE TRIGGER [LOG_UPDATE]
AFTER UPDATE OF [GUEST_NAME], [GUEST_SURNAME], [ADRESS], [CITY], [CITY_CODE], [COUNTRY], [STATUS], [COMMENT]
ON [GUESTS]
BEGIN
INSERT INTO GUESTS_LOG
( GUEST_ID,
NAME_OLD,NAME_NEW,
SURNAME_OLD,SURNAME_NEW,
ADRESS_OLD,ADRESS_NEW,
CITY_OLD,CITY_NEW,
CITY_CODE_OLD,CITY_CODE_NEW,
COUNTRY_OLD,COUNTRY_NEW,
STATUS_OLD,STATUS_NEW,
COMMENT_OLD,COMMENT_NEW,sqlAction,DATE_TIME)
VALUES
(OLD.GUEST_ID,
OLD.GUEST_NAME,NEW.GUEST_NAME,
OLD.GUEST_SURNAME,NEW.GUEST_SURNAME,
OLD.ADRESS,NEW.ADRESS,
OLD.CITY,NEW.CITY,
OLD.CITY_CODE,NEW.CITY_CODE,
OLD.COUNTRY,NEW.COUNTRY,
OLD.STATUS,NEW.STATUS,
OLD.COMMENT,NEW.COMMENT,'record changed',datetime('now','localtime'));
END
工作正常。只是不知道如何将其传递给 SQL server。刚刚开始学习。
27
3
请阅读 Simple-talk.com 上 Pop Rivett 的 本文。它指导你创建一个通用触发器,记录所有更新列的 OLDVALUE 和 NEWVALUE。代码非常通用,你可以将其应用于任何你想审计的表,也可以应用于任何 CRUD 操作,即 INSERT、UPDATE 和 DELETE。唯一的要求是,要审计的表必须有一个 PRIMARY KEY(大多数设计良好的表都应有 PRIMARY KEY)。
下面是 GUESTS 表的相关代码。
我知道这已经是陈年旧事了,但也许能帮到别人。
不要记录"新"值。 您现有的表 GUESTS 已经记录了新值。 这样会重复输入数据,而且数据库的大小也会增长过快。
在这个示例中,我清理了这个表并将其最小化,但以下是记录更改所需的表:
当 GUESTS 表中的某个值(例如:访客姓名)发生变化时,只需使用触发器将整行数据原封不动地注销到日志/审计表中即可。 GUESTS 表中有当前数据,日志/审计表中有旧数据。
然后使用选择语句从两个表中获取数据:
你的数据将显示出表格的外观,从最旧到最新,第一行是创建时的数据,最后一行是当前数据。 您可以清楚地看到哪些数据发生了更改,谁更改了这些数据,以及何时更改的。
另外,我曾经使用过一个函数,在 RecordSet 中循环(在经典 ASP 中),只显示网页上更改的值。 这提供了一个很好的审计线索,用户可以看到随着时间的推移发生了哪些变化。
嘿,这很简单,看这个
@OLD_GUEST_NAME = d.GUEST_NAME from deleted d;
这个变量将存储您删除的旧值,然后您可以将其插入到您想要的位置。
例如