Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I get my SQL trigger to update only the row I'm updating?

Posted on 2011-10-23
6
Medium Priority
?
193 Views
Last Modified: 2012-06-27
I have an SQL trigger which I want to only update the row I'm updating but it updates every row.

CREATE TRIGGER [afm].[IFM_PopulateLocCode] ON [afm].[rm]
AFTER INSERT, UPDATE
AS

UPDATE rm
SET loc_code = rtrim(rtrim(site.site_id)+rtrim(rm.build_no)+rtrim(rm.fl_id)+rtrim(rm.rm_id))

FROM         rm INNER JOIN
                      bl ON rm.bl_id = bl.bl_id INNER JOIN
                      site ON bl.site_id = site.site_id    

How do I get the trigger to only update the row which requires updating?

Cheers  
0
Comment
Question by:CurtinProp
  • 2
  • 2
5 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37015824
You need to JOIN to the INSERTED table and that should limit the updates to only those records that match the row being inserted or updated.
0
 
LVL 2

Author Comment

by:CurtinProp
ID: 37015849
Do you have an example of how to join to an inserted table as I am unsure how to?

Thanks
0
 
LVL 15

Expert Comment

by:Eyal
ID: 37015992
CREATE TRIGGER [afm].[IFM_PopulateLocCode] ON [afm].[rm]
AFTER INSERT, UPDATE
AS

UPDATE rm
SET loc_code = rtrim(rtrim(site.site_id)+rtrim(rm.build_no)+rtrim(rm.fl_id)+rtrim(rm.rm_id))

FROM         rm INNER JOIN
                      bl ON rm.bl_id = bl.bl_id INNER JOIN
                      site ON bl.site_id = site.site_id    
inner join inserted I on rm.id = I.id
0
 
LVL 15

Accepted Solution

by:
Eyal earned 1000 total points
ID: 37015996
oops... little fix

CREATE TRIGGER [afm].[IFM_PopulateLocCode] ON [afm].[rm]
AFTER INSERT, UPDATE
AS

UPDATE rm
SET loc_code = rtrim(rtrim(site.site_id)+rtrim(rm.build_no)+rtrim(rm.fl_id)+rtrim(rm.rm_id))

FROM         rm INNER JOIN
                      bl ON rm.bl_id = bl.bl_id INNER JOIN
                      site ON bl.site_id = site.site_id    
inner join inserted I on rm.rm_id = I.rm_id
0
 
LVL 2

Author Closing Comment

by:CurtinProp
ID: 37016085
Thanks for the solution. Worked perfectly.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

564 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