COLUMNS_UPDATED (Transact-SQL)

2025-05-20

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Questa funzione restituisce uno schema di bit varbinary che indica le colonne inserite o aggiornate in una tabella o vista. Usare COLUMNS_UPDATED un punto qualsiasi all'interno del corpo di un Transact-SQL INSERT o UPDATE di un trigger per verificare se il trigger deve eseguire determinate azioni.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

COLUMNS_UPDATED ( )

Tipi restituiti

varbinary

Osservazioni:

COLUMNS_UPDATED verifica le UPDATE azioni o INSERT eseguite su più colonne. Per verificare la disponibilità o i tentativi in una colonna, usare UPDATE().To test for UPDATE or INSERT attempts on one column, use UPDATE().

COLUMNS_UPDATED restituisce uno o più byte ordinati da sinistra a destra. Il bit più a destra di ogni byte è il bit meno significativo. Il bit più a destra del byte più a sinistra rappresenta la prima colonna della tabella, il bit successivo a sinistra rappresenta la seconda colonna e così via. COLUMNS_UPDATED restituisce più byte se la tabella in cui viene creato il trigger include più di otto colonne. Il byte più a sinistra è il meno significativo. COLUMNS_UPDATED restituisce TRUE per tutte le colonne nelle INSERT azioni perché le colonne hanno valori espliciti o valori impliciti (NULL) inseriti.

Per controllare l'esecuzione di operazioni di aggiornamento o inserimento in colonne specifiche, aggiungere alla sintassi un operatore bit per bit e una maschera di bit di valori interi delle colonne sottoposte a controllo. Si supponga, ad esempio, che la tabella t1 contenga colonne C1, , C2C3, C4e C5. Per verificare che le colonne , e C2 tutte aggiornate correttamente (con la tabella C3 con un C4 trigger), seguire la sintassi con t1. UPDATE& 14 Per verificare se viene aggiornata solo la colonna C2 , specificare & 2. Vedere l'esempio A e l'esempio B per esempi effettivi.

Usare COLUMNS_UPDATED ovunque all'interno di un Transact-SQL INSERT o UPDATE di un trigger. Se questa operazione viene eseguita all'esterno di un trigger, viene restituito un valore NULL.

La ORDINAL_POSITION colonna della INFORMATION_SCHEMA.COLUMNS vista non è compatibile con il modello di bit delle colonne restituite da COLUMNS_UPDATED. Per ottenere uno schema di bit compatibile con COLUMNS_UPDATED, fare riferimento alla proprietà ColumnID della funzione di sistema COLUMNPROPERTY quando si esegue una query sulla vista INFORMATION_SCHEMA.COLUMNS, come illustrato nell'esempio seguente.

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

Se si applica un trigger a una colonna, il valore COLUMNS_UPDATED verrà restituito come true o 1, anche se il valore della colonna rimane invariato. Si tratta di un comportamento predefinito e il trigger deve implementare la logica di business che determina se l'operazione di inserimento/aggiornamento/eliminazione è consentita o meno.

Set di colonne

Quando un set di colonne è definito in una tabella, il comportamento della funzione COLUMNS_UPDATED è il seguente:

Quando si aggiorna in modo esplicito una colonna membro del set di colonne, il bit corrispondente per tale colonna è impostato su 1e il bit del set di colonne è impostato su 1.
Quando si aggiorna in modo esplicito un set di colonne, il bit del set di colonne viene impostato su 1e i bit per tutte le colonne di tipo sparse in tale tabella vengono impostati su 1.
Per le operazioni di inserimento, tutti i bit sono impostati su 1.

Poiché le modifiche apportate a un set di colonne causano la modifica dei bit di tutte le colonne del set di colonne su 1, le colonne non modificate in un set di colonne verranno modificate. Per altre informazioni sui set di colonne, vedere Usare set di colonne.

Esempi

R. Usare COLUMNS_UPDATED per testare le prime otto colonne di una tabella

Nell'esempio seguente vengono create due tabelle: employeeData e auditEmployeeData. La tabella employeeData include informazioni riservate sulle retribuzioni dei dipendenti e può essere modificata dai membri dell'ufficio del personale. Se il numero di codice fiscale, lo stipendio annuale o il numero di conto corrente bancario relativi a un dipendente cambiano, nella tabella di controllo auditEmployeeData viene generato e inserito un record di controllo.

Con la funzione COLUMNS_UPDATED(), è possibile verificare rapidamente eventuali modifiche apportate a colonne contenenti informazioni riservate sui dipendenti. Se si usa COLUMNS_UPDATED() ciò funziona solo quando si tenta di rilevare le modifiche alle prime otto colonne della tabella.

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. Usare COLUMNS_UPDATED per testare più di otto colonne

Per controllare gli aggiornamenti eseguiti su colonne diverse dalle prime otto colonne di una tabella, usare la funzione SUBSTRING per controllare il bit corretto restituito da COLUMNS_UPDATED. Nell'esempio seguente vengono controllati gli aggiornamenti relativi alle colonne 3, 5 e 9 della tabella 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

Condividi tramite