Update() function returns “True” in any case the Insert/update attempt is successful on the supplied column, the significance of this function is to trigger the action/code only if the supplied column is get updated or inserted successfully.

In the below example, I’ve used the Update() function for identifying any changes in “CodeValue” column of table “Configtbl” and ignored the column changes if its value is same.

“Configtbl_Audit” table will get loaded based on the changes in “CodeValue” column in “Configtbl”

drop table if exists Configtbl
drop table if exists Configtbl_Audit

create table Configtbl(
	 Code nvarchar(10) not null,
	 CodeValue decimal(10,2) not null
)
insert into Configtbl(Code,CodeValue) values ('VA1111',25.99),('VA1118',19.99)

create table Configtbl_Audit(
	 Code nvarchar(10) not null,
	 Old_Value decimal(10,2) not null,
	 New_Value decimal(10,2) not null,
	 DateModified datetime not null
)
 
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'TRG_UPD_NEW_OLD_VALUE' AND type = 'TR')  
   DROP TRIGGER TRG_UPD_NEW_OLD_VALUE 
GO 

CREATE TRIGGER TRG_UPD_NEW_OLD_VALUE  
ON Configtbl
FOR UPDATE   
AS   

declare @oldvalue decimal(10,2),@newvalue decimal(10,2), @Code nvarchar(10)
IF (UPDATE (CodeValue))  
BEGIN
	select @oldvalue = CodeValue , @Code = Code from deleted

	insert into Configtbl_Audit(Code, Old_Value, New_Value, DateModified)
	select @Code, @oldvalue, CodeValue, getdate()
	from inserted 
	where code=@Code
	and @oldvalue <> CodeValue

END;  
GO 

Output:-

View of Data in Configtbl and Configtbl_Audit tables before doing any updates:

After updating “Code” column in Configtbl table:

After updating “CodeValue” column in Configtbl table:

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!