Trigger/Update insterted row with sum values

  • Hi.  

     

    I have two tables in SQL Server 7.0.

     

    Table One (Primary index is Customer, Date, RefNo)

     

    Customer

    Date

    RefNo

    TotalValue

     

    Table Two (Primary index is Customer, Date, RefNo, AutoSeqNumber)

     

    Customer

    Date

    RefNo

    AutoSeqNumber

    Value

     

    At the time that the table one row is inserted, the corresponding rows in table two already exist (changing the order of these events is not an option ).   So what I need to do is have a trigger that fires on the insert of a row into table one, sums the Values in table two for the matching customer, date, refno index and put the result into the ‘TotalValue’ row in Table One.

     

    Any help greatly appreciated.

  • Huh, Primary Index? You mean Clustered Index!

    You have mentioned "So what I need to do is have a trigger that fires on the insert of a row into table one, sums the Values in table two for the matching customer, date, refno index and put the result into the ‘TotalValue’ row in Table One." However, Index? Index is not a column in your Tables!

    Could we clarify the above?


    Kindest Regards,

  • Thanks for replying.  By Primary I mean unique.

    To clarify:

    1). the unique index in table one is customer, date, refno.

    2). the unique index in table two is customer, date, refno, AutoSeqNumber

    Lets say table 2 contains the following rows:

    - Jones,02/02/03,4567,50

    - Smith,02/02/03,1234,1,50

    - Smith,02/02/03,1234,2,100

    If I insert a row into table one with the values "smith,02/02/03,1234,0", I want to interogate table two for entries that match "smith,02/02/03,1234", sum the values and update TotalValue in table one with the sum.  In this case, I want to update the TotalValue for "smith,02/02/03,1234"  in table one with 150.

    Thanks,  Jim

     

  • I haven't unit tested this but give it a go!

    CREATE TRIGGER SumValue ON XYZ1

    FOR INSERT

    AS

    DECLARE @CustomerID INTEGER

    DECLARE @Date DATETIME

    DECLARE @TotalValue INTEGER

    DECLARE @CustomerID2 INTEGER

    DECLARE @Date2 DATETIME

    DECLARE @Value INTEGER

    SELECT @CustomerID = CustomerID, @Date = [Date], @TotalValue = TotalValue

    FROM XYZ1 A INNER JOIN Inserted I ON A.CustomerID = I.CustomerID

    SELECT @CustomerID2 = CustomerID, @Date2 = [Date], @Value = Value

    FROM XYZ2

    WHERE XYZ2.CustomerID = @CustomerID

    IF @CustomerID2 = @CustomerID AND @Date2 = @Date AND @Value = @TotalValue

      BEGIN

     SELECT CustomerID, SUM(Value) AS 'Total Value'

     FROM XYZ2

     WHERE XYZ2.CustomerID = @CustomerID2

     GROUP BY CustomerID

      END


    Kindest Regards,

  • How about this


    set nocount on
    create table table1(
       Customer varchar(10) not null,
       [Date] datetime not null,
       RefNo int not null,
       TotalValue int null,
       constraint pk_customer1 primary key (Customer, [Date], RefNo)
    )

    create table table2(
       [Customer] varchar(10) not null,
       [Date] datetime not null,
       RefNo int not null,
       [AutoSeqNumber] int not null,
       Value int not null,
       constraint pk_customer2 primary key ([Customer], [Date], RefNo, AutoSeqNumber)
    )
    go

    create trigger table1_ins on table1 for insert
    as
    update t1 
       set TotalValue = s.TotalValue
    from 
       table1 t1 inner join
          (select 
             t2.Customer,  
             t2.[Date], 
             t2.RefNo, 
             Sum(t2.Value) as TotalValue
          from
             table2 t2 inner join inserted i on
                   t2.Customer = i.Customer
               and t2.[Date]   = i.[Date]
               and t2.RefNo    = i.RefNo
          group by
             t2.Customer,  
             t2.[Date], 
             t2.RefNo) s on
          t1.Customer = s.Customer
      and t1.[Date]   = s.[Date]
      and t1.RefNo    = s.RefNo
    go

    insert table2 values('Jones','02-feb-2003',4567,1,50)
    insert table2 values('Smith','02-feb-2003',1234,1,50)
    insert table2 values('Smith','02-feb-2003',1234,2,100)

    insert table1 values('Smith','02-feb-2003',1234,0)

    select * from table1

    drop table table1
    drop table table2
    

    Output:

    Customer   Date                                                   RefNo       TotalValue  
    ---------- ------------------------------------------------------ ----------- ----------- 
    Smith      2003-02-02 00:00:00.000                                1234        150


    --
    George

  • The trigger fires once even if 0, 1 or many records are affected.  Using local variables is not good - please, no cursor in a trigger - in this case because you assume one record has been affected.  If this must be the case, then an error should be raised before data corruption occurs.

    DECLARE @Rowcount int

    SET @Rowcount = @@ROWCOUNT

    IF @Rowcount = 0 RETURN -- nothing to do (e.g., update ... where 1=0)

    IF @Rowcount > 1 BEGIN

    RAISERROR('Sorry, the trigger is unable to handle more than one record in a single update.  Kick your programmer - softly because of their great error handling.', 16, 1)

    ROLLBACK

    RETURN

    END

    etc

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • rstone,

    hope you're referring to MrSql post, not mine - my version should work for 0, 1 or 10000 rows being inserted (with minor modifications it will work for updates as well). We were bitten really bad with multi-row updates and I learned my lesson

    cheers,


    --
    George

  • rstone,

    You are correct. I forgot about multi row updates inside a trigger.


    Kindest Regards,

  • georgedo,

    I was indeed.  Your trigger, without local variables and using group by, is a clear sign you are multirow aware.  And the group by is a clear sign you know what you are doing.  Experience, even bad, can be good. 

    What the developers often don't realize, as they curse you under their breath, is that any trigger that is not multirow ready limits a DBAs ability to quickly save their butts without a full restore from backup.  It's not easy to fix mistakes one record at a time. 

    Also, a good trigger might be used as a utility if applied to the entire table.  Its logic might be adapted using the original tables (not inserted or deleted) to create a query to redo the calculations on all records in a batch - should it be required due to a logic change or corruption. 

    Randy

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • rstone,

    Can you give us a solution to this ? How would you write it?

    This is indeed not your average trigger!


    Kindest Regards,

  • MrSQL,

    average trigger it is not but trickiest part, IMO, is not multirow update - it's the fact that aggregates cannot be used as update values hence the subselect with grouping.

    Mind you, just looked at the code I wrote - if this trigger is created for update as well as insert then you'll have, as rstone has mentioned, a powerful update utility. You can issue statement like

    update table1 set customer = customer

    and it will in fact recalculate all TotalValues. Just don't try it with 1000000 rows

    Cheers


    --
    George

  • I would start with georgedo's solution.  You can test the guts of the query on the entire table.  Replace the inserted table with the original trigger table.

          select -- perhaps a top 100 here

             t2.Customer, 

             t2.[Date],

             t2.RefNo,

             Sum(t2.Value) as TotalValue

          from

             table2 t2 inner join table1 i on

                   t2.Customer = i.Customer

               and t2.[Date]   = i.[Date]

               and t2.RefNo    = i.RefNo

          group by

             t2.Customer, 

             t2.[Date],

             t2.RefNo

    If the result look good, then the "utility" would be

    update t1

       set TotalValue = s.TotalValue

    from

       table1 t1 inner join

          (select

             t2.Customer, 

             t2.[Date],

             t2.RefNo,

             Sum(t2.Value) as TotalValue

          from

             table2 t2 inner join table1 i on

                   t2.Customer = i.Customer

               and t2.[Date]   = i.[Date]

               and t2.RefNo    = i.RefNo

          group by

             t2.Customer, 

             t2.[Date],

             t2.RefNo) s on

          t1.Customer = s.Customer

      and t1.[Date]   = s.[Date]

      and t1.RefNo    = s.RefNo

    You can wrap this in a begin tran - rollback and use selects, during the tran, to check results.  Don't do this in production.  The same begin tran - rollback can be used to test the trigger too.  The utility script is also a good place to see if performance is a problem. 

    If your utility works, then the trigger will also, provided you remember to used the inserted or deleted table where needed.  Do not update the whole table in the trigger!  If you start to see deadlocks, look for this. 

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • The select portion of the query used is the trigger is often the difficult part.  It can be developed and tested in QA before the trigger is even started. 

    If the same set of keys can be in the inserted table in multiple records, then a distinct set derived from the inserted table might be required first to prevent getting too many records in the join.  This is not the case here since the keys are unique in table1.  This is not the case if you need this sort of trigger on table2.  In that case, select a distinct set of keys for table1 from inserted (table2) first. 

     

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • Thanks everyone for your help.   I've implemented Georgedo's solution and it works fine.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply