SQL Server Trigger to check if value has changed

In this SQL Server tutorial, we will discuss how to create a SQL Server Trigger to check if value has changed. And we will try to understand this topic using various examples in SQL Server.

Recently, I got a requirement where I need to check if the value specified in a specific column of a table has changed or not. The goal of this requirement is to check which column value will be updated after the UPDATE operation in SQL Server.

So, this SQL Server tutorial will explain various methods to define a SQL Server Trigger to check if value has changed. Here is the list of topics that we will discuss.

• SQL Server Trigger to check if value has changed using Update()
• SQL Server Trigger to check if value has changed using COUMNS_UPDATED()
• SQL Server Trigger to check if value has changed using INSERTED & DELETED

SQL Server Trigger to check if value has changed using Update()

Here we will discuss how to define a trigger in SQL Server to check if value has changed using the UPDATE(). Let us first understand what is UPDATE() function in SQL Server.

Overview of UPDATE() function in SQL Server

The UPDATE() function in SQL Server is utilized to return a boolean value as a result. This boolean value indicates if an INSERT or UPDATE operation is performed on a particular column of a SQL Server Table.

Now, we can use this UPDATE() function anywhere in the INSERT or UPDATE SQL Server Trigger. Here is the basic syntax of using the UPDATE() function in SQL Server.

UPDATE( col_name )   

Here col_name is used to specify the name of the column that we want to examine either for the INSERT or the UPDATE operation. Moreover, we can specify any valid column type from SQL Server. But, computed columns are not accepted in this case.

Example: SQL Server Trigger to check if value has changed using Update()

Now that we got an idea of how to use the UPDATE() function, let us move to the next step and understand how to use this function in SQL Server Trigger. For the example demonstration, we are going to use the Countries table.

The Countries table in SQL Server holds some mistyped values. For example, the alpha code for the United States is TS instead of US. Also, the country name Canada is mistyped as Kanada. Here is the sample Countries table.

id	name	alpha-2-code
1	Austria	AT
2	United States	TS
3	United Kingdom	GB
4	Kanada	CA
5	New Zealand	NZ

So, here we will create a trigger in SQL Server that will check and inform which column value is changed. Here is the T-SQL code for the SQL Server Trigger.

USE GeoNames
GO

CREATE TRIGGER trgCountriesColumnTest 
ON dbo.Countries

AFTER UPDATE 
AS
BEGIN 
	IF UPDATE(id)
	BEGIN
		;THROW 51000, 'Cannot update the primary key', 1;  
	END
 
	IF UPDATE(name)
	BEGIN
		PRINT 'Update operation performed on Name column'
	END
 
	IF UPDATE([alpha-2-code])
	BEGIN
		PRINT 'Update operation performed on apha-2-code column'
	END
END
GO

In the above example, we created an after trigger in SQL Server named trgCountriesColumnTest. In this trigger, we have used the UPDATE() function with the IF statement to check which column is been changed.

Now, based on the result of the IF UPDATE() statement, the trigger will print a statement specifying which column is been updated.

Next, let us update the mistyped values of the Countries table using the UPDATE statement.

USE GeoNames
GO

UPDATE dbo.Countries
SET [alpha-2-code] = 'US'
WHERE id = 2

UPDATE dbo.Countries
SET name = 'Canada'
WHERE id = 4

After executing the above update statement, we will get the following result in SQL Server.

So, by the end of this SQL Server section, we got a clear idea of how to define the SQL Server trigger to check if value has changed using Update().

Read: Trigger For Delete in SQL Server

SQL Server Trigger to check if value has changed using COLUMNS_UPDATED()

In this section, we will take a look at another method of defining a SQL Server Trigger to check if value has changed using the COLUMNS_UPDATED() function.

Overview of COLUMNS_UPDATED() function

In the previous section, we have seen how we can use the UPDATE() function in SQL Server. The UPDATE() is used to determine the INSERT and UPDATE operation only for a single column at a time.

However, there is another function in SQL Server that we can use for multiple columns.

The COLUMNS_UPDATED() function in SQL Server is used to determine if multiple columns in a table or view have been added or changed. It returns a VARBINARY stream that we can test for various columns by using a bitmask.

The COLUMNS_UPDATED() function is used to return one or more than one byte arranged in left-to-right order. Moreover, each byte’s least important bit is the one on the right.

