적용 대상:
SQL ServerAzure SQL 데이터베이스Azure SQL Managed Instance
이 함수는 테이블이나 뷰에서 삽입되거나 업데이트된 열을 나타내는 varbinary 비트 패턴을 반환합니다. Transact-SQL COLUMNS_UPDATED 또는 트리거의 본문 내 아무 곳이나 INSERT 사용하여 UPDATE 트리거가 특정 작업을 실행해야 하는지 여부를 테스트합니다.
구문
COLUMNS_UPDATED ( )
반환 형식
varbinary
설명
COLUMNS_UPDATED 은 여러 열에 대해 UPDATE 수행된 작업 또는 INSERT 테스트합니다. 한 열에 대해 UPDATE 테스트하거나 INSERT 시도하려면 UPDATE()를 사용합니다.
COLUMNS_UPDATED는 왼쪽에서 오른쪽으로 정렬된 하나 이상의 바이트를 반환합니다. 각 바이트의 가장 오른쪽 비트는 최하위 비트입니다. 가장 왼쪽의 바이트에 있는 가장 오른쪽 비트는 테이블의 첫 번째 테이블 열을 나타내며, 왼쪽의 다음 비트는 두 번째 열을 나타내는 방식 등으로 이어집니다.
COLUMNS_UPDATED는 트리거를 만든 테이블에 8개를 초과하는 열이 있는 경우 가장 왼쪽에 있는 최하위 바이트를 사용하여 여러 바이트를 반환합니다.
COLUMNS_UPDATED는 열에 TRUE 명시적 값 또는 암시적(NULL) 값이 삽입되어 있기 때문에 작업의 모든 열에 대해 반환 INSERT 됩니다.
특정 열에 대한 업데이트 또는 삽입을 테스트하려면 비트 연산자 및 테스트된 열의 정수 비트 마스크가 있는 구문을 따릅니다. 예를 들어 테이블에 t1 열C1, , C2C3C4및 C5열이 포함되어 있다고 가정해 보겠습니다. 열 및 C2C3C4 모든 열이 성공적으로 업데이트되었는지 확인하려면(트리거가 있는 t1 테이블 UPDATE 사용) 다음 구문을 & 14따릅니다. 열 C2 만 업데이트되는지 여부를 테스트하려면 .를 지정합니다 & 2. 실제 예제는 예제 A 및 예제 B를 참조하세요.
Transact-SQL COLUMNS_UPDATED 또는 트리거 내의 아무 곳이나 INSERT 사용합니다UPDATE. 트리거 외부에서 실행되는 경우 NULL이 반환됩니다.
뷰의 ORDINAL_POSITION 열이 INFORMATION_SCHEMA.COLUMNS 반환COLUMNS_UPDATED된 열의 비트 패턴과 호환되지 않습니다.
COLUMNS_UPDATED와 호환되는 비트 패턴을 얻으려면 다음 예제와 같이 ColumnID 뷰를 쿼리할 때 COLUMNPROPERTY 시스템 함수의 INFORMATION_SCHEMA.COLUMNS 속성을 참조합니다.
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';
트리거가 열에 적용되면 열 값이 변경되지 않는 경우에도 COLUMNS_UPDATED는 true 또는 1로 반환됩니다. 이는 의도적으로 설계된 것이며 트리거는 삽입/업데이트/삭제 작업이 허용되는지 여부를 결정하는 비즈니스 논리를 구현해야 합니다.
열 집합
테이블에 열 집합이 정의되면 COLUMNS_UPDATED 함수가 다음과 같은 방식으로 작동합니다.
열 집합의 멤버 열을 명시적으로 업데이트하는 경우 해당 열의 해당 비트가 설정
1되고 열 집합 비트가 로1설정됩니다.열 집합을 명시적으로 업데이트하는 경우 열 집합 비트가 설정
1되고 해당 테이블의 모든 스파스 열에 대한 비트가 로1설정됩니다.삽입 작업의 경우 모든 비트가 .로
1설정됩니다.열 집합을 변경하면 열 집합에 있는 모든 열의 비트가 다시 설정되므로
1열 집합의 변경되지 않은 열이 수정된 것으로 표시됩니다. 열 집합에 대한 자세한 내용은 열 집합 사용을 참조하세요.
예
A. COLUMNS_UPDATED 사용하여 테이블의 처음 8개 열 테스트
다음 예제에서는 employeeData 및 auditEmployeeData의 두 테이블을 만듭니다.
employeeData 테이블에는 중요한 직원 급여 정보가 있으므로 인력 관리 부서의 멤버만 이를 수정할 수 있습니다. 직원의 SSN(사회 보장 번호), 연봉 또는 은행 계좌 번호가 변경되면 감사 레코드가 생성되고 auditEmployeeData 감사 테이블에 삽입됩니다.
COLUMNS_UPDATED() 함수를 사용하여 중요한 직원 정보가 포함된 열의 변경 내용을 빠르게 테스트할 수 있습니다.
COLUMNS_UPDATED()를 사용하는 경우 이 방법은 테이블의 처음 8개 열에 대한 변경 내용을 검색하려고 할 때만 작동합니다.
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. COLUMNS_UPDATED 사용하여 8개 이상의 열 테스트
처음 8개 이외의 테이블 열에 영향을 주는 업데이트를 확인하려면 SUBSTRING 함수를 사용하여 COLUMNS_UPDATED에서 정확한 비트를 반환하는지 테스트합니다. 다음 예제에서는 3 테이블의 5, 9 및 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