March 10, 2009 at 1:20 pm
Relatively new to creating triggers and having a real hard time updating multiple rows based on IF, Then, Else statements in my trigger.
I have a table that contains an ip address and a location. I want to update the location column based on the 3rd octet of my ip address column any time the ip address is updated. Problem is, it updates the location for every record regardless of the ip being updated or taking into account it's corresponding Ip address. Ie...if I update an ip with a 3rd octet of 13, it will update every other location row with the same value.
I am sure I am missing something obvious but I can't seem to get it to work.
Thanks in advance for any assistance anyone can provide.
Here is the code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [Set_Location] ON [dbo].[usr_hardwareasset]
FOR UPDATE, INSERT
AS
BEGIN
if exists(select usr_Ipaddress from Database1.dbo.usr_hardwareasset where usr_IPAddress like '10.4.10.%')
update usr_hardwareasset
set usr_location = 'Some location1'
else
if exists(select usr_Ipaddress from Database1.dbo.usr_hardwareasset where usr_IPAddress like '10.2.8.%')
update usr_hardwareasset
set usr_location = 'Some location2'
else
if exists(select usr_Ipaddress from Database1.dbo.usr_hardwareasset where usr_IPAddress like '10.9.9.%')
update usr_hardwareasset
set usr_location = 'Some location3
End
March 10, 2009 at 1:24 pm
Inside triggers, there's a "table" called "inserted" that has the rows that have been updated. There's also one called "deleted" that has what they had before they were updated. (Insert triggers don't have the deleted table and delete triggers don't have the inserted table, but update triggers have both, if that makes sense.)
What you need to do is join your table to the inserted table and do your updates from that.
If you can post the table structure, I'm sure we can help with what the trigger should have in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2009 at 2:04 pm
TaylrJ (3/10/2009)
if exists(select usr_Ipaddress from Database1.dbo.usr_hardwareasset where usr_IPAddress like '10.4.10.%')update usr_hardwareasset
set usr_location = 'Some location1'
What you're saying there is if there are any rows at all (even 1) that have an IP address like that, update ALL rows.
You don't really need the exists, you need multiple updates with a where condition or a single update with a CASE WHEN...
UPDATE usr_hardwareasset
SET usr_location = 'Some location1'
FROM usr_hardwareasset INNER JOIN inserted on < Join on the pk column(s) >
WHERE usr_IPAddress like '10.4.10.%'
UPDATE usr_hardwareasset
SET usr_location = 'Some location2'
FROM usr_hardwareasset INNER JOIN inserted on < Join on the pk column(s) >
WHERE usr_IPAddress like '10.3.8.%'
UPDATE usr_hardwareasset
SET usr_location =
CASE
WHEN usr_IPAddress like '10.4.10.%' THEN 'Some location1'
WHEN usr_IPAddress like '10.2.8.%' THEN 'Some location2'
WHEN .... < Rest of the possibilities >
FROM usr_hardwareasset INNER JOIN inserted on < Join on the pk column(s) >
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2009 at 2:57 pm
Thank you for the replies.
I think I have a handle on it with one exception. I only want to update a record if it is inserted or updated. It looks like the way I have the trigger set up, it will update the whole table if only one record is changed or at least run the update statements.
The table structure is basically:
Cf_guid IPaddress Location
If an IP is inserted/updated, it should update location. It should only perform the update for the updated/inserted records instead of the whole table.
Here is the trigger based on the feedback. I may still be missing something.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [Set_Location] ON [dbo].[usr_hardwareasset]
FOR UPDATE, INSERT
AS
BEGIN
UPDATE usr_hardwareasset
SET usr_location = 'Some location1'
FROM usr_hardwareasset INNER JOIN inserted on inserted.cf_guid=usr_hardwareasset.cf_guid
WHERE usr_IPAddress like '10.4.10.%'
UPDATE usr_hardwareasset
SET usr_location = 'Some location2'
FROM usr_hardwareasset INNER JOIN inserted on inserted.cf_guid=usr_hardwareasset.cf_guid
WHERE usr_IPAddress like '10.3.8.%'
End
March 10, 2009 at 3:02 pm
TaylrJ (3/10/2009)
Thank you for the replies.I think I have a handle on it with one exception. I only want to update a record if it is inserted or updated.
Look back at Gus's reply.
As Gus said
Inside triggers, there's a "table" called "inserted" that has the rows that have been updated.
What you need to do is join your table to the inserted table and do your updates from that.
So the trigger you just posted will only update rows that have just been updated or inserted, because you're joining to the inserted table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2009 at 3:06 pm
I think my common sense left the building...or my reading comprehension is lacking today.
I understand now and I really appreciate all the help. I am good to go.
Thanks again to you both.
March 11, 2009 at 7:04 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply