Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    96

    Unanswered: Check Referential Ingerity or Catch Error

    Hi all,

    Just wondering what would be the normal or more efficient practice to insert/update a record.

    1. Check for existence of primary record (using SELECT in stored procedure)
    2. Capture error and handling

    My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @err = @@ERROR" after the insert statement.

    so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

    Any advise would be appreciated..

    Cyherus

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Quote Originally Posted by Cyherus
    Hi all,

    Just wondering what would be the normal or more efficient practice to insert/update a record.

    1. Check for existence of primary record (using SELECT in stored procedure)
    2. Capture error and handling

    My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @err = @@ERROR" after the insert statement.

    so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

    Any advise would be appreciated..

    Cyherus
    Any error above 16 won't be caught. You need to catch those at the client or app level. If you're having these types of RI issues though, you have an app design issue. The PK should already be known by the app when you go to insert foreign key records. On the insert of a new PK, it should either be auto or a true natural key. In this case, there is no chance of error.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2004
    Posts
    96
    Many thanks.. now things are much clearer..

    I'm reading these data from a text file and foreign keys on these records are not checked against the primary tables in my DB, thus the need to handle this.

    I dun quite get you when you mention about inserting new PKs, what do you mean by auto or true natural key??

Posting Permissions

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