Commit and Raiserror in a trigger

Maybe there is a better way to do this. The jist of I want is to have SQL Server raise me 2 types of errors: a WARNING and an ERROR from a trigger when I update a table. If SQL server returns a WARNING the trigger should COMMIT but show the warning to the user (using .NET - preferably through a SQL Exception which is only raised if severity > 10) and if it is an ERROR the trigger should ROLLBACK and show the ERROR to the user (through a SQL Exception). My attempts (needless to say this isn't working) at this was to have a trigger like this:

ALTER TRIGGER [dbo].[TR_TRANSACTION_UPDATE]
ON [dbo].[tTRANSACTION]
FOR UPDATE
AS
BEGIN
   ...

    BEGIN TRY
    DECLARE @id INT ,@maxid INT
    SELECT @id = 0 ,@maxid = MAX(transID) FROM INSERTED 
    WHILE @id < @maxid 
        BEGIN
            SELECT  @id = MIN([TransID]) FROM INSERTED WHERE [TransID] > @id
                EXEC dbo.sp_CheckTransaction @TransID = @id
            END
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        DECLARE @err_msg VARCHAR(MAX), @err_sev AS INT, @err_state AS INT
        SELECT @err_msg = ERROR_MESSAGE(), @err_sev = ERROR_SEVERITY(), @err_state = ERROR_STATE()
        IF @err_state <> 120  -- '120 is 开发者_高级运维not a fatal error from STORED_PROC
              ROLLBACK TRANSACTION
        ELSE
              COMMIT TRANSACTION

        RAISERROR(@err_msg, @err_sev, @err_state)
    END CATCH
END

This trigger doesn't work because it thinks the transaction is uncommittable.

Also, sp_CheckTransaction can RAISE 2 types of errors:

 RAISERROR(@msg, 15, 120)  -- warning
 or RAISERROR(@msg, 15, 121)  -- error

The reason I can't use a severity < 15 is because I want a warning to show up in .NET (I can decide based on state & severity if it is a warning or an error). .NET SqlException is raised only for severity > 10.

 RAISERROR(@err_msg, @err_sev, @err_state)

You cannot do this. You are not allowed to raise system messages, only user messages (error numbers above 50000). You must raise like this:

RAISERROR(N'An error occured in trigger: %s %d %d', 
      <severity>, <state>, @err_msg, @err_sev, @err_state);

[Updated: actually since @err_msg is the message, not the error number, your usage is OK, so ignore this]

For a more lengthy discussion on this topic, see Exception handling and nested transactions. You'll see there explained why your implementation is flawed (it does not check for XACT_STATE() as it should) and the linked article offers a much better implementation.

Now back to your pain point: you need to understand the SQL Server error severity model. Errors raised with severity above 10 are errors and the ADO.Net will raise an exception for them. Errors raised with severity below 10 are informational messages and ADO.NEt will raise an SqlConnection.InfoMessage event for them. See also What do the different RAISERROR severity levels mean?

So the true root of your problem is not your trigger, is the sp_CheckTransaction stored procedure. It should raise severity 16 for errors, and severity 0 for warnings:

RAISERROR(@msg, 0, 120)  -- warning

or

RAISERROR(@msg, 16, 121)  -- error

And use SqlConnection.InfoMessage to intercept warnings. Attempting to use the state as severity will get you nowhere fast.

Your trigger may catch and re-throw the error, depending on a number of factors, but is very very unlikely that you need to. If you do catch an exception then you need to raise a new error, and you need proper logic to handle the severity (raise 16 for errors, 0 for warnings). Never use any other severity other than 16 and 0 in your T-SQL code.

As a side note, starting with the next version of SQL Server you will be able to use a simple THROW; to re-throw the original exception, much like in .Net exception handling. More info SQL Server v.Next (Denali) : Exploring THROW.

I would advise against a design like this. It makes it harder for you to test your code, but if you're stuck doing it this way for some reason, here's how to do it.

raiserror ( 'foo', 1, 1 ) will raise an error, but not cause the execution of the trigger to stop. If the severity is greater then 10, I belive it rolls the transaction back.

http://msdn.microsoft.com/en-us/library/ms177497.aspx

you'll then have to work with the SqlConnection to display the error message.

have you tried:

IF @err_state <> 120  -- '120 is not a fatal error from STORED_PROC'

instead of

IF @err_state <> 120  -- '120 is not a fatal error from STORED_PROC