UPDATE TRIGGER fires only to 1 record on an update query

Hi,
I have an update trigger to a table but when I run an update query to the table and affect more than one records then the trigger fires only to one record not to all records!!! Do you know how to do it to fire for all affected records?
Thanks
nonlinearlyAsked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copying the trigger T-SQL code into this question would greatly help us give you an answer.
0
nonlinearlyAuthor Commented:
I have attached a file with the trigger... But I thought was a trivial issue... just some option that switch the trigger to one or the other direction...
trigger.sql
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
All of the Declare's are scalar variables, which by definition can hold only one value.

The code block below, even though it joins inserted to another table, are setting column values to scalar variables.
 select 
  @ROW_ID          = I.ROW_ID ,
  @ADDR_NUM        = I.ADDR_NUM ,
  @ADDR            = I.ADDR,
  @STATE           = I.STATE ,
  @COUNTY          = I.COUNTY ,
  @ZIPCODE         = I.ZIPCODE ,
  @ATTRIB_40       = X.ATTRIB_40 ,
  @ATTRIB_08       = X.ATTRIB_08
 FROM inserted I , S_ADDR_ORG_X X
 WHERE X.PAR_ROW_ID = I.ROW_ID   

Open in new window

The UPDATE statements have   --> where   ROW_ID = @ROW_ID.
So it appears the developer's intent was that only one row is to be updated.

If the intent is to have this trigger affect the entire set of updated rows, rewrite it to lose the scalar variables.
0
Scott PletcherSenior DBACommented:
I believe the code below will do what you wanted, but using set processing rather than row-by-row.

Btw, SQL Server does NOT support "ROW" level triggers; instead:
ALL rows affected by a SQL statement are made available to the trigger in ONE trigger invocation, in (pseudo)tables "inserted" (not applicable for DELETE triggers) and "deleted" (not applicable for INSERT triggers).



ALTER  TRIGGER [dbo].[trg]
ON [dbo].[table_test]
AFTER INSERT , UPDATE
AS
SET NOCOUNT ON

IF UPDATE (ADDR)
OR UPDATE (ADDR_NUM)
OR UPDATE (COUNTY)
OR UPDATE (STATE)
BEGIN
    UPDATE sao
    SET
        DFLT_SHIP_PRIO_CD = soe.DFLT_SHIP_PRIO_CD
    FROM inserted i
    INNER JOIN dbo.S_ADDR_ORG sao ON
        sao.ROW_ID = i.ROW_ID
    CROSS APPLY (
        SELECT dbo.GetAddressID (i.ADDR, i.COUNTY, i.ADDR_NUM, i.ZIPCODE) AS ATTRIB_04
    ) AS ca1
    LEFT OUTER JOIN dbo.S_ORG_EXT soe ON
        soe.ROW_ID = ca1.ATTRIB_04
    WHERE
        i.ATTRIB_08 IS NULL OR
        i.ATTRIB_08 = 'N' OR
        (i.ATTRIB_08 = 'Y' AND i.ATTRIB_40 IS NULL)
       
    UPDATE saox
    SET
        ATTRIB_40 = soe.NAME,
        ATTRIB_04 = ca1.ATTRIB_04
    FROM inserted i
    INNER JOIN dbo.S_ADDR_ORG_X saox ON
        saox.PAR_ROW_ID = i.ROW_ID
    CROSS APPLY (
        SELECT dbo.GetAddressID (i.ADDR, i.COUNTY, i.ADDR_NUM, i.ZIPCODE) AS ATTRIB_04
    ) AS ca1
    LEFT OUTER JOIN dbo.S_ORG_EXT soe ON
        soe.ROW_ID = ca1.ATTRIB_04
    WHERE
        i.ATTRIB_08 IS NULL OR
        i.ATTRIB_08 = 'N' OR
        (i.ATTRIB_08 = 'Y' AND i.ATTRIB_40 IS NULL)

END --IF
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
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.