SQL Server, how to detect if the values of a column have changed

Hi Guys,

Welcome back!

Today we have a little insight into the world of triggers.
We will see in which ways to detect if the values ​​of a column have changed

Ready? Go!

The UPDATE function

In a trigger, the UPDATE (FIELD) function is the fastest way to check whether the value of the <FIELD> field has changed.

It is faster because it only parses the T-SQL command to be executed even if the field value is not actually changed.

Example:

       

UPDATE TABELLA SET FIELD = FIELD

       
 

always return TRUE

Reading the tables INSERTED and DELETED

Alternatively, to check if the value of a field has been changed, you can query the two tables INSERTED and DELETED by putting them in JOIN:

       

IF EXISTS( SELECT I.ID FROM INSERTED I JOIN DELETED D ON I.ID = D.ID WHERE I.FIELD <> D.FIELD)
BEGIN

END 
       
 

That's all for Today!

Luca

You may also be interested in:

• SQL Server: Transazioni, Lock e Deadlock. Un po di teoria spiegata in modo semplice!
  
• SQL Server, How to find deadlocks: the easy way DBCC TRACEON (1204, 1222,-1)
  
• Identificare la versione di SQL server? Semplice!
  
• SQL Server, find text in a Trigger, Stored Procedures, View and Function. Two ways and what ways is better

Previuos post:

• How SQL Server fills mdfs and ldf with data: Proportional fill and Round robin