Results 1 to 6 of 6

Thread: Insert Triggers

  1. #1
    Join Date
    Jul 2004
    Posts
    24

    Unanswered: Insert Triggers

    I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Within the trigger, you can access a view called INSERTED that shows only the rows that are being inserted by the statement that launched the trigger. You can use the INSERTED view (probably via a JOIN) to limit the number of rows you are affecting in your underlying table.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    my telepathic usb port is clogged...can you post the trigger...

    probably take us a few minutes...

    DDL would be nice as well

    and pat's correct(what again? say it ain't so...)
    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.

  4. #4
    Join Date
    Jul 2004
    Posts
    24

    Heres the trig..

    CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
    FOR INSERT
    AS
    insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
    select tblGroup.Id as DataRecordId,
    1 as TaskNum,
    "Ready" as Status,
    tblUsers.Id as UserId,
    getdate() as StartDateTime
    from tblGroup, tblUsers, tblVendors where (tblGroup.I_Field3=tblVendors.OdissVendorId)
    And (tblGroup.I_Field6 Is Null OR tblGroup.I_Field6='0')
    And (tblUsers.WFID=1)

    ..a little complex. the check for tblGroup.I_Field6 is necessitated because all records are being checked - this where clause could be stripped off if only new records were being checked.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Something like this would do it:

    CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
    FOR INSERT
    AS
    if exists (select 1 from inserted)
    insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
    select i.Id, 1, 'Ready', u.Id, getdate()
    from inserted i
    inner join tblVendors v
    on i.I_Field3=v.OdissVendorId
    inner join tblUsers u
    on (u.WFID=1)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jul 2004
    Posts
    24
    thanx..will try this.

Posting Permissions

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