?
Solved

sql after update trigger

Posted on 2014-08-27
11
Medium Priority
?
532 Views
Last Modified: 2014-08-29
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]
0
Comment
Question by:dastaub
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 40289530
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
 
LVL 23

Accepted Solution

by:
nemws1 earned 668 total points
ID: 40289540
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
 

Author Comment

by:dastaub
ID: 40289548
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 23

Expert Comment

by:nemws1
ID: 40289554
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
 

Author Comment

by:dastaub
ID: 40289564
>> 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
 
LVL 23

Expert Comment

by:nemws1
ID: 40289567
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
 

Author Comment

by:dastaub
ID: 40289671
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 668 total points
ID: 40291198
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
 

Author Comment

by:dastaub
ID: 40291626
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40291954
Yes.  SQL itself does checks to avoid wasting time updating columns where the values haven't actually changed.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 664 total points
ID: 40293312
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question