COLUMNS_UPDATED (Transact-SQL)

2025-05-20

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Deze functie retourneert een varbinaire bitpatroon dat de ingevoegde of bijgewerkte kolommen van een tabel of weergave aangeeft. Gebruik COLUMNS_UPDATED ergens in de hoofdtekst van een Transact-SQL INSERT of UPDATE trigger om te testen of de trigger bepaalde acties moet uitvoeren.

Transact-SQL syntaxis-conventies

Syntaxis

COLUMNS_UPDATED ( )

Retourtypen

varbinair

Opmerkingen

COLUMNS_UPDATED tests voor UPDATE of INSERT acties die worden uitgevoerd op meerdere kolommen. Gebruik UPDATEom te testen op INSERT of pogingen op één kolom.

COLUMNS_UPDATED retourneert een of meer bytes die van links naar rechts zijn gerangschikt. De meest rechtse bit van elke byte is de minst significante bit. De meest rechtse bit van de meest linkse byte vertegenwoordigt de eerste tabelkolom in de tabel, de volgende bit links vertegenwoordigt de tweede kolom, enzovoort. COLUMNS_UPDATED retourneert meerdere bytes als de tabel waarop de trigger wordt gemaakt meer dan acht kolommen bevat, waarbij de minst significante byte het meest links is. COLUMNS_UPDATED retourneert TRUE voor alle kolommen in INSERT acties omdat de kolommen expliciete waarden of impliciete waarden (NULL) hebben ingevoegd.

Als u wilt testen op updates of invoegingen in specifieke kolommen, volgt u de syntaxis met een bitsgewijze operator en een geheel getal bitmasker van de geteste kolommen. Stel dat de tabel t1 kolommenC1, C2, , C3en C4C5. Als u wilt controleren of kolommen en C2C3alle kolommen C4zijn bijgewerkt (met een tabel t1 met een UPDATE trigger), volgt u de syntaxis met & 14. Als u wilt testen of alleen de kolom C2 is bijgewerkt, geeft u & 2op. Zie Voorbeeld A en Voorbeeld B voor werkelijke voorbeelden.

Overal COLUMNS_UPDATED in een Transact-SQL INSERT of UPDATE trigger gebruiken. Als dit buiten een trigger wordt uitgevoerd, wordt een NULL geretourneerd.

De ORDINAL_POSITION kolom van de INFORMATION_SCHEMA.COLUMNS weergave is niet compatibel met het bitpatroon van kolommen dat wordt geretourneerd door COLUMNS_UPDATED. Als u een bitpatroon wilt verkrijgen dat compatibel is met COLUMNS_UPDATED, verwijst u naar de ColumnID eigenschap van de COLUMNPROPERTY systeemfunctie bij het uitvoeren van een query op de INFORMATION_SCHEMA.COLUMNS weergave, zoals wordt weergegeven in het volgende voorbeeld.

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

Als een trigger van toepassing is op een kolom, wordt de COLUMNS_UPDATED waarde geretourneerd als true of 1, zelfs als de kolomwaarde ongewijzigd blijft. Dit is standaard en de trigger moet bedrijfslogica implementeren die bepaalt of de invoeg-/update-/verwijderbewerking is toegestaan of niet.

Kolomsets

Wanneer een kolomset is gedefinieerd in een tabel, gedraagt de COLUMNS_UPDATED functie zich op de volgende manieren:

Wanneer u expliciet een lidkolom van de kolomset bijwerkt, wordt de bijbehorende bit voor die kolom ingesteld op 1en wordt de bit van de kolomset ingesteld op 1.
Wanneer u een kolomset expliciet bijwerkt, wordt de bit van de kolomset ingesteld op 1en worden de bits voor alle parseringskolommen in die tabel ingesteld op 1.
Voor invoegbewerkingen zijn alle bits ingesteld op 1.

Omdat wijzigingen in een kolomset ertoe leiden dat de bits van alle kolommen in de kolomset opnieuw 1worden ingesteld, worden ongewijzigde kolommen in een kolomset gewijzigd. Zie Kolomsets gebruiken voor meer informatie over kolomsets.

Voorbeelden

Eén. Gebruik COLUMNS_UPDATED om de eerste acht kolommen van een tabel te testen

In dit voorbeeld worden twee tabellen gemaakt: employeeData en auditEmployeeData. De employeeData tabel bevat gevoelige salarisgegevens van werknemers en leden van de afdeling Personeelszaken kunnen deze wijzigen. Als het burgerservicenummer (SSN), het jaarlijkse salaris of bankrekeningnummer voor een werknemer verandert, wordt er een auditrecord gegenereerd en ingevoegd in de auditEmployeeData audittabel.

Met de COLUMNS_UPDATED() functie kunnen we snel testen op wijzigingen die zijn aangebracht in kolommen met gevoelige werknemersgegevens. Op COLUMNS_UPDATED() deze manier werkt alleen wanneer u wijzigingen in de eerste acht kolommen in de tabel probeert te detecteren.

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. Gebruik COLUMNS_UPDATED om meer dan acht kolommen te testen

Als u wilt testen op updates die van invloed zijn op andere kolommen dan de eerste acht tabelkolommen, gebruikt u de SUBSTRING functie om de juiste bit te testen die wordt geretourneerd door COLUMNS_UPDATED. In dit voorbeeld wordt getest op updates die van invloed zijn op kolommen 35en 9 in de AdventureWorks2022.Person.Person tabel.

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

Delen via