The bitmask is: power(2,(2-1)) power(2,(3-1)) power(2,(4-1)) = 14.
To test whether all columns 2, 3, and 4 are updated, use = 14 instead of 0 /*Use IF (COLUMNS_UPDATED() & 14) = 14 to see whether all columns 2, 3, and 4 are updated.*/ BEGIN -- Audit OLD record.
COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.
COLUMNS_UPDATED tests for UPDATE or INSERT actions performed on multiple columns.
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.
Cluster-Aware Updating (CAU) is a feature that coordinates software updates on all servers in a failover cluster in a way that does not impact the service availability any more than a planned failover of a cluster node.
COLUMNS_UPDATED returns TRUE for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.
To test for updates or inserts to specific columns, follow the syntax with a bitwise operator and an integer bitmask of the columns being tested.TABLES WHERE TABLE_NAME = 'audit Employee Data') DROP TABLE audit Employee Data; GO CREATE TABLE dbo.employee Data ( emp_id int NOT NULL PRIMARY KEY, emp_bank Account Number 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.audit Employee Data ( audit_log_id uniqueidentifier DEFAULT NEWID() PRIMARY KEY, audit_log_type char (3) NOT NULL, audit_emp_id int NOT NULL, audit_emp_bank Account Number 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 Employee Data ON dbo.employee Data 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.For some applications with continuous availability features (such as Hyper-V with live migration, or an SMB 3.0 file server with SMB Transparent Failover), CAU can coordinate automated cluster updating with no impact on service availability. CAU is agnostic to the type of the clustered application.CAU is an external cluster-updating solution that is layered on top of clustering APIs and Windows Power Shell cmdlets.If the social security number (SSN), yearly salary, or bank account number for an employee is changed, an audit record is generated and inserted into the USE Adventure Works2012; GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.