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

    Unanswered: Insert Trigger Question

    Can someone help me with this scenario:
    I have 2 tables one detail and one summary. The key fields for betwen the the detail and summary tables are the date field na the part # field.
    I need to create a trigger that will insert records when they do not exist in the summary table and also only update the records that are needing to be modified. Any Ideas?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Real time warehousing?

    Can't you run a scheduled process instead?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    NC
    Posts
    13
    Originally posted by Brett Kaiser
    Real time warehousing?

    Can't you run a scheduled process instead?
    I need to have this summary infomration available at any time the daily reports need to be run. A scheduled process would be easier but I need to keep this table up to date as transcations are processed in the detail table.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Use this as an example:

    create table item(id int identity,item# varchar(10))
    create table itemsummary(id int identity,item# varchar(10),quantity int)
    go
    create trigger iu_item on item
    for insert,update
    as
    insert itemsummary(item#)
    select item# from inserted i
    where not exists(select 1 from itemsummary where item#=i.item#)
    update itemsummary set quantity=(select count(*) from item i where i.item#=itemsummary.item#)
    go
    insert item(item#) values('#1')
    insert item(item#) values('#1')
    insert item(item#) values('#2')

    select * from item
    select * from itemsummary

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    He'll need to update..the count? for the part#

    So you need 2 sections...

    Check

    -- An Update
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)

    --An INSERT
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)

    Then do your apporpriate action...

    What's the transaction level?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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