COLUMNS_UPDATED (T-SQL)

Artikel
10/16/2024

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Fungsi ini mengembalikan pola bit varbinary yang menunjukkan kolom tabel atau tampilan yang disisipkan atau diperbarui. Gunakan COLUMNS_UPDATED di mana saja di dalam isi pemicu TRANSACT-SQL INSERT atau UPDATE untuk menguji apakah pemicu harus menjalankan tindakan tertentu.

Konvensi sintaks transact-SQL

Sintaks

COLUMNS_UPDATED ( )

Jenis yang dikembalikan

varbinary

Keterangan

COLUMNS_UPDATED pengujian untuk tindakan UPDATE atau INSERT yang dilakukan pada beberapa kolom. Untuk menguji upaya UPDATE atau INSERT pada satu kolom, gunakan UPDATE().

COLUMNS_UPDATED mengembalikan satu atau beberapa byte yang diurutkan dari kiri ke kanan. Bit paling kanan dari setiap byte adalah bit yang paling tidak signifikan. Bit paling kanan byte paling kiri mewakili kolom tabel pertama dalam tabel, bit berikutnya di sebelah kiri mewakili kolom kedua, dan sebagainya. COLUMNS_UPDATED mengembalikan beberapa byte jika tabel tempat pemicu dibuat berisi lebih dari delapan kolom, dengan byte paling tidak signifikan menjadi yang paling kiri. COLUMNS_UPDATED mengembalikan TRUE untuk semua kolom dalam tindakan INSERT karena kolom memiliki nilai eksplisit atau nilai implisit (NULL) yang disisipkan.

Untuk menguji pembaruan atau menyisipkan ke kolom tertentu, ikuti sintaks dengan operator bitwise dan bitmask bilangan bulat kolom yang diuji. Misalnya, katakanlah tabel t1 berisi kolom C1, C2, C3, C4, dan C5. Untuk memverifikasi bahwa kolom C2, C3, dan C4 semuanya berhasil diperbarui (dengan tabel t1 memiliki pemicu UPDATE), ikuti sintaks dengan & 14. Untuk menguji apakah hanya kolom C2 yang diperbarui, tentukan & 2. Lihat Contoh A dan Contoh B untuk contoh aktual.

Gunakan COLUMNS_UPDATED di mana saja di dalam pemicu TRANSACT-SQL INSERT atau UPDATE.

Kolom ORDINAL_POSITION INFORMATION_SCHEMA. Tampilan COLUMNS tidak kompatibel dengan pola bit kolom yang dikembalikan oleh COLUMNS_UPDATED. Untuk mendapatkan pola bit yang kompatibel dengan COLUMNS_UPDATED, referensikan ColumnID properti fungsi sistem saat mengkueri COLUMNPROPERTY INFORMATION_SCHEMA.COLUMNS tampilan, seperti yang ditunjukkan dalam contoh berikut.

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';

Jika pemicu berlaku untuk kolom, mengembalikan COLUMNS_UPDATED sebagai true atau 1, meskipun nilai kolom tetap tidak berubah. Ini secara desain, dan pemicu harus menerapkan logika bisnis yang menentukan apakah operasi sisipkan/perbarui/hapus diizinkan atau tidak.

Kumpulan kolom

Saat kumpulan kolom ditentukan pada tabel, COLUMNS_UPDATED fungsi berperilaku dengan cara berikut:

Saat secara eksplisit memperbarui kolom anggota dari kumpulan kolom, bit yang sesuai untuk kolom tersebut diatur ke 1, dan bit kumpulan kolom diatur ke 1.
Saat memperbarui kumpulan kolom secara eksplisit, bit kumpulan kolom diatur ke 1, dan bit untuk semua kolom jarang dalam tabel tersebut diatur ke 1.
Untuk operasi sisipan, semua bit diatur ke 1.

Karena perubahan pada kumpulan kolom menyebabkan bit semua kolom dalam kolom yang diatur untuk direset ke 1, kolom yang tidak berubah dalam kumpulan kolom akan muncul dimodifikasi. Lihat Menggunakan Kumpulan Kolom untuk informasi selengkapnya tentang kumpulan kolom.

Contoh

J. Menggunakan COLUMNS_UPDATED untuk menguji delapan kolom pertama tabel

Contoh ini membuat dua tabel: employeeData dan auditEmployeeData. Tabel ini employeeData menyimpan informasi penggajian karyawan sensitif dan anggota departemen sumber daya manusia dapat memodifikasinya. Jika nomor jaminan sosial (SSN), gaji tahunan, atau nomor rekening bank untuk perubahan karyawan, catatan audit dihasilkan dan dimasukkan ke auditEmployeeData dalam tabel audit.

Dengan fungsi ini COLUMNS_UPDATED() , kita dapat dengan cepat menguji setiap perubahan yang dilakukan pada kolom yang berisi informasi karyawan sensitif. Menggunakan COLUMNS_UPDATED() cara ini hanya berfungsi saat mencoba mendeteksi perubahan pada delapan kolom pertama dalam tabel.

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. Menggunakan COLUMNS_UPDATED untuk menguji lebih dari delapan kolom

Untuk menguji pembaruan yang memengaruhi kolom selain delapan kolom tabel pertama, gunakan SUBSTRING fungsi untuk menguji bit yang benar yang dikembalikan oleh COLUMNS_UPDATED. Contoh ini menguji pembaruan yang memengaruhi kolom 3, , 5dan 9 dalam AdventureWorks2022.Person.Person tabel.

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

Lihat juga

Operator Bitwise (Transact-SQL)
BUAT PEMICU (Transact-SQL)
UPDATE() (Transact-SQL)

Bagikan melalui