[SQL] 감사 테이블에 SQL 서버의 로그 레코드 변경
SQL감사 테이블에 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);
내 AUDIT_GUESTS 테이블의 모든 변경 사항을 기록 내 손님 테이블에 트리거를 작성하고 싶습니다. 어떻게 SQL 서버 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 서버에이를 전달하는 방법을 알고있다. 그냥 학습 시작.
해결법
-
==============================
1.팝 Rivett에 의해 Simple-talk.com에이 기사를 살펴 보자. 그것은 모든 업데이트 열에 OLDVALUE, NEWVALUE를 기록하는 일반적인 트리거를 만드는 과정을 안내합니다. 코드는 매우 일반적이며, 당신은 어떤 CRUD 작업 즉 INSERT, UPDATE에 대한 또한, 당신이 감사 싶어 모든 테이블에 적용하고 삭제할 수 있습니다. 유일한 요구 사항은 테이블 (대부분의 잘 설계된 테이블이 어쨌든해야한다) 기본 키가 있어야 감사 할 것입니다.
팝 Rivett에 의해 Simple-talk.com에이 기사를 살펴 보자. 그것은 모든 업데이트 열에 OLDVALUE, NEWVALUE를 기록하는 일반적인 트리거를 만드는 과정을 안내합니다. 코드는 매우 일반적이며, 당신은 어떤 CRUD 작업 즉 INSERT, UPDATE에 대한 또한, 당신이 감사 싶어 모든 테이블에 적용하고 삭제할 수 있습니다. 유일한 요구 사항은 테이블 (대부분의 잘 설계된 테이블이 어쨌든해야한다) 기본 키가 있어야 감사 할 것입니다.
여기 손님 테이블에 대한 관련 코드입니다.
1) 감사 테이블을 만듭니다.
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다음 2) 참석자 테이블에 UPDATE 트리거를 작성.
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 -
==============================
2.나는 이것이 오래 알고,하지만 어쩌면이 다른 사람을 도움이 될 것입니다.
나는 이것이 오래 알고,하지만 어쩌면이 다른 사람을 도움이 될 것입니다.
"새로운"값을 기록하지 마십시오. 기존 테이블, 손님, 새로운 값이 있습니다. 당신은 데이터의 두 항목이 있습니다, 플러스 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 )값이 손님 테이블 (예 : 고객 이름)으로 변경하면 트리거를 사용하여 로그 / 감사 테이블에,있는 그대로, 단순히 데이터의 전체 행을 로그 오프합니다. 당신의 손님 테이블은 로그 / 감사 테이블은 이전 데이터를 가지고, 현재의 데이터가 있습니다.
그런 다음 두 테이블에서 데이터를 얻을 수있는 선택 문을 사용 :
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귀하의 데이터는 테이블의 첫 번째 행 생성 및 마지막 행은 현재의 데이터되고 있었는지 인 상태, 오래된에서 최신에, 어떻게 생겼는지 함께 올 것이다. 그들은 그것을 변경 할 때 당신은 그것을 변경하는 사람들 정확히 변경을 참조 할 수 있습니다.
선택적으로, 나는 (클래식 ASP 단위) 레코드 집합을 통해 루프 기능을 가지고 사용하고, 단지 웹 페이지에서 변경 한 것을 값으로 표시됩니다. 그것은 사용자가 시간이 지남에 변경이 있었는지 볼 수 있도록 GREAT 감사 추적을 위해 만든.
-
==============================
3.이 두 가지 버그 수정 코드입니다. 첫 번째 버그 수정은이 질문에 대한 허용 대답에 코멘트에 Royi Namir 언급했다. 버그는 트리거 코드에서 버그에서에 StackOverflow에 설명되어 있습니다. 두 번째는 자신의 이름에 대한 배수 단어 Fandango68 및 수정 열 @에 의해 발견되었다.
이 두 가지 버그 수정 코드입니다. 첫 번째 버그 수정은이 질문에 대한 허용 대답에 코멘트에 Royi Namir 언급했다. 버그는 트리거 코드에서 버그에서에 StackOverflow에 설명되어 있습니다. 두 번째는 자신의 이름에 대한 배수 단어 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 -
==============================
4.이봐, 그것은 매우 간단이 참조
이봐, 그것은 매우 간단이 참조
@OLD_GUEST_NAME = d.GUEST_NAME 삭제 D에서;
이 변수는 이전 삭제 된 값을 저장하고 당신이 원하는 곳에 당신은 그것을 삽입 할 수 있습니다.
예 -에 대한
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
from https://stackoverflow.com/questions/19737723/log-record-changes-in-sql-server-in-an-audit-table by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
| [SQL] 단지 SQL을 사용하여 SQL Server 2005의 이미지 필드에 그림 삽입 (0) | 2020.04.17 |
|---|---|
| [SQL] DISTINCT를 통해 여러 열을 계산 (0) | 2020.04.17 |
| [SQL] 처리 DATETIME는 JDBC에 0000-00-00 0시 0분 0초 값 (0) | 2020.04.17 |
| [SQL] 컬렉션 / 배열 / 목록에서 쉼표로 구분 된 문자열을 만들기위한 가장 정교한 방법은? (0) | 2020.04.17 |
| [SQL] 자바에서 좋은 동적 SQL 빌더 라이브러리가 있습니까? [닫은] (0) | 2020.04.17 |