COLUMNS_UPDATED(Transact-SQL)
적용 대상:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
이 함수는 테이블이나 뷰에서 삽입되거나 업데이트된 열을 나타내는 varbinary 비트 패턴을 반환합니다. Transact-SQL INSERT 또는 UPDATE 트리거의 본문 내 어디서나 COLUMNS_UPDATED를 사용하여 트리거에서 특정 작업을 실행해야 하는지 여부를 테스트합니다.
Syntax
COLUMNS_UPDATED ( )
참고 항목
SQL Server 2014(12.x) 및 이전 버전에 대한 Transact-SQL 구문을 보려면 이전 버전 설명서를 참조 하세요.
반환 형식
varbinary
설명
COLUMNS_UPDATED는 여러 열에서 수행되는 UPDATE 또는 INSERT 작업을 테스트하기 위한 것입니다. 한 열에 대한 UPDATE 또는 INSERT를 테스트하려면 UPDATE()를 사용하세요.
COLUMNS_UPDATED는 왼쪽에서 오른쪽으로 정렬된 하나 이상의 바이트를 반환합니다. 각 바이트의 가장 오른쪽 비트는 최하위 비트입니다. 가장 왼쪽의 바이트에 있는 가장 오른쪽 비트는 테이블의 첫 번째 테이블 열을 나타내며, 왼쪽의 다음 비트는 두 번째 열을 나타내는 방식 등으로 이어집니다. COLUMNS_UPDATED는 트리거를 만든 테이블에 8개를 초과하는 열이 있는 경우 가장 왼쪽에 있는 최하위 바이트를 사용하여 여러 바이트를 반환합니다. 명시적 값 또는 암시적(NULL) 값이 삽입된 열이 있으므로 COLUMNS_UPDATED는 INSERT 작업의 모든 열에 대해 TRUE를 반환합니다.
특정 열에 대한 업데이트 또는 삽입을 테스트하려면 비트 연산자 및 테스트된 열의 정수 비트 마스크가 있는 구문을 따릅니다. 예를 들어 t1 테이블에 C1, C2, C3, C4 및 C5 열이 포함되어 있다고 가정합니다. C2, C3 및 C4 열이 모두 성공적으로 업데이트되었는지 확인하려면(테이블 t1에 UPDATE 트리거가 있음) & 14의 구문을 따릅니다. C2 열만이 업데이트되었는지 테스트하려면 & 2를 지정합니다. 실제 예제는 예제 A 및 예제 B를 참조하세요.
COLUMNS_UPDATED를 Transact-SQL INSERT 또는 UPDATE 트리거 내 어디서든 사용합니다.
INFORMATION_SCHEMA.COLUMNS 뷰의 ORDINAL_POSITION 열은 COLUMNS_UPDATED에서 반환하는 열의 비트 패턴과 호환되지 않습니다. COLUMNS_UPDATED와 호환되는 비트 패턴을 얻으려면 다음 예제와 같이 INFORMATION_SCHEMA.COLUMNS 뷰를 쿼리할 때 COLUMNPROPERTY 시스템 함수의 ColumnID 속성을 참조합니다.
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 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. COLUMNS_UPDATED를 사용하여 9개 이상의 열 테스트
처음 8개 이외의 테이블 열에 영향을 주는 업데이트를 확인하려면 SUBSTRING 함수를 사용하여 COLUMNS_UPDATED에서 정확한 비트를 반환하는지 테스트합니다. 다음 예제에서는 AdventureWorks2022.Person.Person 테이블의 3, 5 및 9 열에 영향을 주는 업데이트를 테스트합니다.
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
참고 항목
비트 연산자(Transact-SQL)
CREATE TRIGGER(Transact-SQL)
UPDATE()(Transact-SQL)
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기