テーブルの:
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サーバーに渡す方法がわからない。まだ勉強し始めたばかりです。
Simple-talk.com の Pop Rivett による この記事 を見てみてください。この記事では、全ての更新された列のOLDVALUE、NEWVALUEを記録する汎用トリガーを作成する方法を説明します。このコードは非常に汎用的で、監査したい任意のテーブルに適用することができ、また、INSERT、UPDATE、DELETEといったCRUD操作にも適用できます。唯一の要件は、監査対象のテーブルがPRIMARY KEYを持つことです(よく設計されたテーブルのほとんどは、いずれにしても持つべきです)。
以下は、GUESTSテーブルに関連するコードです。
IF NOT EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE Audit
(Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate datetime,
UserName VARCHAR(128))
GO
CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000)
--You will need to change @TableName to match the table to be audited.
-- Here we made GUESTS for your example.
SELECT @TableName = 'GUESTS'
-- date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)
-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','')
+ '''<' + COLUMN_NAME
+ '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
EXEC (@sql)
END
END
GO
古い話ですが、もしかしたら誰かの役に立てるかもしれません。
quot;new"値を記録しないでください。 既存のテーブル「GUESTS」に新しい値が記録されます。 データの二重入力になるし、DBのサイズも大きくなりすぎるからです。
この例では、このテーブルをクリーンアップして最小化しましたが、変更をログオフするために必要なテーブルを以下に示します:
CREATE TABLE GUESTS (
GuestID INT IDENTITY(1,1) PRIMARY KEY,
GuestName VARCHAR(50),
ModifiedBy INT,
ModifiedOn DATETIME
)
CREATE TABLE GUESTS_LOG (
GuestLogID INT IDENTITY(1,1) PRIMARY KEY,
GuestID INT,
GuestName VARCHAR(50),
ModifiedBy INT,
ModifiedOn DATETIME
)
GUESTSテーブルの値(例:ゲスト名)が変わったら、トリガーを使ってその行のデータ全体をそのままLog/Auditテーブルにログオフするだけです。 GUESTSテーブルには現在のデータ、Log/Auditテーブルには古いデータが保存されます。
次に、select文で両方のテーブルからデータを取得します:
SELECT 0 AS 'GuestLogID', GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS] WHERE GuestID = 1
UNION
SELECT GuestLogID, GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS_LOG] WHERE GuestID = 1
ORDER BY ModifiedOn ASC
最初の行が作成時、最後の行が現在のデータで、古いものから新しいものへと、テーブルがどのように見えるかがデータとして表示されます。 何が変わったか、誰が変えたか、いつ変えたかを正確に把握することができます。
オプションで、RecordSetをループして(Classic ASPで)、変更された値だけをウェブページに表示する関数がありました。 これは、時間の経過とともに何が変更されたかをユーザーが確認できる、優れた監査証跡となりました。
これは、2つのバグ修正を含むコードです。 最初のバグ修正は、この質問に対する受け入れられた回答に関するコメントでRoyi Namirによって言及されました。 バグは、トリガーコードのバグのStackOverflowで説明されています。 2つ目は@ Fandango68によって発見され、名前の倍数の単語で列を修正します。
ALTER TRIGGER [dbo].[TR_person_AUDIT]
ON [dbo].[person]
FOR UPDATE
AS
DECLARE @bit INT,
@field INT,
@maxfield INT,
@char INT,
@fieldname VARCHAR(128),
@TableName VARCHAR(128),
@PKCols VARCHAR(1000),
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21),
@UserName VARCHAR(128),
@Type CHAR(1),
@PKSelect VARCHAR(1000)
--You will need to change @TableName to match the table to be audited.
-- Here we made GUESTS for your example.
SELECT @TableName = 'PERSON'
SELECT @UserName = SYSTEM_USER,
@UpdateDate = CONVERT(NVARCHAR(30), GETDATE(), 126)
-- Action
IF EXISTS (
SELECT *
FROM INSERTED
)
IF EXISTS (
SELECT *
FROM DELETED
)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
-- get list of columns
SELECT * INTO #ins
FROM INSERTED
SELECT * INTO #del
FROM DELETED
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.[' + c.COLUMN_NAME + '] = d.[' + c.COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect + '+', '')
+ '''<[' + COLUMN_NAME
+ ']=''+convert(varchar(100),
coalesce(i.[' + COLUMN_NAME + '],d.[' + COLUMN_NAME + ']))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0,
-- @maxfield = MAX(COLUMN_NAME)
@maxfield = -- FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
MAX(
COLUMNPROPERTY(
OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
COLUMN_NAME,
'ColumnID'
)
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(
COLUMNPROPERTY(
OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
COLUMN_NAME,
'ColumnID'
)
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMNPROPERTY(
OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
COLUMN_NAME,
'ColumnID'
) > @field
SELECT @bit = (@field - 1)% 8 + 1
SELECT @bit = POWER(2, @bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
OR @Type IN ('I', 'D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMNPROPERTY(
OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
COLUMN_NAME,
'ColumnID'
) = @field
SELECT @sql =
'
insert into Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
EXEC (@sql)
END
END
簡単なことなので、ご覧ください。
削除されたdから@OLD_GUEST_NAME = d.GUEST_NAME;
この変数は、古い削除された値を格納し、あなたが望む場所にそれを挿入することができます。
例えば
Create trigger testupdate on test for update, delete
as
declare @tableid varchar(50);
declare @testid varchar(50);
declare @newdata varchar(50);
declare @olddata varchar(50);
select @tableid = count(*)+1 from audit_test
select @testid=d.tableid from inserted d;
select @olddata = d.data from deleted d;
select @newdata = i.data from inserted i;
insert into audit_test (tableid, testid, olddata, newdata) values (@tableid, @testid, @olddata, @newdata)
go