복붙노트

[SQL] SQL 서버 업데이트 트리거 만 수정하기 필드

SQL

SQL 서버 업데이트 트리거 만 수정하기 필드

나는 잘 나는 (사람이 이루어진 경우, 이미 일을하고 있지만, 사람이 내 시간을 절약 할 수 있다면, 나는 그것을 appriciate 것입니다) 몇 가지 빠른 바로 가기를 필요 COLUMNS_UPDATED 알고있다

난 단지 업데이트 된 열 값의 XML을 basicaly 필요, 나는 복제 목적이 필요합니다.

삽입 SELECT * FROM 나에게 각 열을 제공하지만, 난 단지 업데이트 된 사람이 필요합니다.

다음과 같은 ...

CREATE TRIGGER DBCustomers_Insert
    ON DBCustomers
    AFTER UPDATE
AS
BEGIN
    DECLARE @sql as NVARCHAR(1024);
    SET @sql = 'SELECT ';


    I NEED HELP FOR FOLLOWING LINE ...., I can manually write every column, but I need 
    an automated routin which can work regardless of column specification
    for each column, if its modified append $sql = ',' + columnname...

    SET @sql = $sql + ' FROM inserted FOR XML RAW';

    DECLARE @x as XML;
    SET @x = CAST(EXEC(@sql) AS XML);


    .. use @x

END

해결법

  1. ==============================

    1.트리거 내부, 당신은 업데이트 된 값을 얻기 위해이 같은 COLUMNS_UPDATED ()를 사용할 수 있습니다

    트리거 내부, 당신은 업데이트 된 값을 얻기 위해이 같은 COLUMNS_UPDATED ()를 사용할 수 있습니다

    -- Get the table id of the trigger
--
DECLARE @idTable      INT

SELECT  @idTable = T.id 
FROM    sysobjects P JOIN sysobjects T ON P.parent_obj = T.id 
WHERE   P.id = @@procid

-- Get COLUMNS_UPDATED if update
--
DECLARE @Columns_Updated VARCHAR(50)

SELECT  @Columns_Updated = ISNULL(@Columns_Updated + ', ', '') + name 
FROM    syscolumns 
WHERE   id = @idTable   
AND     CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1) > 0

    이상의 62 열 .. Arth.Overflow와 테이블이 때이 코드 조각은 실패 ...

    여기 이상 62 열을 처리하지만, 업데이트 된 컬럼의 수를 줄 최종 버전입니다. 그것은 이름을 얻기 위해 'SYSCOLUMNS'와 함께 링크를 쉽게

    DECLARE @Columns_Updated VARCHAR(100)
SET     @Columns_Updated = ''   

DECLARE @maxByteCU INT
DECLARE @curByteCU INT
SELECT  @maxByteCU = DATALENGTH(COLUMNS_UPDATED()), 
        @curByteCU = 1

WHILE @curByteCU <= @maxByteCU BEGIN
    DECLARE @cByte INT
    SET     @cByte = SUBSTRING(COLUMNS_UPDATED(), @curByteCU, 1)

    DECLARE @curBit INT
    DECLARE @maxBit INT
    SELECT  @curBit = 1, 
            @maxBit = 8
    WHILE @curBit <= @maxBit BEGIN
        IF CONVERT(BIT, @cByte & POWER(2,@curBit - 1)) <> 0 
            SET @Columns_Updated = @Columns_Updated + '[' + CONVERT(VARCHAR, 8 * (@curByteCU - 1) + @curBit) + ']'
        SET @curBit = @curBit + 1
    END
    SET @curByteCU = @curByteCU + 1
