Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: Trigger to Insert or Update 2nd Table

    I am new to triggers and need help on the following:

    I have a hourly table that inserts new rows every hour but I need to either Insert or Update the daily table with the sum of the reading from the hourly table. If a row exist in the daily table with the date of the hourly table, then I need to update this row but if it doesn't exist, I need to insert this row.

    Thanks for any suggestions...

    Alan

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    what columns are being updated\inserted in the hourly and daily tables
    and what columns do you want to derive a sum
    can you give a table description?

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    In the Hourly Table, I have RecNo, LctnNo, HrDateTime, Reading.
    In the Daily Table, I have RecNo, LctnNo, DlyDate, Reading.

    How do you check to see if the DailyRd.DlyDate = HoulyRd.HrDateTime. If so, update the DailyRd.Reading with the Sum of HourlyRd.Reading for the Current Row Date of HourlRd.HrDateTime. If not, insert a new DailyRd row.

    Thanks...
    Alan

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Is the INSERT into Hourly table generated by the FE code? You should have a stored procedure that would do that for you (of course, first, - you need to write it )

    Code:
    declare @RecordID_Daily int
    begin tran
    insert Hourly (...) values (...)
    set @RecordID_Daily = (
       select RecordID from Daily 
          inner join Hourly on Daily.DlyDate = Houly.HrDateTime)
    if @RecordID_Daily is null
       -- DO YOUR INSERT
    else
       -- DO YOUR UPDATE
    commit tran

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    Thanks... This gets me going in the right directions.

    Alan



    [code]
    declare @RecordID_Daily int
    begin tran
    insert Hourly (...) values (...)
    set @RecordID_Daily = (
    select RecordID from Daily
    inner join Hourly on Daily.DlyDate = Houly.HrDateTime)
    if @RecordID_Daily is null
    -- DO YOUR INSERT
    else
    -- DO YOUR UPDATE
    commit tran

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •