How to use a variable in the Transact SQL update function within a trigger?

Question

How to use a variable in the Transact SQL update function within a trigger?

Navigation

#1 by (1 votes)

0

What I want to achieve is to go through each column of a table and check if it has a change with the function update (colum), but apparently it takes the variable where I keep the column as a valid argument.

DECLARE @field INT ,
       @maxfield INT ,
       @fieldname VARCHAR(128) ,
       @TableName VARCHAR(128) ,
       @SelectUpdate Varchar(100) = 'Select '

SELECT         @field = 0, 
       @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION > @field

               SELECT @fieldname = COLUMN_NAME
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field

        IF (UPDATE(@fieldname))
        BEGIN
            if (@field = 1)
            select @SelectUpdate = @SelectUpdate + @fieldname
            else
            select @SelectUpdate = @SelectUpdate +', ' +@fieldname
        END
END

sql sql-server trigger

asked by Ricardo Hernández Fernández 20.03.2018 в 18:07

source

1 answer

Visual Studio 2017 hangs Problem with Jquery and the structure switch case

score 1 · Accepted Answer

Unfortunately you can not use a variable to indicate the column name in the UPDATE clause. What you could do is use COLUMNS_UPDATED() along with syscolumns to get the names of the updated columns. The following is a simple proof of concept:

-- Tabla de ejemplo
CREATE TABLE Prueba (
    COL1    INT,
    COL2    INT,
    COL3    INT,
    COL4    INT
)

-- Tabla para registrar que campos fueron actualizados
CREATE TABLE PruebaLog (
    Camposactualizados VARCHAR(MAX)
)

GO

-- Trigger para el insert y update
CREATE TRIGGER Trigger_Prueba ON Prueba
FOR INSERT, UPDATE
AS

    SET NOCOUNT ON

    DECLARE @Columns_Updated VARCHAR(MAX)

    SELECT  @Columns_Updated = ISNULL(@Columns_Updated + ', ', '') + name
        FROM    syscolumns
        WHERE   id = OBJECT_ID('Prueba')
        AND     CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1)  > 0

    INSERT INTO PruebaLog(Camposactualizados)
        VALUES(@Columns_Updated)
GO

-- El insert actualiza todas las columnas
INSERT INTO Prueba (COL1, COL2) VALUES (1,2)
SELECT  *
    FROM PruebaLog

/* Resultado:
Camposactualizados 
----------------------
COL1, COL2, COL3, COL4
*/

-- El update actualiza solo ciertas columnas
UPDATE Prueba SET COL2=3
SELECT  *
    FROM PruebaLog

/* Resultado:
Camposactualizados 
----------------------
COL1, COL2, COL3, COL4
COL2
*/

DROP TRIGGER Trigger_Prueba
DROP TABLE Prueba
DROP TABLE PruebaLog
GO

COLUMNS_UPDATED() returns varbinary with a bit pattern that indicates the update status of each column in an operation of INSERT or UPDATE of a certain trigger .