Подтвердить что ты не робот

Триггер обновления SQL Server, Получить только измененные поля

Мне известно о COLUMNS_UPDATED, ну, мне нужно немного быстрого ярлыка (если кто-то сделал, я уже делаю это, но если кто-то может сэкономить мое время, я буду его использовать)

Мне нужно в основном XML только обновленных значений столбцов, мне нужно это для цели репликации.

SELECT * FROM insert дает мне каждый столбец, но мне нужны только обновленные.

что-то вроде следующего...

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
4b9b3361

ОТВЕТЫ

Ответ 1

Внутри триггера вы можете использовать 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

У меня есть другое совершенно другое решение, которое вообще не использует 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','[email protected]',24);
INSERT INTO Sample_Table VALUES (2,'Alice','Brown','2255','[email protected]',32);
INSERT INTO Sample_Table VALUES (3,'Reg','Jones','2280','[email protected]',19);
INSERT INTO Sample_Table VALUES (4,'Mary','Doe','2216','[email protected]',28);
INSERT INTO Sample_Table VALUES (5,'Peter','Nash','2214','[email protected]',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 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','[email protected]',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. Этот вопрос делает нечто похожее на возвращение вывода в том же формате.

Изменение: Изучив комментарии ниже, если у вас есть естественный первичный ключ, который может измениться, вы все равно можете использовать этот метод. Вам просто нужно добавить столбец, который по умолчанию заполнен GUID, используя функцию NEWID(). Затем вы используете этот столбец вместо первичного ключа.

Возможно, вы захотите добавить индекс в это поле, но поскольку удаленные и вставленные таблицы в триггере находятся в памяти, он может не привыкнуть и может отрицательно повлиять на производительность.

Ответ 3

Я сделал это как простой "однострочный". Без использования, поворота, петель, многих переменных и т.д., Что делает его похожим на процедурное программирование. 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.

Он основан на представлении COLUMNS, чтобы он мог включать или исключать только определенные столбцы.

Ответ 4

Единственный способ, с помощью которого вы могли бы выполнить это без жестких имен столбцов кодирования, - это удалить содержимое удаленной таблицы в временную таблицу, а затем построить запрос на основе определения таблицы для сравнения содержимого вашу таблицу temp и фактическую таблицу, и верните список разделенных столбцов на основе того, соответствуют ли они или не соответствуют. По общему признанию, нижеследующее является сложным.

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

Ответ 5

Ниже код работает для более чем 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

Ответ 6

Я преобразовал принятый ответ, чтобы получить список имен столбцов, разделенных запятой (согласно рекомендации автора). Выходные данные - "Columns_Updated" как "Столбец1, Столбец2, Столбец5"

-- 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

В примере кода, предоставленном Риком, отсутствует обработка для обновления нескольких строк.

Пожалуйста, дайте мне улучшить версию Рика, как показано ниже:

USE [AFC]
GO

/****** Object:  Trigger [dbo].[trg_Survey_Identify_Updated_Columns]    Script Date: 27/7/2018 14:08:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg_Survey_Identify_Updated_Columns] ON [dbo].[Sample_Table] --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 = 'Sample_Table';---Change to your table name

DECLARE t_cursor CURSOR
FOR
SELECT ContactID 
FROM inserted

OPEN t_cursor

FETCH NEXT
FROM t_cursor
INTO @RecordPk 

WHILE @@FETCH_STATUS = 0
BEGIN
	--SELECT @UpdatedBy = Surname --Change to your column name for the user update field
	--FROM inserted;
	--SELECT @RecordPk = ContactID --Change to the table primary key field
	--FROM inserted;
	SET @RecordPkName = 'ContactID';
	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;

		--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);
		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;

	FETCH NEXT
	FROM t_cursor
	INTO @RecordPk 
END

DROP TABLE #Inserted;

DROP TABLE #deleted;

CLOSE t_cursor;

DEALLOCATE t_cursor;

Ответ 8

Это прекрасный пример для журнала отслеживания обновленных значений по столбцам с уникальными записями и пользователем Updated.

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