Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Unanswered: insert trigger for related table?

    have the following schema:
    [dbo].[tbl_Events]
    [Event_ID] [uniqueidentifier] NOT NULL (PK)
    [Location_ID] [uniqueidentifier] NULL (FK)
    [Observation] [nvarchar]
    [dbo].[tbl_Locations]
    [Event_ID] [uniqueidentifier] NOT NULL
    [Location_ID] [uniqueidentifier] NULL (PK)
    [Notes] [nvarchar]

    Locations is the parent table with a relationship to events through Location ID.

    Location_ID is set to be NewID() on create in Locations, and Event_ID is set to be NewID() on create in events. The table, relationship, and PK format is non-changeable due to organizational policy governing replication.

    I'm looking for advice on how to define an insert trigger that will create a new row in events, with the location_id pulled from the parent location table, and a new unique event_id. E.g., when (by outside application with no ability to embed sql code) a new location record is created, it gets a location_id of 8170daed-92c8-47f1-98ca-147800329686, and the trigger creates a new event record also with a location_ID of 8170daed-92c8-47f1-98ca-147800329686 and an event_ID of cfed8fe8-b5be-4f78-b366-008672e39637.

  2. #2
    Join Date
    Apr 2011
    Posts
    17
    instructions given by you imply that , when you create new location record you want the corresponding record to be inserted in Event_tbl with its new event_id and the newly created location.you may try following trriger :

    use <dbname>
    go
    create trigger <trigger_name>
    on tbl_location
    for insert
    as

    insert into tbl_event ( Event_Id , Location_Id )
    values ( NewID(),Inserted.Location_Id );

    go


    Best Regards,
    Krishna.

  3. #3
    Join Date
    Apr 2011
    Posts
    6

    re:insert trigger for related table?

    Hi thanks for the prompt reply!

    I tried the following:

    create trigger new_Location
    on dbo.tbl_Locations
    for insert
    as
    insert into dbo.tbl_Events ( Event_Id , Location_Id )
    values ( NewID(),Inserted.Location_Id );
    go


    and get this error:
    Msg 4104, Level 16, State 1, Procedure new_Location, Line 7
    The multi-part identifier "Inserted.Location_Id" could not be bound.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    create trigger new_Location
    on dbo.tbl_Locations
    for insert
    as
    	insert into dbo.tbl_Events ( Event_Id , Location_Id )
    	SELECT NewID(), Inserted.Location_Id 
    	FROM Inserted
    ;
    GO
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Krishna, your code will fail on multi-record transactions.

    All triggers should be coded to handle multi-record transactions, and not assume that transactions will involve only single records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2011
    Posts
    6

    good point

    how would I modify this code to work on multirow updates? The above code works great, except it simply doesn't fire on multirow inserts. Thanks!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See my answer on your new thread. I'm closing this thread, since you have opened a new one.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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