The first column of the table is represented by the rightmost bit of the leftmost byte, followed by the second column by the bit immediately to the left, and so on.

Example: SQL Server Trigger to check if value has changed using COLUMNS_UPDATED()

Now that we got an idea of what the COLUMNS_UPDATED() function is in SQL Server. Let us look at a simple example to define a trigger in SQL Server to check if value has changed or not.

The T-SQL code for the example is given below.

USE GeoNames
GO

CREATE OR ALTER TRIGGER trgCountriesColumnTest 
ON dbo.Countries

AFTER UPDATE 
AS
BEGIN 
	IF COLUMNS_UPDATED() = 0x01
	BEGIN
		;THROW 51000, 'Cannot update the primary key', 1;  
	END
 
	IF COLUMNS_UPDATED() = 0x02
	BEGIN
		PRINT 'Update operation performed on Name column'
	END
 
	IF COLUMNS_UPDATED() = 0x04
	BEGIN
		PRINT 'Update operation performed on apha-2-code column'
	END
END
GO

In the above example, we have defined a trigger that uses the IF COLUMNS_UPDATED() statement to check which column is updated. The overall functionality of this trigger is also the same as it will indicate the column name as a result.

So, just like in the previous example, if we perform an update on different columns of the Countries table, we will get the following result.

So, in this section, we have understood how we can use the COUMNS_UPDATED() function. And understand how to define a SQL Server Trigger to check if value has changed.

Read: How to execute Trigger in SQL Server

SQL Server Trigger to check if value has changed using INSERTED & DELETED

Now, there is a common issue with the UPDATE() and COLUMNS_UPDATED() functions in SQL Server. Both functions check if the UPDATE operation is performed or not. But these functions will never rectify if the actual value is changed or not.

Issue with UPDATE() and COLUMNS_UPADTED()

Now, if we use any of the previous examples and try to use the UPDATE statement without changing the actual value. In this case, the trigger will still indicate the column name even if there is no actual change in the table.

Here is an example of this execution in SQL Server.

Solution

Now, let us discuss how we can overcome this issue and understand another method to check if a value is changed in SQL Server using a trigger. To solve this issue in a SQL Server Trigger, we can use the INSERTED and DELETED magic tables.

Moreover, using these magic tables we can fetch the information related to each record that is modified. But before we create a trigger in SQL Server, we need to create a log table for the Countries table.

USE GeoNames
GO

CREATE TABLE dbo.CountriesLog
(
	log_id INT IDENTITY(1,1) PRIMARY KEY,
	country_id INT
);

After defining the log table, we will use the INSERTED, DELETED, and CountriesLog tables in the trigger to check which value is modified. Here is the SQL code for the trigger in SQL Server.

USE GeoNames
GO

CREATE OR ALTER TRIGGER trgCountriesLog 
ON dbo.Countries

AFTER UPDATE 
AS
BEGIN 
	INSERT INTO dbo.CountriesLog(country_id)
	SELECT updated.id
	FROM
	(
		SELECT * FROM INSERTED
		EXCEPT
		SELECT * FROM DELETED
	) updated
END
GO

In the above example, we have defined a trigger named trgCountriesLog. This trigger will insert the country id of a record in the CountriesLog table whose value is changed.

Now, after defining this trigger, if we use the UPDATE statement to update values to the same one, it will not insert any record in the log table.

The trigger will insert only those records in the log table whose value is changed from the previous one. Here is an example of this task in SQL Server.

So, in this SQL Server, we have seen how to define a SQL Server Trigger to check if value is changed using INSERTED and DELETED tables.

Read: SQL Server Trigger On View

Conclusion

At the end of this SQL Server tutorial, we got a clear understanding of how to define a trigger in SQL Server to check if value id changed from a table. Additionally, we have covered 3 different methods in SQL Server for this particular task using examples.

These are the set of examples that we have covered in this tutorial.

• SQL Server Trigger to check if value has changed using Update()
• SQL Server Trigger to check if value has changed using COUMNS_UPDATED()
• SQL Server Trigger to check if value has changed using INSERTED & DELETED

You may also like to read the following SQL Server tutorials.

• SQL Server Trigger to Increment Id
• How to Debug an SQL Server Trigger
• SQL Server Trigger If Exists Raise Error