Dúvida - Validar se campo foi alterado na TRIGGER dinamicamente.

SQL Server

SQL

11/05/2020

Olá, senhores, boa tarde!

Tenho uma tabela (Tabela_B) que listo os campos que devem ser considerados no momento de realizar alteração em uma outra tabela (Tabela_A):

Exemplo: Tabela A (Id, Nome, Descrição, telefone, email)
Tabela B (Nome, Descrição)

Ou seja, apesar da tabela A, onde preciso criar a trigger ter todos os campos acima, só vou executar uma determinada rotina, se forem alterados os valores dos campos presentes na tabela B.

Até aí, tudo bem, com essa quantidade de campos, dá pra fazer a verificação campo a campo.

Porém, são vários campos, o que torna a validação manual inviável, neste caso como poderia fazer isso dinâmico, obter os campos que foram alterados?

Exemplo, estou usando SQL Server:


CREATE TRIGGER TRG_Tabela_A_CONTROLE
ON Tabela_A
AFTER DELETE,UPDATE
AS
BEGIN

DECLARE @FDC_CAMPO VARCHAR(80), @LINHA INT, @ETA_COD VARCHAR(10)



IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM Deleted) -- UPDATE
BEGIN

DECLARE CS_TESTE CURSOR FOR
Select
FDC_CAMPO, Row_Number() Over (Order By FDT_TABELA) LINHA, ETA_COD
From
Tabela_B

OPEN CS_TESTE

FETCH NEXT FROM CS_TESTE INTO @FDC_CAMPO, @LINHA, @ETA_COD

WHILE @@FETCH_STATUS = 0
BEGIN

IF UPDATE(@FDC_CAMPO) /*AQUI SÓ ACEITA O CAMPO DA TABELA, NÃO ACEITA VARIÁVEL*/
BEGIN

/*AÇÃO A SER REALIZADA*/
END

FETCH NEXT FROM CS_TESTE INTO @FDC_CAMPO, @LINHA, @ETA_COD
END

CLOSE CS_TESTE
DEALLOCATE CS_TESTE

END

END
Jose Carvalho

Jose Carvalho

Curtidas 0

Respostas

Emerson Nascimento

Emerson Nascimento

11/05/2020

tente isto:
CREATE OR ALTER TRIGGER TRG_Tabela_A_CONTROLE
ON Tabela_A
AFTER DELETE,UPDATE
AS
BEGIN
	DECLARE @FDC_CAMPO VARCHAR(80);
	DECLARE @ID_COLUNA INT;
	DECLARE @PW_COLUNA INT;
	DECLARE @BYTE_COLUNA INT;

	IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM Deleted) -- UPDATE
	BEGIN

		DECLARE CS_TESTE CURSOR
		FOR	Select
				LTRIM(RTRIM(FDC_CAMPO))
			From
				Tabela_B
			Where
				FDC_TABELA = 'Tabela_A';

		OPEN CS_TESTE;

		FETCH NEXT FROM CS_TESTE
		INTO @FDC_CAMPO;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT
				@ID_COLUNA = COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
				COLUMN_NAME, 'ColumnID')
			FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME = 'Tabela_A' AND COLUMN_NAME = @FDC_CAMPO;

			-- é necessário encontar o byte correto para a função COLUMNS_UPDATED()
			SET @BYTE_COLUNA = (@ID_COLUNA / 8);
			SET @ID_COLUNA = (@ID_COLUNA - (8 * @BYTE_COLUNA));
			SET @PW_COLUNA = POWER(2, (@ID_COLUNA-1));

			IF ((SUBSTRING(COLUMNS_UPDATED(), (@BYTE_COLUNA+1), 1) & @PW_COLUNA) = @PW_COLUNA)
			BEGIN
				/*AÇÃO A SER REALIZADA*/
				PRINT 'campo ' + @FDC_CAMPO + ' alterado.'
			END

			FETCH NEXT FROM CS_TESTE
			INTO @FDC_CAMPO;
		END

		CLOSE CS_TESTE;
		DEALLOCATE CS_TESTE;

	END

END
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

11/05/2020

