COLUMNS_UPDATED (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Cette fonction retourne un modèle binaire de type varbinary qui indique les colonnes insérées ou mises à jour d’une table ou d’une vue. Utilisez COLUMNS_UPDATED n’importe où dans le corps d’une Transact-SQL INSERT ou UPDATE d’un déclencheur pour tester si le déclencheur doit exécuter certaines actions.

Conventions de la syntaxe Transact-SQL

Syntaxe

COLUMNS_UPDATED ( )

Types de retour

varbinary

Notes

COLUMNS_UPDATED tests pour UPDATE ou INSERT actions effectuées sur plusieurs colonnes. Pour tester UPDATE ou INSERT essayer sur une colonne, utilisez UPDATE().

COLUMNS_UPDATED retourne un ou plusieurs octets classés de gauche à droite. Le bit le plus à droite dans chaque octet est le bit le moins significatif. Le bit le plus à droite de l’octet le plus à gauche représente la première colonne de la table, le suivant à gauche représente la deuxième colonne, etc. COLUMNS_UPDATED retourne plusieurs octets si la table sur laquelle le déclencheur est créé contient plus de huit colonnes, l’octet le moins significatif étant celui le plus à gauche. COLUMNS_UPDATED renvoie TRUE toutes les colonnes dans INSERT les actions, car les colonnes ont des valeurs explicites ou des valeurs implicites (NULL) insérées.

Pour tester l’existence de mises à jour ou d’insertions dans des colonnes spécifiques, utilisez dans la syntaxe un opérateur au niveau du bit et un masque de bits d’entier des colonnes testées. Par exemple, supposons que la table t1 contient des colonnesC1, , C2C3, C4et C5. Pour vérifier que les colonnes C2, C3et C4 toutes correctement mises à jour (avec une table t1 ayant un UPDATE déclencheur), suivez la syntaxe avec & 14. Pour tester si seule la colonne C2 est mise à jour, spécifiez & 2. Consultez Exemple A et Exemple B pour obtenir des exemples réels.

Utilisez COLUMNS_UPDATED n’importe où à l’intérieur d’un Transact-SQL INSERT ou UPDATE d’un déclencheur. S’il s’exécute en dehors d’un déclencheur, une valeur NULL est retournée.

La ORDINAL_POSITION colonne de la INFORMATION_SCHEMA.COLUMNS vue n’est pas compatible avec le modèle de bits des colonnes retournées par COLUMNS_UPDATED. Pour obtenir un modèle binaire compatible avec COLUMNS_UPDATED, référencez la propriété ColumnID de la fonction système COLUMNPROPERTY quand vous interrogez la vue INFORMATION_SCHEMA.COLUMNS, comme illustré dans l’exemple suivant :

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

Si un déclencheur s’applique à une colonne, COLUMNS_UPDATED retourne true ou 1, même si la valeur de colonne reste inchangée. Cela est intentionnel. Le déclencheur doit implémenter une logique métier qui détermine si l’opération d’insertion/de mise à jour/de suppression est autorisée ou non.

Jeux de colonnes

Quand un jeu de colonnes est défini sur une table, la fonction COLUMNS_UPDATED se comporte comme suit :

• Lors de la mise à jour explicite d’une colonne membre du jeu de colonnes, le bit correspondant pour cette colonne est défini 1sur , et le bit défini sur le jeu de colonnes est défini 1sur .

• Lors de la mise à jour explicite d’un jeu de colonnes, le bit du jeu de colonnes est défini 1sur , et les bits pour toutes les colonnes éparses de cette table sont définis 1sur .

• Pour les opérations d’insertion, tous les bits sont définis sur 1.

  Étant donné que les modifications apportées à un jeu de colonnes entraînent la modification 1des bits de toutes les colonnes de l’ensemble de colonnes dans le jeu de colonnes. Pour plus d’informations sur les jeux de colonnes, consultez Utiliser des jeux de colonnes.

Exemples

R. Utilisez COLUMNS_UPDATED pour tester les huit premières colonnes d’une table

Cet exemple crée deux tables, employeeData et auditEmployeeData. La table employeeData contient des informations sensibles relatives aux salaires des employés et les membres du service des ressources humaines peuvent la modifier. Si le numéro de sécurité sociale, le salaire annuel ou le numéro de compte bancaire d’un employé change, un enregistrement d’audit est généré et inséré dans la table d’audit auditEmployeeData.

Avec la fonction COLUMNS_UPDATED(), nous pouvons tester rapidement si des modifications ont été apportées aux colonnes contenant des informations sensibles relatives aux employés. Cette utilisation de COLUMNS_UPDATED() fonctionne seulement pour détecter des modifications apportées aux huit premières colonnes de la table.

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. Utiliser COLUMNS_UPDATED pour tester plus de huit colonnes

Pour tester l’existence de mises à jour affectant des colonnes autres que les huit premières colonnes d’une table, utilisez la fonction SUBSTRING afin de tester le bit adéquat retourné par COLUMNS_UPDATED. Cet exemple teste l’existence de mises à jour affectant les colonnes 3, 5 et 9 de la table 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

Contenu connexe

• Opérateurs au niveau du bit (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• UPDATE - Fonctions de déclencheur (Transact-SQL)