END
  2. ==============================

    2.나는 전혀 COLUMNS_UPDATED를 사용하지 않는 다른 완전히 다른 솔루션을했습니다 않으며, 런타임시 동적 SQL을 구축에 의존한다. (당신은 디자인 타임에 동적 SQL을 사용하고 싶지만 또 다른 이야기를 먹으 렴 수 있습니다.)

    나는 전혀 COLUMNS_UPDATED를 사용하지 않는 다른 완전히 다른 솔루션을했습니다 않으며, 런타임시 동적 SQL을 구축에 의존한다. (당신은 디자인 타임에 동적 SQL을 사용하고 싶지만 또 다른 이야기를 먹으 렴 수 있습니다.)

    당신은 단지 각각에 대해 고유 키, 필드 값 및 필드 이름 열이 남아 있습니다 그래서 기본적으로 당신은 그들 각각의 피벗 해제, 삽입 및 삭제 테이블로 시작합니다. 그럼 당신은 변경된 아무것도 두 개의 필터를 가입 할 수 있습니다.

    여기에 전체 작업 예제가 기록되는 것을 보여주는 몇 가지 테스트 통화를 포함한다.

    -- -------------------- Setup tables and some initial data --------------------
CREATE TABLE dbo.Sample_Table (ContactID int, Forename varchar(100), Surname varchar(100), Extn varchar(16), Email varchar(100), Age int );
INSERT INTO Sample_Table VALUES (1,'Bob','Smith','2295','bs@example.com',24);
INSERT INTO Sample_Table VALUES (2,'Alice','Brown','2255','ab@example.com',32);
INSERT INTO Sample_Table VALUES (3,'Reg','Jones','2280','rj@example.com',19);
INSERT INTO Sample_Table VALUES (4,'Mary','Doe','2216','md@example.com',28);
INSERT INTO Sample_Table VALUES (5,'Peter','Nash','2214','pn@example.com',25);

CREATE TABLE dbo.Sample_Table_Changes (ContactID int, FieldName sysname, FieldValueWas sql_variant, FieldValueIs sql_variant, modified datetime default (GETDATE()));

GO

-- -------------------- Create trigger --------------------
CREATE TRIGGER TriggerName ON dbo.Sample_Table FOR DELETE, INSERT, UPDATE AS
BEGIN
    SET NOCOUNT ON;
    --Unpivot deleted
    WITH deleted_unpvt AS (
        SELECT ContactID, FieldName, FieldValue
        FROM 
           (SELECT ContactID
                , cast(Forename as sql_variant) Forename
                , cast(Surname as sql_variant) Surname
                , cast(Extn as sql_variant) Extn
                , cast(Email as sql_variant) Email
                , cast(Age as sql_variant) Age
           FROM deleted) p
        UNPIVOT
           (FieldValue FOR FieldName IN 
              (Forename, Surname, Extn, Email, Age)
        ) AS deleted_unpvt
    ),
    --Unpivot inserted
    inserted_unpvt AS (
        SELECT ContactID, FieldName, FieldValue
        FROM 
           (SELECT ContactID
                , cast(Forename as sql_variant) Forename
                , cast(Surname as sql_variant) Surname
                , cast(Extn as sql_variant) Extn
                , cast(Email as sql_variant) Email
                , cast(Age as sql_variant) Age
           FROM inserted) p
        UNPIVOT
           (FieldValue FOR FieldName IN 
              (Forename, Surname, Extn, Email, Age)
        ) AS inserted_unpvt
    )

    --Join them together and show what's changed
    INSERT INTO Sample_Table_Changes (ContactID, FieldName, FieldValueWas, FieldValueIs)
    SELECT Coalesce (D.ContactID, I.ContactID) ContactID
        , Coalesce (D.FieldName, I.FieldName) FieldName
        , D.FieldValue as FieldValueWas
        , I.FieldValue AS FieldValueIs 
    FROM 
        deleted_unpvt d

            FULL OUTER JOIN 
        inserted_unpvt i
            on      D.ContactID = I.ContactID 
                AND D.FieldName = I.FieldName
    WHERE
         D.FieldValue <> I.FieldValue --Changes
        OR (D.FieldValue IS NOT NULL AND I.FieldValue IS NULL) -- Deletions
        OR (D.FieldValue IS NULL AND I.FieldValue IS NOT NULL) -- Insertions
