How to find a column is updated.

In application development, there are situations where an action is driven based on a change in data. A trigger can be used to perform an action based on the data change or some type of events that occur in the database or the server.

SQL Server has a function called “UPDATE()” which is used to identify whether an update or insert occurred for the given column. The scope of this function is only available to the trigger. This function is useful to branch the code based on the updates that occur for the column.

For example, the following code can be used to send an alert or log the change in the audit table when there is a change detected in the “TAXID” column of the “CustomerInfo” table.

CREATE TABLE dbo.CustomerInfo (
CustomerId INT IDENTITY(1,1), TAXID VARCHAR(25), Phone VARCHAR(15)
)

GO

CREATE TRIGGER dbo.tr_CustomerInfo ON dbo.CustomerInfo AFTER UPDATE
AS 
BEGIN
	IF UPDATE(TAXID) --> 'AUDIT' 
	BEGIN
		IF EXISTS (
                SELECT 1 AS Change
		FROM inserted i JOIN deleted d
		ON i.CustomerId=d.CustomerId AND ISNULL(i.TAXID,'') != ISNULL(d.TAXID,'')
		)
		BEGIN
			PRINT 'AUDIT LOG/SEND MESSAGE TO QUEUE FOR ALERT'
		END
	END
	PRINT 'OTHER BUSINESS/APPLICATION LOGIC'
	/* 
	'
	'	
	*/
END

GO 

INSERT INTO dbo.CustomerInfo (TAXID, Phone) VALUES('584-9653-8TY','256-8956-412')

UPDATE dbo.CustomerInfo SET TAXID = NULL WHERE CustomerId=1
UPDATE dbo.CustomerInfo SET Phone = NULL WHERE CustomerId=1

The ‘AUDIT’ section of the trigger will execute for the first update statement as it affects the TAXID column but the later update statement will not go through the ‘AUDIT’ section as TAXID is not part of the update statement.

This function doesn’t detect actual changes on the data as it will return “true” even if the same value is updated to the column. The actual data change is identified using the special tables called “inserted” and “deleted”. The “UPDATE” function is useful for modularizing the logic so that the code ‘AUDIT’ will execute only when the column is part of the UPDATE query.

Leave a Reply

Your email address will not be published.