sql after update trigger

Below is an AFTER UPDATE TRIGGER.  The actual trigger includes 32 columns.  the trigger works, but is there a way to only update the columns that have been changed?  One option is to use a WHERE clause of:
WHERE  (isnull(Ins.ColumnName,'') <> isnull(Del.ColumnName,'')).  
I assume I would have to put this where statement in for each of the 32 columns and have 32 queries.  Is there another way?

UPDATE Visit SET
PerID =      Ins.PerID,
PatFName = Ins.PatFName,
...  30 other column names
FROM  Visit
INNER JOIN Inserted As Ins ON Visit.[VisitID] = Ins.[VisitID]
dastaubAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
I'm not sure why you care.  If its the same value that's already there, nothing happens.  Are you seeing values getting updated that shouldn't be updated?
0
nemws1Database AdministratorCommented:
I guess you could use a CASE statement for this in a single (if complex) query:

UPDATE Visit SET
PerID = CASE WHEN (ISNULL(Ins.PerID, '') <> ISNULL(Del.PerID, ''))
        THEN Ins.PerID
        ELSE Del.PerID
        END
, PatFName = CASE WHEN (ISNULL(Ins.PatFName, '') <> ISNULL(Del.PatFName, ''))
       THEN Ins.PatFName
       ELSE Del.PatFName
       END
...  30 other column names
FROM  Visit
INNER JOIN Inserted As Ins ON Visit.[VisitID] = Ins.[VisitID] 

Open in new window


If you're running SQL 2012 on, you can shorten this a little:
UPDATE Visit SET
PerID = IIF((ISNULL(Ins.PerID, '') <> ISNULL(Del.PerID, '')), Ins.PerID, Del.PerID)
, PatFName = IIF((ISNULL(Ins.PatFName, '') <> ISNULL(Del.PatFName, '')), Ins.PatFName, Del.PatFName)
...  30 other column names
FROM  Visit
INNER JOIN Inserted As Ins ON Visit.[VisitID] = Ins.[VisitID] 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dastaubAuthor Commented:
When a column is not changed, what is the content of the INSERTED table column for the unchanged column?  is that column blank or contain the original value in the underlying table?

>> I'm not sure why you care.  If its the same value that's already there, nothing happens.  Are you seeing values getting updated that shouldn't be updated?  =  I was assuming it may make the process faster by not updating a column with the identical value that is already there.

is does appear that:

IIF((ISNULL(Ins.PerID, '') <> ISNULL(Del.PerID, '')), Ins.PerID, Del.PerID)

does still cause an update regardless of a change or no change to the original value?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nemws1Database AdministratorCommented:
Sorry, I was thinking of a normal updat where you're doing something like this:

Update table
Set field1='value'

Where field1 is *already* set to 'value'. In that case, update really doesn't do anything.

In your example, what would some of the values be for Ins.Perid versus Del.Perid when the trigger is triggered?
0
dastaubAuthor Commented:
>> In your example, what would some of the values be for Ins.Perid versus Del.Perid when the trigger is triggered?

PerID is a unique number that identifies a Person and that column would not change.  Most of the time, maybe 3 of the 32 columns would change and the next UPDATE a different set of 3 or so columns would be updated.  Almost never would all 32 columns change at the same time.  In its current format, the PerID would be updated with the same PerID it currently has.  it would change PerID of 13200 to PerID to 13200 because of no change.

This trigger is a "catch all" for catching any update to the table and sending that update to the second table.
0
nemws1Database AdministratorCommented:
Gotcha. Have you tried the CASE statement query? I think it will work for you (back up your database first, though, just in case!)
0
dastaubAuthor Commented:
Using the above suggested CASE statement query in the trigger requires both the Deleted and Inserted table.  Is the below the best practice for joining the 3 necessary tables?

UPDATE Visit SET
Name = CASE WHEN (ISNULL(Ins.FName, '') <> ISNULL(Del.FName, '')) THEN Ins.FName ELSE Del.FName END,
MRN = CASE WHEN (ISNULL(Ins.MRN, '') <> ISNULL(Del.MRN, '')) THEN Ins.MRN ELSE Del.MRN END,
...
FROM  Visit
INNER JOIN Inserted As Ins ON Visit.[VisitID] = Ins.[VisitID]
INNER JOIN Deleted As Del ON Del.[VisitID] = Ins.[VisitID]
0
Scott PletcherSenior DBACommented:
SQL Server itself has built-in code to short-circuit logging columns where the value doesn't actually change (as long as you don't specify a clustering key column name, since key column update requires a DELETEs and INSERT, not an UPDATE; also, LOB data may or may not cause activity).  Therefore, just code a straightforward UPDATE, but avoid specifying key column(s) as long as they can never change.  Thus, checking all the columns is far more overhead than it's worth.


UPDATE v
SET
    --PerID *never* changes, and specifying it might cause lots of extra activity in SQL Server
    PatFName = i.PatFname,
    ...
FROM inserted i
INNER JOIN deleted d ON
    d.VisitID = i.VisitID
INNER JOIN dbo.Visit v ON
    v.VisitID = i.VisitID
0
dastaubAuthor Commented:
i understand not specifying columns that do not change, but not clear on SQL's ability to not update fields that are the same even though they are included in the trigger?

if I specify to update firstname, lastname, and middle initial in the trigger and the middle initial is updated in the underlying table, the way I have it now all 3 columns will be updated by the trigger because they are all in the update trigger or are you saying SQL knows only to update the middle initial?
0
Scott PletcherSenior DBACommented:
Yes.  SQL itself does checks to avoid wasting time updating columns where the values haven't actually changed.
0
Anthony PerkinsCommented:
On a somewhat related note, contrary to popular belief, the IF UPDATE (columnanme) does not report if a value has changed, but rather that the column was affected.  In other words if a column is in an UPDATE statement than IF UPDATE() will always be true regardless of whether the value(s)  actually changed or not.  The only way to know if the values changed is to compare the results in the INSERTED and DELETED tables.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.