END
GO
-- -------------------- Try some changes --------------------
UPDATE Sample_Table SET age = age+1;
UPDATE Sample_Table SET Extn = '5'+Extn where Extn Like '221_';

DELETE FROM Sample_Table WHERE ContactID = 3;

INSERT INTO Sample_Table VALUES (6,'Stephen','Turner','2299','st@example.com',25);

UPDATE Sample_Table SET ContactID = 7 where ContactID = 4; --this will be shown as a delete and an insert
-- -------------------- See the results --------------------
SELECT *, SQL_VARIANT_PROPERTY(FieldValueWas, 'BaseType') FieldBaseType, SQL_VARIANT_PROPERTY(FieldValueWas, 'MaxLength') FieldMaxLength from Sample_Table_Changes;

-- -------------------- Cleanup --------------------
DROP TABLE dbo.Sample_Table; DROP TABLE dbo.Sample_Table_Changes;

    그래서 더 BIGINT 비트 필드와 ARTH 오버 플로우 문제 장난하지 않습니다. 당신이 디자인 타임에 비교하려는 열을 알고 있다면 당신은 어떤 동적 SQL이 필요하지 않습니다.

    단점에 출력은 다른 형식으로 모든 필드 값을 다시 출력을 선회로의 sql_variant로, 첫 번째는 고정 될 수있는 변환되고, 두 번째는 지식을 기반으로 필요한 유형의 다시 개주에 의해 고정 될 수있다 테이블의 설계, 그러나이 모두는 일부 복잡한 동적 SQL을 필요로한다. 이들 모두는 XML 출력에 문제가되지 않을 수 있습니다. 이 질문은 같은 형식의 출력 등을 받고 비슷한 않습니다.

    편집 : 당신이 다음 변경할 수 있습니다 당신은 여전히이 방법을 사용할 수 있습니다 자연의 기본 키가있는 경우, 아래의 의견을 검토. 당신은 방금 NEWID () 함수를 사용하여 GUID와 기본적으로 채워지는 열을 추가해야합니다. 그런 다음 기본 키 대신에이 열을 사용합니다.

    이 필드에 인덱스를 추가 할 수 있지만, 트리거의 삭제 및 삽입 테이블이 메모리에로는 사용되지 수 있으며 성능에 부정적인 영향을 미칠 수 있습니다.

  3. ==============================

    3.나는 간단한 "한 줄"로 일을했습니다. , 수 등 피벗, 루프, 많은 변수를 사용하지 않고는 절차 적 프로그래밍처럼 보이는. SQL은 프로세스 데이터 세트를 사용한다 :-), 상기 용액은 :

    나는 간단한 "한 줄"로 일을했습니다. , 수 등 피벗, 루프, 많은 변수를 사용하지 않고는 절차 적 프로그래밍처럼 보이는. SQL은 프로세스 데이터 세트를 사용한다 :-), 상기 용액은 :

    DECLARE @sql as NVARCHAR(1024);

select @sql = coalesce(@sql + ',' + quotename(column_name), quotename(column_name))
from INFORMATION_SCHEMA.COLUMNS
where substring(columns_updated(), columnproperty(object_id(table_schema + '.' + table_name, 'U'), column_name, 'columnId') / 8 + 1, 1) & power(2, -1 + columnproperty(object_id(table_schema + '.' + table_name, 'U'), column_name, 'columnId') % 8 ) > 0
    and table_name = 'DBCustomers'
    -- and column_name in ('c1', 'c2') -- limit to specific columns
    -- and column_name not in ('c3', 'c4') -- or exclude specific columns

SET @sql = 'SELECT ' + @sql + ' FROM inserted FOR XML RAW';

