Raise a Particular Error in t-SQL with RAISERROR

Question

I have a view that has an INSTEAD OF INSERT trigger (in SQL Server 2005). When the user inserts into the view, they are in fact making inserts and updates to a number of tables. The view is sufficiently complex that it cannot have an index, so is unfortunately unconstrained.

The view is being inserted into from C# using code that would be problematic to change. This code catches primary and unique key violations using the following:

try
{
    ... // Insert into view
}
catch (SqlException ex)
{
    if (ex.Number == 2627 || ex.Number == 2601) // Primary key exception, unique constraint violation
    {
        ... // Report the duplicate entry to the user
    }
    else
    {
        throw;
    }
}

So my question is: can I use RAISERROR within my trigger to create an exception with number 2627 or 2601?

Answer 1

No. You'll have to wait for THROW in the next release (maybe)

You can only throw errors that you have put into sys.messages (50000+), or with text that gives 50000. Or embed it in the text and change your c#. You can't throw errors less than 50000

If the view is so complex that you can't use DRI, then it is too complex. Also, you'll have concurrency issues: overlapping calls will break your "uniqueness" at some point when you roll your own.

Answer 2

I'm not sure if you can actually RAISE a genuine primary key violation. Though you can RAISE your own error with your own message and then catch that. This will also allow you to distinguish between a genuine primary key violation and your own custom violations.

Perhaps the crudest way to accomplish this would be...

SQL Code (in TRIGGER definition maybe)...

RAISERROR('Custom View Violation',16,1);

C#...

try 
{
    //execute SP / Insert etc...
}
catch (SqlException ex)
{
    if (ex.Message.Split('\r')[0] == "Custom View Violation")
    {
        //deal with your exception
    }
}