• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

if update

i see the below segment in the update trigger..

does it mean if  payslip_code column is updated in the original update statement, then the below segment will be executed? (if other columns are updates, then the below segment will not be executed?)
if update(payslip_code)  
            begin  
               <code...>
            end  
0
25112
Asked:
25112
6 Solutions
 
devilJinKazamaCommented:
yep u got it right.

whichever table its set on, the code says - if column payslip_code is updated then run the code below.

payslip_code has to be updated to run this code, other columns won't execute the code
0
 
Anthony PerkinsCommented:
Actually no.  You cannot infer that the data was actually modified (otherwise the clause may have been called IF UPDATED() and not IF UPDATE() ).  What it does do is tell you that the column participated in UPDATE statement.  The only true way to discover if the data has actually  changed is by comparing the values in the columns of the DELETED and INSERTED logical tables.
0
 
Anthony PerkinsCommented:
But don't take my word for it, do something like this:
UPDATE YourTableName
SET YourColumn = YourColumn
WHERE SomeConditionGoesHere

And you will see that the IF UPDATE() is true, even though the value obviously did not change.  Yes, I do realize this example is silly, but I hope you get my drift.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if UPDATE(column name) will tell you if "at least" that column was part of the UPDATE ... SET column_name = <expression>
as indicated by acperkins: it will NOT tell you if the value actually changed !
0
 
Anthony PerkinsCommented:
>>as indicated by acperkins: it will NOT tell you if the value actually changed !<<
And I will confess I learned that from you.
 
0
 
25112Author Commented:
so what the first expert said was partially true..

what angel and acp have confirmed is that: IF that column was at least a part of that update.. are you meaning it was one of the SET columns.. not in the FROM or WHERE part, right?
0
 
Anthony PerkinsCommented:
Do you have access to SQL Server's BOL.  This is what it has to say about IF UPDATE()
The UPDATE() function can be used to determine if an INSERT or UPDATE statement affected a specific column in the table. The function returns TRUE whenever the column is assigned a value.
...
Alternatively, the COLUMNS_UPDATED function can be used to check which columns in a table were updated by an INSERT or UPDATE statement. This function uses an integer bitmask to specify the columns to test. For more information, see CREATE TRIGGER.

0
 
25112Author Commented:
ok..

i better install it also.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now