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!
One thought on “Update() Trigger Function in Table”