How to count the columns that were updated in a trigger?

Question

How to count the columns that were updated in a trigger?

Navigation

#1 by (1 votes)

1

I have the following table called Movies :

(id int, nombre varchar(50), genero varchar (50), clasificacion char(1), año int)

I have this trigger to validate that only one field can be updated at a time in that table.

Would there be any way to optimize this code so as not to use as many if?
Suppose that tomorrow I have a table of 40 columns.

create trigger tr_peliculasactulizacion on peliculas for update
as 
begin
    declare @conta int=0
    if update(id)
        select @conta+=1
    if update (nombre)
        select @conta+=1
    if update (genero)
        select @conta+=1
    if update(clasificacion)
        select @conta+=1
    if update(año)
        select @conta+=1
    if @conta>1
    begin
        rollback tran
        raiserror ('solo se puede actualizar un campo a la vez', 16, 1);
    end 
end

sql sql-server trigger

asked by Eduardo Cortez 22.11.2018 в 06:06

source

1 answer

php decode json received via _POST Error when playing shared sound with shared preferences

score 1 · Answer 1

If you have SQL Server 2008 or higher, can you use the COLUMNS_UPDATED() which returns a VARBINARY that is not more than a bit mask, where each bit in 1 represents in order the column that has been updated, that is to say 1010 represents that column 1 and 2 of a four-column table have been updated. To count how many bits in on we have (the number of columns updated) we can do something like this:

create trigger tr_peliculasactulizacion on peliculas for update
as 
begin

    DECLARE @Binary VARBINARY = COLUMNS_UPDATED()
    DECLARE @C  INT  = 0

    WHILE @Binary > 0 
      SELECT @C = @C + (@Binary % 2),  
        @Binary = @Binary /2

    IF @C > 1
    begin
            rollback tran
            raiserror ('solo se puede actualizar un campo a la vez', 16, 1);
    end 
end