Column 1 2 3 4 5 6 7 8 || 9 10 ---------------------------------------------------------------------------------------------------------- Byte 1 1 1 1 1 1 1 1 || 2 2 Bit Position (dec) 1 2 4 8 16 32 64 128 || 1 2 Bit Position (hex) 1 2 4 8 10 20 40 80 || 1 2 -- EXAMPLE USING COLUMNS_UPDATED() USE pubs 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 employeeData ( emp_id int NOT NULL, 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 auditEmployeeData ( audit_log_id uniqueidentifier DEFAULT NEWID(), 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 updEmployeeData ON employeeData FROM update AS /* Check whether columns 2, 3 or 4 have been updated. If any or all of 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. To check if ALL (as opposed to any) of columns 2, 3, and 4 are updated, use "= 14" in place of "> 0" (see below). */ /* Use IF (COLUMNS_UPDATED() & 14) = 14 to see if ALL of columns 2, 3 and 4 have been updated. */ IF (COLUMNS_UPDATED() & 14) > 0 BEGIN -- Audit OLD record. INSERT INTO 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 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 -- EXAMPLE INSTEAD OF TRIGGER -- CREATE TWO TABLES WITH CUSTOMER DATA SELECT * INTO CustomersGer FROM Customers WHERE Country = 'Germany' SELECT * INTO CustomersMex FROM Customers WHERE Country = 'Mexico' GO -- CREATE VIEW ON TABLES CREATE VIEW CustomersView AS SELECT * FROM CustomersGer UNION SELECT * FROM CustomersMex GO -- CREATE TRIGGER CREATE TRIGGER Customers_Update2 ON CustomersView INSTEAD OF UPDATE AS DECLARE @Country nvarchar(15) SET @Country = (SELECT Country FROM inserted) IF @Country = 'Germany' BEGIN UPDATE CustomersGer SET CustomersGer.Phone = inserted.Phone FROM CustomersGer JOIN inserted ON CustomersGer.CustomerID = inserted.CustomerID END ELSE IF @Country = 'Mexico' BEGIN UPDATE CustomersMex SET CustomersMex.Phone = inserted.Phone FROM CustomersMex JOIN inserted ON CustomersMex.CustomerID = inserted.CustomerID END -- TEST TRIGGER UPDATE CustomersView SET Phone = '030-007xxxx' WHERE CustomerID = 'ALFKI' SELECT CustomerID, Phone FROM CustomersView WHERE CustomerID = 'ALFKI' SELECT CustomerID, Phone FROM CustomersGer WHERE CustomerID = 'ALFKI'