COLUMNS_UPDATED() и маска

Вот столкнулся c такой задачкой:

Требовалось прикрутить к таблице дату последнего обновления, однако так, чтобы дата последнего обновления изменялась только при изменении 20 из 22 столбцов.


Сразу пришло в голову что-то типо:

CREATE     TRIGGER [my_tbl_upd_last_dt_au] ON [dbo].[my_tbl]
AFTER UPDATE
AS
IF UPDATE(col_1) OR 
    UPDATE(col_2) OR 
    ...
    UPDATE(col_20) OR 
BEGIN
   ....
END

Однако, это не «путь клана», т.к. при добавлении нового столбца прийдется пересоздавать триггер, и к тому же я могу просто забыть это сделать.


Одиним из возможных вариантов был COLUMNS_UPDATED()
Первое что я написал было:

ALTER TRIGGER [money_pr_acc_set_last_upd_dt_au] ON [dbo].[money_pri_ras_acc]
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON
  	
  	DECLARE @col_idx INT
  	DECLARE @mask_col_for_upd INT

  	SET @col_idx = COLUMNPROPERTY(OBJECT_ID('dbo.money_pri_ras_acc'), 'is_closed', 'ColumnID')
  	SET @mask_col_for_upd = 0xFFFFFFFF ^ POWER(2, @col_idx - 1) 
  	
  	SET @col_idx = COLUMNPROPERTY(OBJECT_ID('dbo.money_pri_ras_acc'), 'dt_last_update', 'ColumnID')
  	SET @mask_col_for_upd = @mask_col_for_upd ^ POWER(2, @col_idx - 1)
  	  	
  	IF (COLUMNS_UPDATED() & @mask_col_for_upd) > 0 -- Все столбцы, кроме is_closed, dt_last_update
  	BEGIN
	  	UPDATE mpra 
  		SET dt_last_update = GETDATE()
  		FROM INSERTED ins
  			INNER JOIN dbo.money_pri_ras_acc mpra ON mpra.code = ins.code
  	END
END


Однако, как оказалось, этот код не работает!

Читаю:
Функция COLUMNS_UPDATED возвращает один или более байтов, которые упорядочены слева направо по принципу: крайний правый бит — наименее значащий бит в байте. Крайний правый бит крайнего левого байта представляет первый столбец в таблице, следующий бит слева представляет второй столбец и так далее.

Вроде все правильно, но где же причина? Оказывается, при COLUMNS_UPDATED() & @mask_col_for_upd правило «младьшее из регистра по меньшему адресу» не работает.

Вариант CAST(COLUMNS_UPDATED() AS INT) & @mask_col_for_upd не работает тоже.(Я могу так сделать, с учетом что количество столбцов в моей таблице < 32)

В справке еще рекомендуют SUBSTRING, и сразвнивать байты, однако я хочу в INT одним махом!
Ничего не поделаешь, прийдется писать функцию, которая занесет данные из младших адресов в младший разряд INT:
ALTER FUNCTION [dbo].[GetColumnsUpdatedInt32](@col_upd as VARBINARY(4))
RETURNS INT
BEGIN
  DECLARE @col8_1 INT
  DECLARE @col8_2 INT
  DECLARE @col8_3 INT
  DECLARE @col8_4 INT
  DECLARE @col_upd_int32 INT
  
  SET @col8_1 = SUBSTRING(@col_upd, 1, 1)
  SET @col8_2 = SUBSTRING(@col_upd, 2, 1)
  SET @col8_3 = SUBSTRING(@col_upd, 3, 1)
  SET @col8_4 = SUBSTRING(@col_upd, 4, 1)
  SET @col_upd_int32 = @col8_1 + 256 * @col8_2 + 256 * 256 * @col8_3 +  256 * 256 * 256 * @col8_4
    
  RETURN @col_upd_int32	
END


Теперь поменяем код триггера:
ALTER TRIGGER [dbo].[money_pr_acc_set_last_upd_dt_au] ON [dbo].[money_pri_ras_acc]
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @col_upd_int INT
	DECLARE @col_idx INT
	DECLARE @mask_col_for_upd INT
	 	
	SET @col_upd_int = dbo.GetColumnsUpdatedInt32(COLUMNS_UPDATED())

	SET @col_idx = COLUMNPROPERTY(OBJECT_ID('dbo.money_pri_ras_acc'), 'is_closed', 'ColumnID')
	SET @mask_col_for_upd = 0xFFFFFFFF ^ POWER(2, @col_idx - 1) 

	SET @col_idx = COLUMNPROPERTY(OBJECT_ID('dbo.money_pri_ras_acc'), 'dt_last_update', 'ColumnID')
	SET @mask_col_for_upd = @mask_col_for_upd ^ POWER(2, @col_idx - 1)
	  	
	IF (@col_upd_int & @mask_col_for_upd) > 0 -- Все столбцы, кроме is_closed, dt_last_update
	BEGIN
  		UPDATE mpra 
		SET dt_last_update = GETDATE()
		FROM INSERTED ins
			INNER JOIN dbo.money_pri_ras_acc mpra ON mpra.code = ins.code
	END
END


Работает!
Tags:
COLUMNS_UPDATED(), Microsoft SQL Server

You can't comment this post because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author's username will be hidden by an alias.