talvez assim seja melhor:
CREATE OR ALTER TRIGGER TRG_Tabela_A_CONTROLE
ON Tabela_A
AFTER DELETE,UPDATE
AS
BEGIN
	DECLARE @FDC_CAMPO VARCHAR(80);
	DECLARE @ID_COLUNA INT;
	DECLARE @PW_COLUNA INT;
	DECLARE @BYTE_COLUNA INT;

	IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM Deleted) -- UPDATE
	BEGIN
		DECLARE CS_TESTE CURSOR
		FOR	SELECT
				RTRIM(LTRIM(COLUMN_NAME)),
				COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),  
				COLUMN_NAME, 'ColumnID')
			FROM INFORMATION_SCHEMA.COLUMNS  
			WHERE TABLE_NAME = 'Tabela_A'
				AND COLUMN_NAME IN (SELECT LTRIM(RTRIM(FDC_CAMPO)) FROM Tabela_B WHERE FDC_TABELA = 'Tabela_A')

		OPEN CS_TESTE;

		FETCH NEXT FROM CS_TESTE
		INTO @FDC_CAMPO, @ID_COLUNA;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- é necessário encontar o byte correto para a função COLUMNS_UPDATED()
			SET @BYTE_COLUNA = (@ID_COLUNA / 8);
			SET @ID_COLUNA = (@ID_COLUNA - (8 * @BYTE_COLUNA));
			SET @PW_COLUNA = POWER(2, (@ID_COLUNA-1));

			IF ((SUBSTRING(COLUMNS_UPDATED(), (@BYTE_COLUNA+1), 1) & @PW_COLUNA) = @PW_COLUNA)
			BEGIN
				/*AÇÃO A SER REALIZADA*/
				PRINT 'campo ' + @FDC_CAMPO + ' alterado.'
			END

			FETCH NEXT FROM CS_TESTE
			INTO @FDC_CAMPO, @ID_COLUNA;
		END

		CLOSE CS_TESTE;
		DEALLOCATE CS_TESTE;

	END

END

GOSTEI 0
Jose Carvalho

Jose Carvalho

11/05/2020

Emerson, boa tarde!

Agradeço pelo retorno, mas neste caso mesmo não tendo alterado o valor do campo, ou sem ter alterado um campo que está na tabela B, ele executa a ação a ser realizada.

Exemplo: ao executar UPDATE TABELA_A SET TELEFONE = ''7199999999'' WHERE CHAVE = 1

ele executa a ação "PRINT ''campo '' + @FDC_CAMPO + '' alterado.''", mesmo não tendo este campo na tabela_B e o campo já está esse no banco.

talvez assim seja melhor:
CREATE OR ALTER TRIGGER TRG_Tabela_A_CONTROLE
ON Tabela_A
AFTER DELETE,UPDATE
AS
BEGIN
	DECLARE @FDC_CAMPO VARCHAR(80);
	DECLARE @ID_COLUNA INT;
	DECLARE @PW_COLUNA INT;
	DECLARE @BYTE_COLUNA INT;

	IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM Deleted) -- UPDATE
	BEGIN
		DECLARE CS_TESTE CURSOR
		FOR	SELECT
				RTRIM(LTRIM(COLUMN_NAME)),
				COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ''.'' + TABLE_NAME),  
				COLUMN_NAME, ''ColumnID'')
			FROM INFORMATION_SCHEMA.COLUMNS  
			WHERE TABLE_NAME = ''Tabela_A''
				AND COLUMN_NAME IN (SELECT LTRIM(RTRIM(FDC_CAMPO)) FROM Tabela_B WHERE FDC_TABELA = ''Tabela_A'')

		OPEN CS_TESTE;

		FETCH NEXT FROM CS_TESTE
		INTO @FDC_CAMPO, @ID_COLUNA;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- é necessário encontar o byte correto para a função COLUMNS_UPDATED()
			SET @BYTE_COLUNA = (@ID_COLUNA / 8);
			SET @ID_COLUNA = (@ID_COLUNA - (8 * @BYTE_COLUNA));
			SET @PW_COLUNA = POWER(2, (@ID_COLUNA-1));

			IF ((SUBSTRING(COLUMNS_UPDATED(), (@BYTE_COLUNA+1), 1) & @PW_COLUNA) = @PW_COLUNA)
			BEGIN
				/*AÇÃO A SER REALIZADA*/
				PRINT ''campo '' + @FDC_CAMPO + '' alterado.''
			END

			FETCH NEXT FROM CS_TESTE
			INTO @FDC_CAMPO, @ID_COLUNA;
		END

		CLOSE CS_TESTE;
		DEALLOCATE CS_TESTE;

	END

END

GOSTEI 0
Emerson Nascimento

Emerson Nascimento

11/05/2020

ao executar a instrução abaixo, trocando Tabela_A pela tabela pertinente, retorna algum registro:
SELECT
    RTRIM(LTRIM(COLUMN_NAME)),
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ''.'' + TABLE_NAME),  
    COLUMN_NAME, ''ColumnID'')
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = ''Tabela_A''
    AND LTRIM(RTRIM(COLUMN_NAME)) IN (SELECT LTRIM(RTRIM(FDC_CAMPO)) FROM Tabela_B WHERE FDC_TABELA = ''Tabela_A'')
GOSTEI 0
Jose Carvalho

Jose Carvalho

11/05/2020

Retorna sim, os campos em comum nas tabelas.

ao executar a instrução abaixo, trocando Tabela_A pela tabela pertinente, retorna algum registro:
SELECT
    RTRIM(LTRIM(COLUMN_NAME)),
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ''.'' + TABLE_NAME),  
    COLUMN_NAME, ''ColumnID'')
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = ''Tabela_A''
    AND LTRIM(RTRIM(COLUMN_NAME)) IN (SELECT LTRIM(RTRIM(FDC_CAMPO)) FROM Tabela_B WHERE FDC_TABELA = ''Tabela_A'')
GOSTEI 0
POSTAR