COLUMNS_UPDATED (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Эта функция возвращает битовый шаблон 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 все успешно обновлены C3C4(с таблицей t1 с триггеромUPDATE), следуйте синтаксису.& 14 Чтобы проверить, обновляется ли только столбец C2 , укажите & 2. Фактические примеры см. в разделе Пример A и Примере Б.

Используйте COLUMNS_UPDATED в любом месте внутри Transact-SQL INSERT или UPDATE триггера. Если это выполняется за пределами триггера, возвращается значение NULL.

Столбец ORDINAL_POSITIONINFORMATION_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 AdventureWorks2022.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person';

Если триггер применяется к столбцу, значение COLUMNS_UPDATED будет возвращаться в виде true или 1, даже если значение столбца остается неизменным. Это нормальное поведение, и триггер должен реализовывать бизнес-логику, которая определяет, допустимы ли операции вставки, обновления и удаления.

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

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

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

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

• Для операций вставки для всех битов задано значение 1.

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

Примеры

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

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

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

USE AdventureWorks2022;
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 dbo.employeeData
(
    emp_id INT NOT NULL PRIMARY KEY,
    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 dbo.auditEmployeeData
(
    audit_log_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    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 dbo.updEmployeeData
ON dbo.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.
This bitmask translates into base_10 as: 2 + 4 + 8 = 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 dbo.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 AS del;
    -- Audit NEW record.
        INSERT INTO dbo.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 AS 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 dbo.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 dbo.employeeData
SET emp_bankAccountNumber = '133146A0',
    emp_SSN = 'R-M53550M'
WHERE emp_id = 101;
GO

SELECT * FROM dbo.auditEmployeeData;
GO

В. Использование COLUMNS_UPDATED для тестирования более восьми столбцов

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

USE AdventureWorks2022;
GO

IF OBJECT_ID(N'Person.uContact2', N'TR') IS NOT NULL
    DROP TRIGGER Person.uContact2;
GO

CREATE TRIGGER Person.uContact2
    ON Person.Person
    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.Person
    SET NameStyle = NameStyle,
        FirstName = FirstName,
        EmailPromotion = EmailPromotion;
GO

Связанный контент

• Побитовые операторы (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• UPDATE — триггерные функции (Transact-SQL)