Professional Web Applications Themes

RAISERROR - Microsoft SQL / MS SQL Server

Hi there, because of some inabilities of SQL Server 2000, I have to use a trigger to enforce referential integrity. If an INSERT statement is not allowed to be executed (because of a virtual constraint -- the referenced foreign key is not available), I want to raise an error. First I was using RAISERROR (547, 16, 23000) because these are the error code (547), priority (16) and SQL state (23000) that are used if the referential constraint would have prevented execution of the INSERT statement. [Values are copied from JDBC SQLException that was caught.] But this does not work: message ...

  1. #1

    Default RAISERROR

    Hi there,

    because of some inabilities of SQL Server 2000, I have to use a trigger to
    enforce referential integrity.
    If an INSERT statement is not allowed to be executed (because of a virtual
    constraint -- the referenced foreign key is not available), I want to raise
    an error.

    First I was using

    RAISERROR (547, 16, 23000)

    because these are the error code (547), priority (16) and SQL state (23000)
    that are used if the referential constraint would have prevented execution
    of the INSERT statement. [Values are copied from JDBC SQLException that was
    caught.]

    But this does not work:
    message id 547 is not valid, although it is listed in
    "master.sysmessages" (13000 < msg_id < 2^31)
    state seems to be no SQL state (0 < state < 127)

    I would like to raise the same error that occurs with constraints working.
    How can I achieve this?
    Thanks for your advice,

    Johannes


    Johannes Lebek Guest

  2. #2

    Default Re: RAISERROR

    You can only raise user defined error messages (error 50000 and higher) with
    RAISERROR, so you can't mimick system error messages.

    Can you tell is what you try to achieve and what the "inabilities" of SQL
    Server 2000 are that prevent you from achieving that?

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Johannes Lebek" <joh.lebekgSmPxA.Mde> wrote in message
    news:#NKA1nHRDHA.2768tk2msftngp13.phx.gbl...
    > Hi there,
    >
    > because of some inabilities of SQL Server 2000, I have to use a trigger to
    > enforce referential integrity.
    > If an INSERT statement is not allowed to be executed (because of a virtual
    > constraint -- the referenced foreign key is not available), I want to
    raise
    > an error.
    >
    > First I was using
    >
    > RAISERROR (547, 16, 23000)
    >
    > because these are the error code (547), priority (16) and SQL state
    (23000)
    > that are used if the referential constraint would have prevented execution
    > of the INSERT statement. [Values are copied from JDBC SQLException that
    was
    > caught.]
    >
    > But this does not work:
    > message id 547 is not valid, although it is listed in
    > "master.sysmessages" (13000 < msg_id < 2^31)
    > state seems to be no SQL state (0 < state < 127)
    >
    > I would like to raise the same error that occurs with constraints working.
    > How can I achieve this?
    > Thanks for your advice,
    >
    > Johannes
    >
    >

    Jacco Schalkwijk Guest

Similar Threads

  1. ADOConnection loses RAISERROR from SQL Server
    By James in forum ASP Database
    Replies: 1
    Last Post: November 14th, 02:33 PM
  2. Replies: 0
    Last Post: September 19th, 06:55 AM
  3. Replies: 0
    Last Post: September 19th, 04:59 AM
  4. Replies: 0
    Last Post: September 18th, 11:40 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139