COLUMNS_UPDATED (Transact-SQL)

Возвращает битовый шаблон varbinary, который показывает, какие столбцы таблицы или представления добавлялись или изменялись. Функция COLUMNS_UPDATED используется в теле триггера языка Transact-SQL INSERT или UPDATE для проверки возможности выполнения триггером определенных операций.

Соглашения о синтаксическом обозначении в Transact-SQL

Синтаксис

COLUMNS_UPDATED ( ) 

Типы возвращаемых данных

varbinary

Замечания

Функция COLUMNS_UPDATED проверяет возможность выполнения операций триггером UPDATE или INSERT над множеством столбцов. Чтобы проверить срабатывание триггера UPDATE или INSERT на одном столбце, используйте инструкцию UPDATE().

Функция COLUMNS_UPDATED возвращает один или более байтов, которые упорядочены слева направо по принципу: крайний правый бит — наименее значащий бит в байте. Крайний правый бит крайнего левого байта представляет первый столбец в таблице, следующий бит слева представляет второй столбец и так далее. Функция COLUMNS_UPDATED возвращает множество байт, если таблица в которой триггер создается, содержит более чем восемь столбцов, с наименее значащим байтом в крайней левой позиции. Функция COLUMNS_UPDATED возвращает значение TRUE для всех столбцов при операциях INSERT, поскольку при вставке столбцам присваиваются явно указанные значения или неявные значения (NULL).

Чтобы проверить обновление и вставку в определенные столбцы, следуйте синтаксису битовых операций и целой битовой маске проверяемого столбца. Например, таблица t1 содержит столбцы C1, C2, C3, C4 и C5. Чтобы проверить, что все столбцы C2, C3 и C4 обновлены (в таблице t1 под действием триггера UPDATE), следуйте синтаксису & 14. Чтобы проверить обновление только столбца C2, определите & 2.

Функция COLUMNS_UPDATED может быть использована внутри триггера языка Transact-SQL INSERT или UPDATE.

Внимание!
В SQL Server 2008 столбец ORDINAL_POSITION представления INFORMATION_SCHEMA.COLUMNS не совместим с битовым шаблоном столбцов, возвращаемым функцией COLUMNS_UPDATED. Чтобы получить битовый шаблон, совместимый с функцией COLUMNS_UPDATED, обратитесь к свойству ColumnID системной функции COLUMNPROPERTY при запросе представления INFORMATION_SCHEMA.COLUMNS, как показано в следующем примере:

SELECT TABLE_NAME, COLUMN_NAME,
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';

Наборы столбцов

Если в таблице определен набор столбцов, функция COLUMNS_UPDATED действует следующими способами.

• Если столбец из набора столбцов явным образом обновляется, соответствующему биту для этого столбца присваивается значение 1, а биту для набора столбцов присваивается значение 1.

• Если набор столбцов явным образом обновляется, биту для набора столбцов присваивается значение 1 и биты для всех разреженных столбцов в этой таблице получают значение 1.

• При операциях вставки всем битам присваивается значение 1.

  Поскольку при изменениях в наборе столбцов битам для всех столбцов в наборе столбцов присваивается значение 1, столбцы в наборе столбцов, не подвергавшиеся изменениям, будут выглядеть как измененные. Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.

Примеры

А. Использование функции COLUMNS_UPDATED для проверки первых восьми столбцов таблицы

Следующий пример создает две таблицы: employeeData и auditEmployeeData. Таблица employeeData содержит сведения о заработной плате служащих и может быть изменена членами отдела кадров. Если номер социальной страховки (SSN), ежегодная заработная плата или номер банковского счета служащего изменен, то создается запись аудита и вставляется в таблицу аудита auditEmployeeData.

С помощью функции COLUMNS_UPDATED() можно выполнить быструю проверку всех изменений в столбцах, которые содержат важные сведения о служащих. Использование функции COLUMNS_UPDATED() таким способом будет оправдано, только если пытаться выявить изменения в первых восьми столбцах таблицы.

USE AdventureWorks;
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'employeeData')
   DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'auditEmployeeData')
   DROP TABLE auditEmployeeData;
GO
CREATE TABLE employeeData (
   emp_id int NOT NULL,
   emp_bankAccountNumber char (10) NOT NULL,
   emp_salary int NOT NULL,
   emp_SSN char (11) NOT NULL,
   emp_lname nchar (32) NOT NULL,
   emp_fname nchar (32) NOT NULL,
   emp_manager int NOT NULL
   );
GO
CREATE TABLE auditEmployeeData (
   audit_log_id uniqueidentifier DEFAULT NEWID(),
   audit_log_type char (3) NOT NULL,
   audit_emp_id int NOT NULL,
   audit_emp_bankAccountNumber char (10) NULL,
   audit_emp_salary int NULL,
   audit_emp_SSN char (11) NULL,
   audit_user sysname DEFAULT SUSER_SNAME(),
   audit_changed datetime DEFAULT GETDATE()
   );
GO
CREATE TRIGGER updEmployeeData 
ON employeeData 
AFTER UPDATE AS
/*Check whether columns 2, 3 or 4 have been updated. If any or all
 columns 2, 3 or 4 have been changed, create an audit record. The 
bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test 
whether all columns 2, 3, and 4 are updated, use = 14 instead of >0
 (below).*/
   IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see whether all columns 2, 3, 
and 4 are updated.*/
      BEGIN
-- Audit OLD record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'OLD', 
            del.emp_id,
            del.emp_bankAccountNumber,
            del.emp_salary,
            del.emp_SSN
         FROM deleted del
-- Audit NEW record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'NEW',
            ins.emp_id,
            ins.emp_bankAccountNumber,
            ins.emp_salary,
            ins.emp_SSN
         FROM inserted ins
   END;
GO
/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
   VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32);
GO
/*Updating the employee record for employee number 101 to change the 
salary to 51000 causes the UPDATE trigger to fire and an audit trail to 
be produced.*/
UPDATE employeeData
   SET emp_salary = 51000
   WHERE emp_id = 101;
GO
SELECT * FROM auditEmployeeData;
GO
/*Updating the employee record for employee number 101 to change both 
the bank account number and social security number (SSN) causes the 
UPDATE trigger to fire and an audit trail to be produced.*/
UPDATE employeeData
   SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
   WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO

Б. Использование функции COLUMNS_UPDATED для проверки более чем восьми столбцов

Чтобы проверить обновления других столбцов таблицы, кроме первых восьми, используйте функцию SUBSTRING для проверки корректности бита, возвращенного функцией COLUMNS_UPDATED. В следующем примере выполняется проверка обновления столбцов 3, 5 и 9 таблицы AdventureWorks.Person.Contact.

USE AdventureWorks;
GO
IF OBJECT_ID (N'uContact2', N'TR') IS NOT NULL
    DROP TRIGGER Person.tr1;
GO
CREATE TRIGGER uContact2 ON Person.Contact
AFTER UPDATE AS
    IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20) 
        AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) ) 
    PRINT 'Columns 3, 5 and 9 updated';
GO
UPDATE Person.Contact 
   SET Title=Title,
      MiddleName=MiddleName,
      EmailPromotion=EmailPromotion;
GO

См. также

Справочник

побитовые операторы (Transact-SQL)

CREATE TRIGGER (Transact-SQL)

UPDATE() (Transact-SQL)