COLUMNS_UPDATED (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure
Essa função retorna um padrão de bit varbinary que indica as colunas inseridas ou atualizadas de uma tabela ou exibição. Use COLUMNS_UPDATED em qualquer lugar no corpo de um gatilho INSERT ou UPDATE do Transact-SQL para testar se o gatilho deve executar determinadas ações.
Convenções de sintaxe de Transact-SQL
Sintaxe
COLUMNS_UPDATED ( )
Observação
Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.
Tipos de retorno
varbinary
Comentários
COLUMNS_UPDATED testa para UPDATE ou INSERT as ações executadas em várias colunas. Para testar as tentativas de UPDATE ou INSERT em uma coluna, use UPDATE().
COLUMNS_UPDATED retorna um ou mais bytes ordenados da esquerda para a direita. O bit mais à direita de cada byte é o bit menos significativo. O mais à direita do byte mais à esquerda representa a primeira coluna na tabela; o próximo bit à esquerda representa a segunda coluna, e assim por diante. COLUMNS_UPDATED retornará vários bytes se a tabela na qual o gatilho foi criado contiver mais que oito colunas, ficando o byte menos significativo na extrema esquerda. COLUMNS_UPDATED retorna TRUE para todas as colunas em ações INSERT porque as colunas têm valores explícitos ou implícitos (NULL) inseridos.
Para testar as atualizações ou inserções feitas em colunas específicas, a sintaxe deve ser seguida por um operador bit a bit e um bitmask inteiro das colunas testadas. Por exemplo, digamos que a tabela t1 contenha as colunas C1, C2, C3, C4 e C5. Para verificar se as colunas C2, C3 e C4 foram todas atualizadas com sucesso (tendo a tabela t1 um gatilho UPDATE), siga a sintaxe com & 14. Para testar se apenas a coluna C2 foi atualizada, especifique & 2. Veja o Exemplo A e o Exemplo B para obter exemplos reais.
Use COLUMNS_UPDATED em qualquer lugar dentro de um gatilho INSERT ou UPDATE do Transact-SQL.
A coluna ORDINAL_POSITION da exibição INFORMATION_SCHEMA.COLUMNS não é compatível com o padrão de bit de colunas retornadas por COLUMNS_UPDATED. Para obter um padrão de bit compatível com COLUMNS_UPDATED, veja a propriedade ColumnID da função do sistema COLUMNPROPERTY ao consultar a exibição INFORMATION_SCHEMA.COLUMNS, como mostrado no exemplo a seguir.
SELECT TABLE_NAME, COLUMN_NAME,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks2022.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person';
Se um gatilho se aplicar a uma coluna, o COLUMNS_UPDATED retornará como true ou 1, mesmo que o valor da coluna permaneça inalterado. Isso é por design, e o gatilho deve implementar a lógica de negócios que determina se a operação de inserir/atualizar/excluir é permitida ou não.
Conjuntos de colunas
Quando um conjunto de coluna é definido em uma tabela, a função COLUMNS_UPDATED comporta-se das seguintes maneiras:
Ao atualizar explicitamente uma coluna membro do conjunto de colunas, o bit correspondente para aquela coluna é definido como 1, e o bit do conjunto de colunas é definido como 1.
Ao atualizar explicitamente um conjunto de colunas, o bit do conjunto de colunas é definido como 1 e os bits para todas as colunas esparsas na tabela são definidos como 1.
Para operações de inserção, todos os bits são definidos como 1.
Porque alterações a um conjunto de coluna fazem os bits de todas as colunas no conjunto de colunas serem redefinidos como 1, colunas inalteradas em um conjunto de colunas parecerão modificadas. Veja Usar conjuntos de colunas para obter mais informações sobre conjuntos de colunas.
Exemplos
a. Usando COLUMNS_UPDATED para testar as primeiras oito colunas de uma tabela
Este exemplo cria duas tabelas: employeeData e auditEmployeeData. A tabela employeeData contém informações de folha de pagamento confidenciais de funcionários, e membros do departamento de recursos humanos podem modificá-las. Se o SSN (número do seguro social), o salário anual ou o número da conta bancária de um funcionário for alterado, um registro de auditoria será gerado e inserido na tabela de auditoria auditEmployeeData.
Com a função COLUMNS_UPDATED(), podemos rapidamente testar quaisquer alterações feitas a colunas contendo informações confidenciais de funcionários. Usar COLUMNS_UPDATED() dessa forma funcionará apenas quando você estiver tentando detectar alterações nas primeiras oito colunas da tabela.
USE AdventureWorks2022;
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 dbo.employeeData (
emp_id INT NOT NULL PRIMARY KEY,
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 dbo.auditEmployeeData (
audit_log_id uniqueidentifier DEFAULT NEWID() PRIMARY KEY,
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 dbo.updEmployeeData
ON dbo.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.
This bitmask translates into base_10 as: 2 + 4 + 8 = 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 dbo.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 dbo.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 dbo.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 dbo.employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101;
GO
SELECT * FROM dbo.auditEmployeeData;
GO
B. Usando COLUMNS_UPDATED para testar mais de oito colunas
Para testar atualizações que afetam outras colunas além das oito primeiras colunas da tabela, use a função SUBSTRING para testar o bit correto retornado por COLUMNS_UPDATED. Este exemplo testa as atualizações que afetam as colunas 3, 5 e 9 na tabela AdventureWorks2022.Person.Person.
USE AdventureWorks2022;
GO
IF OBJECT_ID (N'Person.uContact2', N'TR') IS NOT NULL
DROP TRIGGER Person.uContact2;
GO
CREATE TRIGGER Person.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 NameStyle = NameStyle,
FirstName=FirstName,
EmailPromotion=EmailPromotion;
GO
Veja também
Operadores bit a bit (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
UPDATE() (Transact-SQL)
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de