COLUMNS_UPDATED (Transact-SQL)

2025-05-20

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance

Den här funktionen returnerar ett varbinärt bitmönster som anger de infogade eller uppdaterade kolumnerna i en tabell eller vy. Använd COLUMNS_UPDATED var som helst i brödtexten i en Transact-SQL INSERT eller UPDATE utlösare för att testa om utlösaren ska köra vissa åtgärder.

Transact-SQL syntaxkonventioner

Syntax

COLUMNS_UPDATED ( )

Returtyper

varbinary

Anmärkningar

COLUMNS_UPDATED tester för UPDATE eller INSERT åtgärder som utförs på flera kolumner. Om du vill testa för UPDATE eller INSERT försök på en kolumn använder du UPDATE().

COLUMNS_UPDATED returnerar en eller flera byte som sorteras från vänster till höger. Den högra biten av varje byte är den minst betydande biten. Den högra biten av den vänstra byte representerar den första tabellkolumnen i tabellen, nästa bit till vänster representerar den andra kolumnen och så vidare. COLUMNS_UPDATED returnerar flera byte om tabellen där utlösaren skapas innehåller fler än åtta kolumner, där minst betydande byte är längst till vänster. COLUMNS_UPDATED returnerar TRUE för alla kolumner i INSERT åtgärder eftersom kolumnerna antingen har explicita värden eller implicita värden (NULL).

Om du vill testa uppdateringar eller infogningar i specifika kolumner följer du syntaxen med en bitvis operator och en heltalsbitmask för de testade kolumnerna. Anta till exempel att tabellen t1 innehåller kolumner C1, , C2C3, C4och C5. Om du vill kontrollera att kolumnerna , och alla har uppdaterats (med en C2 utlösare i tabellenC3) följer du syntaxen med C4.t1UPDATE& 14 Om du vill testa om endast kolumnen C2 uppdateras anger du & 2. Se Exempel A och Exempel B för faktiska exempel.

Använd COLUMNS_UPDATED var som helst i en Transact-SQL INSERT eller UPDATE utlösare. Om detta körs utanför en utlösare returneras en NULL.

Kolumnen ORDINAL_POSITION i INFORMATION_SCHEMA.COLUMNS vyn är inte kompatibel med bitmönstret för kolumner som returneras av COLUMNS_UPDATED. Om du vill hämta ett bitmönster som är kompatibelt med COLUMNS_UPDATEDrefererar du till ColumnID egenskapen för COLUMNPROPERTY systemfunktionen när du INFORMATION_SCHEMA.COLUMNS kör frågor mot vyn, som du ser i följande exempel.

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

Om en utlösare gäller för en kolumn COLUMNS_UPDATED returneras som true eller 1, även om kolumnvärdet förblir oförändrat. Detta är avsiktligt och utlösaren bör implementera affärslogik som avgör om åtgärden insert/update/delete är tillåten eller inte.

Kolumnuppsättningar

När en kolumnuppsättning definieras i en tabell COLUMNS_UPDATED fungerar funktionen på följande sätt:

När du uttryckligen uppdaterar en medlemskolumn i kolumnuppsättningen anges motsvarande bit för kolumnen till 1, och kolumnuppsättningsbiten är inställd på 1.
När du uttryckligen uppdaterar en kolumnuppsättning är kolumnuppsättningsbiten inställd på 1, och bitarna för alla glesa kolumner i tabellen är inställda på 1.
För infogningsåtgärder är alla bitar inställda på 1.

Eftersom ändringar i en kolumnuppsättning gör att bitarna i alla kolumner i kolumnen återställs till 1ändras oförändrade kolumner i en kolumnuppsättning. Mer information om kolumnuppsättningar finns i Använda kolumnuppsättningar .

Exempel

A. Använd COLUMNS_UPDATED för att testa de första åtta kolumnerna i en tabell

I det här exemplet skapas två tabeller: employeeData och auditEmployeeData. Tabellen employeeData innehåller känslig löneinformation för anställda och personalavdelningens medlemmar kan ändra den. Om personnummer (SSN), årslön eller bankkontonummer för en anställd ändras genereras en granskningspost och infogas i granskningstabellen auditEmployeeData .

Med funktionen COLUMNS_UPDATED() kan vi snabbt testa eventuella ändringar som görs i kolumner som innehåller känslig information om anställda. Det COLUMNS_UPDATED() här sättet fungerar bara när du försöker identifiera ändringar i de första åtta kolumnerna i tabellen.

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

B. Använd COLUMNS_UPDATED för att testa fler än åtta kolumner

Om du vill testa uppdateringar som påverkar andra kolumner än de första åtta tabellkolumnerna SUBSTRING använder du funktionen för att testa rätt bit som returneras av COLUMNS_UPDATED. I det här exemplet testas uppdateringar som påverkar kolumnerna 3, 5och 9 i AdventureWorks2022.Person.Person tabellen.

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

Feedback

Var den här sidan till hjälp?