COLUMNS_UPDATED (Transact-SQL)

Devuelve un patrón de bits varbinary que indica las columnas de una tabla o vista que se insertaron o se actualizaron. COLUMNS_UPDATED se utiliza en cualquier parte del cuerpo de un desencadenador INSERT o UPDATE de Transact-SQL para comprobar si el desencadenador debe ejecutar determinadas acciones.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL


COLUMNS_UPDATED ( ) 

COLUMNS_UPDATED comprueba las acciones UPDATE o INSERT realizadas en varias columnas. Para comprobar los intentos UPDATE o INSERT realizados en una columna, utilice UPDATE().

COLUMNS_UPDATED devuelve uno o varios bytes ordenados de izquierda a derecha, donde el primer bit por la derecha es el menos importante de cada byte. El primer bit por la derecha del byte 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 8 columnas, siendo el menos significativo el primero por la izquierda. COLUMNS_UPDATED devuelve el valor TRUE en todas las columnas de las acciones INSERT porque en las columnas se insertaron valores explícitos o implícitos (NULL).

Para comprobar las actualizaciones o inserciones de columnas específicas, siga la sintaxis con un operador bit a bit y una máscara de bits de enteros de las columnas que se están comprobando. Por ejemplo, la tabla t1 contiene las columnas C1, C2, C3, C4 y C5. Para comprobar que todas las columnas C2, C3 y C4 están actualizadas (donde la tabla t1 tiene un desencadenador UPDATE), siga la sintaxis con & 14. Para comprobar si solo se ha actualizado la columna C2, especifique & 2.

COLUMNS_UPDATED puede utilizarse en cualquier parte dentro de un desencadenador INSERT o UPDATE de Transact-SQL.

Nota de advertenciaAdvertencia

En SQL Server 2008, la columna ORDINAL_POSITION de la vista INFORMATION_SCHEMA.COLUMNS 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 siguiente ejemplo.

SELECT TABLE_NAME, COLUMN_NAME,
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks2008R2.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';

Conjuntos de columnas

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

  • Cuando una columna que es miembro del conjunto de columnas se actualiza de forma explícita, el bit correspondiente para dicha columna se establece en 1, y el bit para el conjunto de columnas se establece en 1.

  • Cuando un conjunto de columnas se actualiza de forma explícita, el bit para dicho conjunto de columnas se establece en 1, y los bits para todas las columnas dispersas de la tabla se establecen en 1.

  • En 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 establezcan en 1, parecerá que se han modificado las columnas de un conjunto cuyas columnas no se cambiaron. Para obtener más información acerca de los conjuntos de columnas, vea Utilizar conjuntos de columnas.

A. Usar COLUMNS_UPDATED para comprobar las primeras ocho columnas de una tabla.

En el siguiente ejemplo se crean dos tablas: employeeData y auditEmployeeData. La tabla employeeData contiene información confidencial de los sueldos de los empleados y puede ser modificada por 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(), las comprobaciones de los cambios en las columnas que contienen información confidencial sobre los empleados se pueden realizar rápidamente. COLUMNS_UPDATED() solo se puede utilizar de esta manera para intentar detectar modificaciones en las primeras ocho columnas de la tabla.

USE AdventureWorks2008R2;
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 employeeData (
   emp_id int NOT NULL,
   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 auditEmployeeData (
   audit_log_id uniqueidentifier DEFAULT NEWID(),
   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 updEmployeeData 
ON 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. 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 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 del

-- Audit NEW record.
      INSERT INTO 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 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 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 employeeData
   SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
   WHERE emp_id = 101;
GO
SELECT * FROM auditEmployeeData;
GO

B. Utilizar COLUMNS_UPDATED para comprobar más de ocho columnas

Si tiene que comprobar actualizaciones que afectan a otras columnas que no sean las ocho primeras de una tabla, utilice la función SUBSTRING para comprobar si COLUMNS_UPDATED devuelve el bit correcto. En los siguientes ejemplos se comprueban las actualizaciones que afectan a las columnas 3, 5 y 9 de la tabla AdventureWorks2008R2.Person.Person .

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'uContact2', N'TR') IS NOT NULL
    DROP TRIGGER Person.tr1;
GO
CREATE TRIGGER 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 JobTitle=JobTitle,
      MiddleName=MiddleName,
      EmailPromotion=EmailPromotion;
GO

Adiciones de comunidad

AGREGAR
Mostrar: