COLUMNS_UPDATED (Transact-SQL)

2025-05-20

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

Esta función devuelve un patrón de bits varbinary que indica las columnas de una tabla o vista que se insertaron o se actualizaron. Use COLUMNS_UPDATED cualquier lugar dentro del cuerpo de un Transact-SQL INSERT o UPDATE desencadenador para probar si el desencadenador debe ejecutar determinadas acciones.

Convenciones de sintaxis de Transact-SQL

Sintaxis

COLUMNS_UPDATED ( )

Tipos de valores devueltos

varbinary

Observaciones

COLUMNS_UPDATEDcomprueba las acciones o UPDATE realizadas INSERT en varias columnas. Para probar UPDATE los intentos o INSERT en una columna, use UPDATE().

COLUMNS_UPDATED devuelve uno o más bytes que se ordenan de izquierda a derecha. El bit situado más a la derecha de cada byte es el menos significativo. El primer bit por la derecha del byte situado más a la izquierda representa la primera columna de la tabla, el siguiente bit a la izquierda representa la segunda columna, y así sucesivamente. COLUMNS_UPDATED devuelve varios bytes si la tabla en que se ha creado el desencadenador contiene más de ocho columnas, siendo el menos significativo el primero por la izquierda. COLUMNS_UPDATED devuelve TRUE para todas las columnas de INSERT acciones porque las columnas tienen valores explícitos o valores implícitos (NULL) insertados.

Para probar las actualizaciones o inserciones en columnas específicas, siga la sintaxis con un operador bit a bit y una máscara de bits de enteros de las columnas probadas. Por ejemplo, supongamos que la tabla t1 contiene columnas C1, C2, C3, C4y C5. Para comprobar que las columnas , y todas se han actualizado correctamente (con una tabla C2 que tiene un C3 desencadenador), siga la sintaxis con C4.t1UPDATE& 14 Para probar si solo se actualiza la columna C2 , especifique & 2. Vea Ejemplo A y Ejemplo B para obtener ejemplos reales.

Use COLUMNS_UPDATED cualquier lugar dentro de una Transact-SQL INSERT o UPDATE desencadenador. Si se ejecuta fuera de un desencadenador, se devuelve un valor NULL.

La ORDINAL_POSITION columna de la INFORMATION_SCHEMA.COLUMNS vista no es compatible con el patrón de bits de las columnas devueltas por COLUMNS_UPDATED. Para obtener un patrón de bits compatible con COLUMNS_UPDATED, haga referencia a la propiedad ColumnID de la función del sistema COLUMNPROPERTY cuando realice una consulta de la vista INFORMATION_SCHEMA.COLUMNS, como se muestra en el ejemplo siguiente.

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 desencadenador se aplica a una columna, COLUMNS_UPDATED se devolverá como true o 1, incluso si el valor de columna permanece sin cambios. Esto es así por diseño y el desencadenador debe implementar la lógica de negocios que determina si la operación de inserción, actualización o eliminación está permitida o no.

Conjuntos de columnas

Cuando un conjunto de columnas se define en una tabla, la función COLUMNS_UPDATED se comporta de las maneras siguientes:

Al actualizar explícitamente una columna miembro del conjunto de columnas, el bit correspondiente para esa columna se establece 1en y el bit del conjunto de columnas se establece en 1.
Al actualizar explícitamente un conjunto de columnas, el bit del conjunto de columnas se establece 1en y los bits de todas las columnas dispersas de esa tabla se establecen 1en .
Para las operaciones de inserción, todos los bits se establecen en 1.

Dado que los cambios en un conjunto de columnas hacen que los bits de todas las columnas del conjunto de columnas se restablezcan 1en , las columnas sin cambios de un conjunto de columnas aparecerán modificadas. Vea Usar conjuntos de columnas para obtener más información sobre los conjuntos de columnas.

Ejemplos

Un. Use COLUMNS_UPDATED para probar las ocho primeras columnas de una tabla

En este ejemplo se crean dos tablas: employeeData y auditEmployeeData. La tabla employeeData contiene información confidencial de los sueldos de los empleados y la pueden modificar los miembros del departamento de recursos humanos. Si se cambia el número de seguridad social, el sueldo anual o el número de cuenta bancaria de un empleado, se genera un registro de auditoría y se inserta en la tabla de auditoría auditEmployeeData.

Con la función COLUMNS_UPDATED(), se pueden probar rápidamente los cambios realizados en las columnas que contienen información confidencial de los empleados. El uso de COLUMNS_UPDATED() de esta manera solo funciona al intentar detectar cambios en las primeras ocho columnas de la tabla.

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. Uso de COLUMNS_UPDATED para probar más de ocho columnas

Para comprobar las actualizaciones que afectan a otras columnas que no sean las ocho primeras de la tabla, use la función SUBSTRING para comprobar si COLUMNS_UPDATED devuelve el bit correcto. En este ejemplo se comprueban las actualizaciones que afectan a las columnas 3, 5 y 9 de la tabla 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

Compartir a través de