適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
この関数は、テーブルまたはビューの挿入または更新された列を示す varbinary ビット パターンを返します。
COLUMNS_UPDATEDTransact-SQL INSERT またはUPDATE トリガーの本体内の任意の場所を使用して、トリガーが特定のアクションを実行する必要があるかどうかをテストします。
構文
COLUMNS_UPDATED ( )
戻り値の型
varbinary さん
解説
COLUMNS_UPDATED は、複数の列に対して実行される UPDATE または INSERT アクションをテストします。 1 つの列で UPDATE または INSERT 試行をテストするには、 UPDATE() を使用します。
COLUMNS_UPDATED は、左から右に順序付けられた 1 つまたは複数のバイトを返します。 各バイトの右端のビットは、最下位ビットです。 左端のバイトの右端のビットがテーブル内の最初のテーブル列を表し、右から 2 番目のビットは 2 番目の列を、それ以下のビットも同様の順で列を表します。 トリガーが作成されるテーブルに列が 9 個以上ある場合、COLUMNS_UPDATED は複数のバイトを返します。最下位バイトが左端になります。
COLUMNS_UPDATEDは、TRUEアクションのすべての列に対してINSERTを返します。これは、列に明示的な値または暗黙的な (NULL) 値が挿入されているためです。
特定の列に対する更新または挿入をテストするには、テスト対象列のビットごとの演算子および整数ビットマスクを使用した構文に従います。 たとえば、テーブル t1 には、 C1、 C2、 C3、 C4、および C5の列が含まれているとします。 すべての列が正常に更新C2、C3、およびC4 (テーブルt1UPDATEトリガーを持つ) ことを確認するには、& 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にリセットされるため、列セット内の変更されていない列は変更された状態で表示されます。 列セットの詳細については、「列セットの使用」を参照してください。
例
Ａ。 COLUMNS_UPDATEDを使用してテーブルの最初の 8 列をテストする
この例では、employeeData と auditEmployeeData という 2 つのテーブルを作成します。
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