February 27, 2009 at 7:48 am
The following update statement:
Update company_ext_attributes
set ExtValue603 = AprimoRTSMappings.Region,
ExtValue604 = AprimoRTSMappings.Territory,
ExtValue312 = AprimoRTSMappings.SubCode
from AprimoRTSMappings,companies,company_ext_attributes
where companies.company_id=company_ext_attributes.company_id
and company_ext_attributes.ExtValue301 IN ('OED','EID')
and companies.country=AprimoRTSMappings.country
and ISNULL(AprimoRTSMappings.state,'A') =
Case
when companies.country <> 'USA' then ISNULL(AprimoRTSMappings.state,'A')
else companies.state
end
AND companies.company_id in (797039,846066,926631,819017,825779,916530,874446,835550)
returns:
(1 row(s) affected)
(4 row(s) affected)
Whereas the 'select' version this query only returns 4 rows.
What can the first (1 row affected) be?
Thanks for the help,
Bill
February 27, 2009 at 7:51 am
I think you have probably turned on "include actual execution plkan" in SSMS
When on it returns an extra result set .. OR there is a trigger in there that fires and updates one row.
Mike
February 27, 2009 at 7:59 am
When I turn on Include Actual Execution Plan, I get;
(4 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
There is an update trigger:
But is suggests all 4 rows should be updated leading to 2 messages of (4 row(s) affected) each.
Bill
February 27, 2009 at 8:00 am
Check triggers on company_ext_attributes
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 27, 2009 at 8:29 am
Here is the trigger. When I reviewed this, I concluded that each of the 4 rows would have been updated, therefore the additional message would have 4 affected rather that 1 affected.
I had intended to include the trigger in the previous reply - sorry.
ALTER Trigger [dbo].[AGS_InsertUpdateCompanyExtAttributes] ON [dbo].[company_ext_attributes]
AFTER INSERT, UPDATE
AS
BEGIN
Declare @SFDC_AcctOwnerID nvarchar(400)
, @SFDC_AccountOwnerName nvarchar(255)
, @CompanyID int
IF @@RowCount > 0
BEGIN
SELECT @CompanyID = company_id FROM inserted
if (@CompanyID is null)
SELECT @CompanyID = company_id FROM updated
--Grab the SFDC Account Owner ID (represents a SFDC Login ID)
SELECT @SFDC_AcctOwnerID = ExtValue227
FROM company_ext_attributes
WHERE company_id = @CompanyID
--Grab the Participant name that is tied to a SFDC User, via the Partipant's Owner Key (EA ID: )
SELECT @SFDC_AccountOwnerName = RTRIM(LTRIM(isNull(first_name + ' ', '') + isNull(last_name,'')))
FROM participant_ext_attributes pea
INNER JOIN participants p
ON p.participant_id = pea.participant_id
WHERE pea.ExtValue901 = @SFDC_AcctOwnerID
UPDATE company_ext_attributes
SET ExtValue239 = @SFDC_AccountOwnerName
WHERE company_id = @CompanyID
END
END
February 27, 2009 at 8:44 am
That trigger will not work correctly with multiple rows. Common mistake of retrieving something from inserted into a variable - so you get one of them. There are lots iof examples around on how to write triggers that handle multi-row updates, but essentially you need to ensure ALL rows in inserted and deleted are dealt with (preferably not using a cursor!)
Mike
February 27, 2009 at 8:59 pm
SELECT @CompanyID = company_id FROM updated
Your code contains the line above... there's no such thing as an UPDATED table inside the trigger. Do you have a table outside the trigger called UPDATED?
Trigger tables are named INSERTED and DELETED... there is no UPDATED trigger table.
--Jeff Moden
March 3, 2009 at 10:00 am
Very true. To see what rows are updated you refer to the DELETED table for the old values and INSERTED for the new values.
Cheers,
Brian
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply