Post reply

Re-write trigger to handle multiple rows

  • March 8, 2006 at 5:36 pm

    #93659

    I´m new to triggers and I need to re-write the one below and make sure it can handle multiple rows into table3. This code fires only one row.

    CREATE TRIGGER InsPeriods ON dbo.table2FOR INSERTASdeclare@indice int,@itemID int,@periods int,@payment_date datetime,@period_value decimal(15,2)select @periods=periods, @period_value=total/periods, @paymentdate=startpayment, @itemID=itemID from insertedset @indice = 1while @indice <= @periodsbegininsert dbo.table3 (itemID, installment, periodvalue, paymentdate)values (@itemID, @indice, @paymentvalue, DATEADD(month,@indice-(1),@paymentdate))set @indice= @indice+1end

  • March 8, 2006 at 8:57 pm

    #625582

    I think something like this will work for you:

    CREATE TRIGGER InsPeriods ON dbo.table2

    FOR INSERT

    AS

    insert into dbo.table3 (itemID, installment, periodvalue, paymentdate)

    select

      INSERTED.itemID,

      INSERTED.installment,

      --avoid division by zero or NULL Values in the calculation; should zero even be inserted? it's up to your business rules.

      case when isnull(INSERTED.period,0) = 0 then 0

      else isnull(INSERTED.total,0) / isnull(INSERTED.periods,0)

      end,

      INSERTED.startpayment

    from INSERTED

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

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