I found an issue recently with a trigger a developer had written on a table that captured columns updated on that table and inserted the results into another table. We were required to do this for auditing and the table had sensitive/critical data. When the auditors went through the captured information they found that in some cases the data before and after the ‘change’ was the same on some columns. By audit standards this was not considered a ‘change’. This led to some speculation on validity of the trigger and long explanations had to be written on why this was happening.

I found the issue to be this – the developer was using the function ‘columns_updated()’ to capture which columns were changed. Now if for some reason user tabbed to the column and left it unchanged or even changed it to one value and back to original before saving – columns_updated still showed the column as changed.

Using the same example from BOL -

    USE AdventureWorks; 
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 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 

ALTER TRIGGER [dbo].[tr_EmpHistory] ON [dbo].[employeeData] 
FOR UPDATE 
AS 
BEGIN      
	DECLARE @FieldsUpdated XML      
	DECLARE @ColumnsUpdated VARBINARY(100)

	SET @ColumnsUpdated = COLUMNS_UPDATED()     

	SET @FieldsUpdated = (
		SELECT COLUMN_NAME AS Name 
		FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = 'employeeData' 
		AND (
			sys.fn_IsBitSetInBitmask (
			@ColumnsUpdated, COLUMNPROPERTY(OBJECT_ID('dbo' + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) <> 0
			)  
	FOR XML AUTO, ROOT('Fields')
   )

	INSERT INTO auditEmployeeData
	(
		audit_emp_id,
		audit_emp_bankAccountNumber,
		audit_emp_salary,
		audit_emp_SSN,
		audit_emp_lname,
		audit_emp_fname,
		audit_emp_manager,
		ColumnsUpdated
	)     

	SELECT 
		emp_id,
		emp_bankAccountNumber,
		emp_salary,
		emp_SSN,
		emp_lname,
		emp_fname,
		emp_manager,
		@FieldsUpdated  
	FROM INSERTED 
END

GO
    

Now I insert a record that i am going to audit:

    /*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
    

Now am updating the inserted record, setting one column (emp_SSN) to the same value as it was.

   /*Updating the employee record for employee number 101 to change the 
    salary to 51000, note that am not changing the ssn just updating it to the same value, this 
    causes the UPDATE trigger to fire and an audit trail to 
    be produced.*/

    UPDATE employeeData 
       SET emp_salary = 51000, 
           emp_SSN = 'R-M53550M' 
       WHERE emp_id = 101; 
    GO
   

Now we will look at the audit table..

    select * from auditEmployeedata
    

If we click on the last field which gives list of fields modified – we get both fields, emp_salary and emp_ssn

If we modify the audit trigger as below, to use inserted and deleted tables instead of columns modified, we get the desired result.

    ALTER TRIGGER [dbo].[tr_EmpHistory] 
	ON [dbo].[employeeData] 
FOR UPDATE 
AS 
BEGIN      
      DECLARE @FieldsUpdated xml, 
			  @FieldsUpdated1 varchar(100)      
      SELECT @Fieldsupdated1 = ' ' 
      SELECT 
		@FieldsUpdated1 = @FieldsUpdated1 + ' emp_bankaccountnumber' 
		FROM inserted as a, 
			deleted as b 
		WHERE a.emp_id = b.emp_id              
		AND a.emp_bankAccountNumber <> b.emp_bankAccountNumber 
      SELECT 
		@FieldsUpdated1 = @FieldsUpdated1 + 'emp_salary ' 
		FROM inserted as a, 
			deleted as b 
		WHERE a.emp_id = b.emp_id              
		AND a.emp_salary <> b.emp_salary

      SELECT 
		@FieldsUpdated1 = @FieldsUpdated1 + 'emp_SSN ' 
		FROM inserted as a, 
			deleted as b 
		WHERE a.emp_id = b.emp_id              
		AND a.emp_SSN <> b.emp_SSN 
      SELECT 
		@FieldsUpdated1 = @FieldsUpdated1 + 'emp_lname ' 
		FROM inserted as a, 
			deleted as b 
		WHERE a.emp_id = b.emp_id              
		AND a.emp_lname <> b.emp_lname 
      SELECT 
		@FieldsUpdated1 = @FieldsUpdated1 + 'emp_fname ' 
		FROM inserted as a, 
			deleted as b 
		WHERE a.emp_id = b.emp_id              
		AND a.emp_fname <> b.emp_fname 
      SELECT 
		@FieldsUpdated1 = @FieldsUpdated1 + 'emp_manager ' 
		FROM inserted as a, 
			deleted as b 
		WHERE a.emp_id = b.emp_id              
		AND a.emp_manager <> b.emp_manager 
      SELECT @Fieldsupdated =  (
		SELECT COLUMN_NAME AS Name 
		FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = 'employeeData' 
		AND CHARINDEX(COLUMN_NAME,(ltrim(rtrim(@fieldsupdated1)))) > 0  
      FOR XML AUTO, ROOT('Fields') )

      INSERT INTO auditEmployeeData(
		audit_emp_id,
		audit_emp_bankAccountNumber,
		audit_emp_salary,
		audit_emp_SSN,
		audit_emp_lname,
		audit_emp_fname,
		audit_emp_manager,
		ColumnsUpdated )     

      SELECT emp_id,
		emp_bankAccountNumber,
		emp_salary,
		emp_SSN,
		emp_lname,
		emp_fname,
		emp_manager,
		@FieldsUpdated  
	FROM INSERTED 
END
GO
    

Now I run the statement to modify only salary, but set bank account number to same id.

   UPDATE employeeData 
   SET emp_salary = 90000, 
       emp_SSN = 'R-M53550M' 
   WHERE emp_id = 101; 
    GO
   

Then I select from audit log table:

The columns updates shows me only salary:

If I re run it to alter both salary and SSN -

    UPDATE employeeData 
       SET emp_salary = 33000, 
           emp_SSN = 'R-M53550F' 
       WHERE emp_id = 101; 
    GO
    

I select from the audit table:

The column updates show me both columns -

Hence we were able to achieve the same results by modifying the trigger to use inserted and updated tables.