Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    KL, Malaysia
    Posts
    38

    Unanswered: Trapping Primary Key violation in Transact SQL

    Is it possible to trap Primary key, foreign key violations using transact SQL?

    Notice that I am not able to trap it:

    begin
    insert into testtuan values ('asdsd')

    if @@error <>0
    begin
    print 'print error message'
    end
    end

    The above code never goes to print error message, but instead goes to something like below:

    Violation of PRIMARY KEY constraint 'PK_testtuan'. Cannot insert duplicate key in object 'testtuan'.
    The statement has been terminated.


    Thanks.
    Save the dugongs!

  2. #2
    Join Date
    Jan 2003
    Location
    KL, Malaysia
    Posts
    38
    btw, the above code is in a trigger. I just set it in such a way so that I can test what happens when I enter some values which violates the primary key constraint and how the trigger handles it.
    Save the dugongs!

  3. #3
    Join Date
    Apr 2003
    Location
    UK
    Posts
    11
    Originally posted by tswern
    btw, the above code is in a trigger. I just set it in such a way so that I can test what happens when I enter some values which violates the primary key constraint and how the trigger handles it.

    You do not say which version of SQL Server you are using.

    For SQL Server 2000, you need to use a Instead Of Insert trigger to test for key violations. The basic syntax is :-

    CREATE TRIGGER IOI_Test ON Test
    INSTEAD OF INSERT
    AS

    IF NOT EXISTS(SELECT Test.KeyValue
    FROM Test, inserted
    WHERE Test.KeyValue = inserted.KeyValue)
    BEGIN
    INSERT INTO Test(KeyValue)
    SELECT KeyValue
    FROM inserted
    END
    ELSE
    BEGIN
    Print 'The key value already exists'
    END


    You have to check to see if the record exists before you try and insert otherwise you will receive the key violation error as soon as you try to insert the record.

  4. #4
    Join Date
    Jan 2003
    Location
    KL, Malaysia
    Posts
    38
    Hi spudly, thanks for your response. I am using Instead of Trigger. Trying to cut down on the table scan and was trying to avoid using the solution you proposed. Seems like I have no choice.

    Any other alternatives?
    Save the dugongs!

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you have an index on, back to Spudly's post, KeyValue you won't be doing a table scan.

    btw I would replace the print statment in Spudly's post with a raiserror as a print statment won't register as an error.

    raiserror('The key value already exists',16,1)
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Apr 2003
    Location
    UK
    Posts
    11
    Originally posted by Paul Young
    If you have an index on, back to Spudly's post, KeyValue you won't be doing a table scan.

    btw I would replace the print statment in Spudly's post with a raiserror as a print statment won't register as an error.

    raiserror('The key value already exists',16,1)
    The print statement was really a throw away line to show the principle.

    If the duplicate record is not important then I usually use a print statement.

    If the duplicate is important then I would dump the record with all it's values into an error table so that I can investigate the error condition.

    I also forgot to mention that the routine only works if you are inserting one record at a time. A bulk insert will break the trigger.

  7. #7
    Join Date
    Feb 2003
    Posts
    109

    uh its easy

    uh theres an easy way to trap violations of entity integrity.

    for starters, you do a count of the PK column, and any records that have a count of this > 1 need to go into an exceptions table.
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    aaron_kempf, doing a count will force a scan of the index table where as using EXISTS() will allow a seek into the table. On a non indexed table you will do a scan no matter what.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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