DECLARE @x as XML;
SET @x = CAST(EXEC(@sql) AS XML);

    그것은 COLUMNS_UPDATED를 사용하는 여덟 개 이상의 열을 담당 - 당신이 원하는대로 많은 열로 처리합니다.

    그것은 COLUMNPROPERTY를 사용하여 얻을해야 적절한 열 순서를 처리합니다.

    그것은 포함하거나 특정 열을 제외 할 수 있도록이보기 열을 기반으로합니다.

  4. ==============================

    4.아래의 코드는 64 이상 열 및 로그에만 업데이트 된 열 작동합니다. 주석의 지시에 따라 모두 잘해야한다.

    아래의 코드는 64 이상 열 및 로그에만 업데이트 된 열 작동합니다. 주석의 지시에 따라 모두 잘해야한다.

    /*******************************************************************************************
 *         Add the below table to your database to track data changes using the trigger    *
 *         below. Remember to change the variables in the trigger to match the table that  *
 *         will be firing the trigger                                                      *
 *******************************************************************************************/
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[AuditDataChanges]
(
  [RecordId] [INT] IDENTITY(1, 1)
                   NOT NULL ,
  [TableName] [VARCHAR](50) NOT NULL ,
  [RecordPK] [VARCHAR](50) NOT NULL ,
  [ColumnName] [VARCHAR](50) NOT NULL ,
  [OldValue] [VARCHAR](50) NULL ,
  [NewValue] [VARCHAR](50) NULL ,
  [ChangeDate] [DATETIME2](7) NOT NULL ,
  [UpdatedBy] [VARCHAR](50) NOT NULL ,
  CONSTRAINT [PK_AuditDataChanges] PRIMARY KEY CLUSTERED
    ( [RecordId] ASC )
    WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
           ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON  [PRIMARY];

GO

ALTER TABLE [dbo].[AuditDataChanges] ADD  CONSTRAINT [DF_AuditDataChanges_ChangeDate]  DEFAULT (GETDATE()) FOR [ChangeDate];
GO



/************************************************************************************************
 * Add the below trigger to any table you want to audit data changes on. Changes will be saved  *
 * in the AuditChangesTable.                                                                    *
 ************************************************************************************************/


ALTER TRIGGER trg_Survey_Identify_Updated_Columns ON Survey --Change to match your table name
   FOR INSERT, UPDATE
AS
SET NOCOUNT ON;

DECLARE @sql VARCHAR(5000) ,
    @sqlInserted NVARCHAR(500) ,
    @sqlDeleted NVARCHAR(500) ,
    @NewValue NVARCHAR(100) ,
    @OldValue NVARCHAR(100) ,
    @UpdatedBy VARCHAR(50) ,
    @ParmDefinitionD NVARCHAR(500) ,
    @ParmDefinitionI NVARCHAR(500) ,
    @TABLE_NAME VARCHAR(100) ,
    @COLUMN_NAME VARCHAR(100) ,
    @modifiedColumnsList NVARCHAR(4000) ,
    @ColumnListItem NVARCHAR(500) ,
    @Pos INT ,
    @RecordPk VARCHAR(50) ,
    @RecordPkName VARCHAR(50);

SELECT  *
INTO    #deleted
FROM    deleted;
SELECT  *
INTO    #Inserted
FROM    inserted;

SET @TABLE_NAME = 'Survey'; ---Change to your table name
SELECT  @UpdatedBy = UpdatedBy --Change to your column name for the user update field
FROM    inserted;
SELECT  @RecordPk = SurveyId --Change to the table primary key field
FROM    inserted;   
SET @RecordPkName = 'SurveyId';
SET @modifiedColumnsList = STUFF(( SELECT   ',' + name
                                   FROM     sys.columns
                                   WHERE    object_id = OBJECT_ID(@TABLE_NAME)
                                            AND SUBSTRING(COLUMNS_UPDATED(),
                                                          ( ( column_id
                                                          - 1 ) / 8 + 1 ),
                                                          1) & ( POWER(2,
                                                          ( ( column_id
                                                          - 1 ) % 8 + 1 )
                                                          - 1) ) = POWER(2,
                                                          ( column_id - 1 )
                                                          % 8)
                                 FOR
                                   XML PATH('')
                                 ), 1, 1, '');


WHILE LEN(@modifiedColumnsList) > 0
    BEGIN
        SET @Pos = CHARINDEX(',', @modifiedColumnsList);
        IF @Pos = 0
            BEGIN
                SET @ColumnListItem = @modifiedColumnsList;
            END;
        ELSE
            BEGIN
                SET @ColumnListItem = SUBSTRING(@modifiedColumnsList, 1,
                                                @Pos - 1);
            END;    

        SET @COLUMN_NAME = @ColumnListItem;
        SET @ParmDefinitionD = N'@OldValueOut NVARCHAR(100) OUTPUT';
        SET @ParmDefinitionI = N'@NewValueOut NVARCHAR(100) OUTPUT';
        SET @sqlDeleted = N'SELECT @OldValueOut=' + @COLUMN_NAME
            + ' FROM #deleted where ' + @RecordPkName + '='
            + CONVERT(VARCHAR(50), @RecordPk);
        SET @sqlInserted = N'SELECT @NewValueOut=' + @COLUMN_NAME
            + ' FROM #Inserted where ' + @RecordPkName + '='
            + CONVERT(VARCHAR(50), @RecordPk);
        EXECUTE sp_executesql @sqlDeleted, @ParmDefinitionD,
            @OldValueOut = @OldValue OUTPUT;
        EXECUTE sp_executesql @sqlInserted, @ParmDefinitionI,
            @NewValueOut = @NewValue OUTPUT;
        IF ( LTRIM(RTRIM(@NewValue)) != LTRIM(RTRIM(@OldValue)) )
            BEGIN   
                SET @sql = 'INSERT INTO [dbo].[AuditDataChanges]
                                               ([TableName]
                                               ,[RecordPK]
                                               ,[ColumnName]
                                               ,[OldValue]
                                               ,[NewValue]
                                               ,[UpdatedBy])
                                         VALUES
                                               (' + QUOTENAME(@TABLE_NAME, '''') + '
                                               ,' + QUOTENAME(@RecordPk, '''') + '
                                               ,' + QUOTENAME(@COLUMN_NAME, '''') + '
                                               ,' + QUOTENAME(@OldValue, '''') + '
                                               ,' + QUOTENAME(@NewValue, '''') + '
                                               ,' + QUOTENAME(@UpdatedBy, '''') + ')';


                EXEC (@sql);
            END;     
        SET @COLUMN_NAME = '';
        SET @NewValue = '';
        SET @OldValue = '';
        IF @Pos = 0
            BEGIN
                SET @modifiedColumnsList = '';
            END;
        ELSE
            BEGIN
           -- start substring at the character after the first comma
                SET @modifiedColumnsList = SUBSTRING(@modifiedColumnsList,
                                                     @Pos + 1,
                                                     LEN(@modifiedColumnsList)
                                                     - @Pos);
            END;
    END;
DROP TABLE #Inserted;
DROP TABLE #deleted;

GO
  5. ==============================

    5.열 이름을 코딩 한 다음 임시 테이블의 내용을 비교하기 위해 테이블 ​​정의를 기반으로 쿼리를 작성, 임시 테이블에 삭제 된 테이블의 내용을 드롭하는 것입니다 당신이 열심히하지 않고이 작업을 수행 할 수 있다는 것을 나에게 발생하는 유일한 방법 실제 테이블, 그리고 그들이 또는 일치하지 않는 않는 여부에 따라 구분 된 열 목록을 반환합니다. 인정 하듯이, 아래는 정교한입니다.

    열 이름을 코딩 한 다음 임시 테이블의 내용을 비교하기 위해 테이블 ​​정의를 기반으로 쿼리를 작성, 임시 테이블에 삭제 된 테이블의 내용을 드롭하는 것입니다 당신이 열심히하지 않고이 작업을 수행 할 수 있다는 것을 나에게 발생하는 유일한 방법 실제 테이블, 그리고 그들이 또는 일치하지 않는 않는 여부에 따라 구분 된 열 목록을 반환합니다. 인정 하듯이, 아래는 정교한입니다.

    Declare @sql nvarchar(4000)
DECLARE @ParmDefinition nvarchar(500)
Declare @OutString varchar(8000)
Declare @tbl sysname

Set @OutString = ''
Set @tbl = 'SomeTable' --The table we are interested in
--Store the contents of deleted in temp table
Select * into #tempDelete from deleted 
--Build sql string based on definition 
--of table 
--to retrieve the column name
--or empty string
--based on comparison between
--target table and temp table
set @sql = ''
Select @sql = @sql + 'Case when IsNull(i.[' + Column_Name + 
'],0) = IsNull(d.[' + Column_name + '],0) then '''' 
 else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +'
from information_schema.columns 
where table_name = @tbl
--Define output parameter
set @ParmDefinition = '@OutString varchar(8000) OUTPUT'
--Format sql
set @sql = 'Select @OutString = ' 
+ Substring(@sql,1 , len(@sql) -1) + 
' From SomeTable i  ' --Will need to be updated for target schema
+ ' inner join #tempDelete d on
i.PK = d.PK ' --Will need to be updated for target schema
--Execute sql and retrieve desired column list in output parameter
exec sp_executesql @sql, @ParmDefinition, @OutString OUT
drop table  #tempDelete
--strip trailing column if a non-zero length string 
--was returned
if Len(@Outstring) > 0 
    Set @OutString = Substring(@OutString, 1, Len(@Outstring) -1)
--return comma delimited list of changed columns. 
Select @OutString 
End
  6. ==============================

    6.나는 열 이름의 목록 (저자의 권고에 따라) 쉼표로 구분하여 얻을 수있는 허용 대답을 변형. 출력 - '열 1, 열 2, COLUMN5'로 "바로 Columns_Updated"

    나는 열 이름의 목록 (저자의 권고에 따라) 쉼표로 구분하여 얻을 수있는 허용 대답을 변형. 출력 - '열 1, 열 2, COLUMN5'로 "바로 Columns_Updated"

    -- get names of updated columns
DECLARE @idTable      INT
declare @ColumnName nvarchar(300)
declare @ColId int

SELECT  @idTable = T.id 
FROM    sysobjects P JOIN sysobjects T ON P.parent_obj = T.id 
WHERE   P.id = @@procid

DECLARE @changedProperties nvarchar(max) = ''

DECLARE @Columns_Updated VARCHAR(2000) = ''

DECLARE @maxByteCU INT
DECLARE @curByteCU INT
SELECT  @maxByteCU = DATALENGTH(COLUMNS_UPDATED()), 
        @curByteCU = 1

WHILE @curByteCU <= @maxByteCU BEGIN
    DECLARE @cByte INT
    SET     @cByte = SUBSTRING(COLUMNS_UPDATED(), @curByteCU, 1)

    DECLARE @curBit INT
    DECLARE @maxBit INT
    SELECT  @curBit = 1, 
            @maxBit = 8
    WHILE @curBit <= @maxBit BEGIN
        IF CONVERT(BIT, @cByte & POWER(2, @curBit - 1)) <> 0 BEGIN
            SET @ColId = cast( CONVERT(VARCHAR, 8 * (@curByteCU - 1) + @curBit) as int)

            select @ColumnName = [Name]
            FROM syscolumns 
            WHERE id = @idTable and colid = @ColId

            SET @Columns_Updated = @Columns_Updated + ',' + @ColumnName
        END
        SET @curBit = @curBit + 1
    END
    SET @curByteCU = @curByteCU + 1
END
  7. ==============================

    7.릭 제공하는 샘플 코드는 여러 행 업데이트를 처리 부족.

    릭 제공하는 샘플 코드는 여러 행 업데이트를 처리 부족.

    저 아래로 릭의 버전을 향상 주시기 바랍니다 :

    USE [AFC] 가다 / ****** 대상 :. 트리거 [DBO] [trg_Survey_Identify_Updated_Columns] 스크립트 날짜 : 27/7/2018 14시 8분 49초 ****** / SET ANSI_NULLS ON 가다 SET QUOTED_IDENTIFIER ON 가다 ALTER TRIGGER [DBO]. [trg_Survey_Identify_Updated_Columns] ON은 [DBO]. [Sample_Table] --change은 테이블 이름과 일치하는 FOR INSERT ,최신 정보 같이 SET NOCOUNT ON; VARCHAR @Sql DECLARE (5000) , sqlInserted NVARCHAR (500) @ , sqlDeleted NVARCHAR (500) @ @ NewValue로 NVARCHAR (100) @에 OldValue NVARCHAR (100) , UpdatedBy VARCHAR @ (50) @ ParmDefinitionD NVARCHAR (500) , ParmDefinitionI NVARCHAR (500) @ , TABLE_NAME VARCHAR (100) @ , COLUMN_NAME VARCHAR (100) @ @ modifiedColumnsList NVARCHAR (4000) @ ColumnListItem NVARCHAR (500) , 포스 INT @ , RecordPk VARCHAR @ (50) , RecordPkName VARCHAR @ (50); 고르다 * INTO는 # 삭제 FROM 삭제; 고르다 * INTO #Inserted FROM 삽입; SET @TABLE_NAME = 'Sample_Table', 테이블 이름 --- 변경 DECLARE의 t_cursor의 커서 에 대한 SELECT ContactID가 FROM 삽입 OPEN t_cursor NEXT를 FETCH t_cursor FROM INTO @RecordPk @@ FETCH_STATUS = 0 WHILE BEGIN --select @UpdatedBy = 성 --change 사용자 업데이트 필드에 대한 열 이름 --from 삽입; --select @RecordPk = ContactID가 --change 테이블 기본 키 필드 --from 삽입; SET @RecordPkName = 'ContactID가'; SET @modifiedColumnsList = 다도 (( SELECT ','+ 이름 sys.columns FROM WHERE 한 object_id = OBJECT_ID (@TABLE_NAME) AND SUBSTRING (COLUMNS_UPDATED () ((COLUMN_ID - 1) / 8 + 1), 1) (POWER (2, ((COLUMN_ID - 1) % 8 + 1) - 1)) = POWER (2 (COLUMN_ID - 1) % 8) 용의 XML의 PATH ( '') ) 1, 1 ''); LEN (@modifiedColumnsList)> 0 WHILE BEGIN SET @Pos = CHARINDEX ( ',', @modifiedColumnsList); @Pos = 0 IF BEGIN SET @ColumnListItem = @modifiedColumnsList; 종료; 그밖에 BEGIN SET @ColumnListItem = SUBSTRING (@modifiedColumnsList, 1, @Pos - 1); 종료; SET @COLUMN_NAME = @ColumnListItem; 'OldValueOut NVARCHAR (100) OUTPUT @'SET @ParmDefinitionD = N; 'NewValueOut NVARCHAR (100) OUTPUT @'SET @ParmDefinitionI = N; SET @sqlDeleted = N'SELECT @ OldValueOut = '+ @COLUMN_NAME +'# 삭제 어디서 '+ @RecordPkName +'= '+ CONVERT (VARCHAR (50) @RecordPk); SET @sqlInserted = N'SELECT @ NewValueOut = '+ @COLUMN_NAME +'#Inserted FROM 어디 @RecordPkName + + '='+ CONVERT (VARCHAR (50) @RecordPk); sp_executesql을 @sqlDeleted를 EXECUTE @ ParmDefinitionD @ OldValueOut = @OldValue OUTPUT; sp_executesql을 @sqlInserted를 EXECUTE @ ParmDefinitionI @ NewValueOut = @NewValue OUTPUT; --print @newvalue --print @oldvalue IF (LTRIM (RTRIM (@NewValue))! = LTRIM (RTRIM (@OldValue))) BEGIN SET @Sql = "INSERT INTO [DBO]. AuditDataChanges]                                                ([TABLENAME]                                                [RecordPK]                                                [의 ColumnName]                                                [에 OldValue]                                                [NewValue로])                                          VALUES                                                ( '+ QUOTENAME (@TABLE_NAME' '' ') +'                                                '+ QUOTENAME (@RecordPk' '' ') +'                                                '+ QUOTENAME (@COLUMN_NAME' '' ') +'                                                '+ QUOTENAME (@OldValue' '' ') +'                                                '+ QUOTENAME (@NewValue' '' ') +'                                                '+') '; EXEC (@Sql); 종료; SET @COLUMN_NAME = ''; SET @NewValue은 ''; SET @OldValue은 ''; @Pos = 0 IF BEGIN SET @modifiedColumnsList은 ''; 종료; 그밖에 BEGIN - 첫 번째 쉼표 후 문자에서 하위 문자열 시작 SET @modifiedColumnsList = SUBSTRING (@modifiedColumnsList, @Pos + 1, LEN (@modifiedColumnsList) - @Pos); 종료; 종료; NEXT를 FETCH t_cursor FROM INTO @RecordPk 종료 DROP TABLE #Inserted; DROP TABLE은 # 삭제; CLOSE t_cursor; DEALLOCATE t_cursor;

  8. ==============================

    8.이 독특한 기록과 UpdatedBy 사용자와 업데이트 된 칼럼 값을 추적 로그의 완벽한 예입니다.

    이 독특한 기록과 UpdatedBy 사용자와 업데이트 된 칼럼 값을 추적 로그의 완벽한 예입니다.

    IF NOT EXISTS
      (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ColumnAuditLogs]') 
               AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
       CREATE TABLE ColumnAuditLogs
               (Type CHAR(1), 
               TableName VARCHAR(128), 
               PK VARCHAR(1000), 
               FieldName VARCHAR(128), 
               OldValue VARCHAR(1000), 
               NewValue VARCHAR(1000), 
               UpdateDate datetime, 
               UserName VARCHAR(128),
               UniqueId uniqueidentifier,
               UpdatedBy int
               )
GO

create TRIGGER TR_ABCTable_AUDIT ON ABCTable 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),
       @UniqueId varchar(100),
       @UpdatedBy VARCHAR(50) 


--You will need to change @TableName to match the table to be audited. 
-- Here we made ABCTable for your example.
SELECT @TableName = 'ABCTable' -- change table name accoring your table name

-- use for table unique records for everytime updation.
set @UniqueId = CONVERT(varchar(100),newID())
-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)

 SELECT  @UpdatedBy = ModifiedBy --Change to your column name for the user update field
FROM    inserted;


-- 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+'+','') 
       + '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 ColumnAuditLogs (    Type, 
               TableName, 
               PK, 
               FieldName, 
               OldValue, 
               NewValue, 
               UpdateDate, 
               UserName,
               UniqueId,
               [UpdatedBy])
select ''' + @Type + ''',''' 
       + @TableName + ''',' + @PKSelect
       + ',''' + @fieldname + ''''
       + ',convert(varchar(1000),d.' + @fieldname + ')'
       + ',convert(varchar(1000),i.' + @fieldname + ')'
       + ',''' + @UpdateDate + ''''
       + ',''' + @UserName + ''''
       + ',''' + @UniqueId + ''''
        + ',' + QUOTENAME(@UpdatedBy, '''')
       + ' 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

    9. from https://stackoverflow.com/questions/1254787/sql-server-update-trigger-get-only-modified-fields by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] PHP는 변수를 사용하여 동적 MySQL의 쿼리 만들기  (0) 2020.05.02
[SQL] 이름 데이터베이스 디자인 당신이 선호하는 표기법 그 이유는 무엇입니까?  (0) 2020.05.02
[SQL] 유효성 검사 SQL 스크립트 코드  (0) 2020.05.01
[SQL] 어떻게 MySQL의에 고유 ID를 생성하는?  (0) 2020.05.01
[SQL] 집계 함수와 SQL GROUP BY CASE 문  (0) 2020.05.01
,

티스토리툴바