Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Question Unanswered: Trigger INSTEAD OF INSERT??

    So... dear friends
    I want to insert values to a table without firing the foreign key Constraint.
    I think one way is to insert values into the parent table first and then perform the initial insert.
    Is this possible with an INSTEAD OF INSERT Trigger or another way? If yes can you make an example? I want to check for dublicate values in the parent table too!!
    Thanx!
    I feel dizzy, too much coffein in my blood, and i'm still getting these error messages. Well doctor?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Violating referential Integrity ... I wouldnt advise that ....

    Duplicate values in Parent table ... dont you have a primary key or something ????
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Mar 2004
    Posts
    13

    I didn't ment that.

    I have a table named Products and another one Sales i want to insert a Sale typing for example @Product = 'Cola'. but 'Cola' as doesn't exist as PK in the Products table. I want to pause the Sale Insert, insert Cola into the Products table first, and then resume.
    I feel dizzy, too much coffein in my blood, and i'm still getting these error messages. Well doctor?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    For that an Instead of trigger would work fine
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Mar 2004
    Posts
    13
    Can you give me an example?
    I feel dizzy, too much coffein in my blood, and i'm still getting these error messages. Well doctor?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't over think this...

    If it's an application, don't even allow the insert in to the child in the first place without checking the possible values for the parent...don't allow free form entries....

    and if it's not from an application...good let it fail...
    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.

  7. #7
    Join Date
    Mar 2004
    Posts
    13
    In the trigger: I want to check if the inserted "Product" value exists in the parent Products table. If yes, do the Sale Insert, if no, first insert the value into the parent table, and then insert the Sale. I cannot understand where is the problem with that action.
    I feel dizzy, too much coffein in my blood, and i'm still getting these error messages. Well doctor?

Posting Permissions

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