Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Unanswered: Need advise on Insert trigger

    Hi all,

    I am trying to create a insert trigger that gets a value from the new record and use that to get additional data from other tables and update the new record

    this is howfar i came:

    Code:
    create trigger trUpdateGEOData
    on BK_Machine
    after insert
    as
    update BK_Machine
    set BK_Machine.LOC_Street = GEO_Postcode.STraatID, BK_Machine.Loc_City = GEO_Postcode.PlaatsID
    from BK_Machine join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode, inserted
    where BK_Machine.MachineID = Inserted.MachineID and BK_Machine.Loc_Postalcode = GEO_Postcode.postcode and BK_Machine.LOC_Doornumber <= GEO_Postcode.van and
    BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
    Trigger runs fine but doesn't do a thing probably it can't find the machineid i think,

    can someone help me?

    Cheers Wimmo

  2. #2
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    So sorry for posting such a stupid mistake from my hazy view today!!

    < should be >

    Trying to get awake today, sorry for the disturbance

    Cheers Wimmo

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Cleaned up your code:
    Code:
    create trigger trUpdateGEOData
    on BK_Machine
    after insert
    as
    update	BK_Machine
    set	BK_Machine.LOC_Street = GEO_Postcode.STraatID,
    	BK_Machine.Loc_City = GEO_Postcode.PlaatsID
    from	BK_Machine
    	inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
    		and BK_Machine.LOC_Doornumber <= GEO_Postcode.van
    		and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
    	inner join inserted on BK_Machine.MachineID = Inserted.MachineID
    Now, it strikes me that the inner join on GEO_Postcode could return no records, in which case no BK_Machine records would be updated. It could also potentially return more than one record, in which case you would get unpredictable results for your update.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Thanx Blindman,

    What do you mean with the inner join: is it when there are more records inserted at the same time with the same machineid?

    Wimmo

Posting Permissions

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