Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
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 dentro do corpo de um Transact-SQL INSERT ou UPDATE gatilho para testar se o gatilho deve executar determinadas ações.
Convenções de sintaxe de Transact-SQL
Sintaxe
COLUMNS_UPDATED ( )
Tipos de retorno
varbinary
Comentários
COLUMNS_UPDATED testes para UPDATE ou INSERT ações executadas em várias colunas. Para testar UPDATE ou INSERT tentar 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 INSERT ações porque as colunas têm valores explícitos ou valores 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 colunasC1, C2, C3e C4C5. Para verificar se as colunas e C2C3todas foram atualizadas C4com êxito (com a tabela t1 tendo um UPDATE gatilho), siga a sintaxe com & 14. Para testar se apenas a coluna C2 é atualizada, especifique & 2. Veja o Exemplo A e o Exemplo B para obter exemplos reais.
Use COLUMNS_UPDATED qualquer lugar dentro de um Transact-SQL INSERT ou UPDATE gatilho. Se isso for executado fora de um gatilho, um NULL será retornado.
A ORDINAL_POSITION coluna do modo de exibição INFORMATION_SCHEMA.COLUMNS não é compatível com o padrão de bit das 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 essa coluna é definido
1como , e o bit do conjunto de colunas é definido como1.Ao atualizar explicitamente um conjunto de colunas, o bit do conjunto de colunas é definido
1como , e os bits para todas as colunas esparsas nessa tabela são definidos como1.Para operações de inserção, todos os bits são definidos como
1.Como as alterações em um conjunto de colunas fazem com que os bits de todas as colunas no conjunto de colunas sejam redefinidos
1, as colunas inalteradas em um conjunto de colunas serão modificadas. Veja Usar conjuntos de colunas para obter mais informações sobre conjuntos de colunas.
Exemplos
a. Use COLUMNS_UPDATED para testar as oito primeiras 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 AS 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 AS 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. Usar 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