Robert,

Using RaisError the way you use it in your example is generating an error message for the client, but is not actually an error in the trigger which is why the transaction is completing. As far as SQL Server is concerned the error has been handled and the trigger returns 0 for success. To demonstrate try this code:

[font="Courier New"]CREATE PROCEDURE dbo.test

AS

--select 'test'

RAISERROR 19999 'test';

GO

DECLARE @retval INT

EXEC @retval = dbo.test

SELECT @retval

DROP PROCEDURE dbo.test[/font]

For a trigger with an actual error the transaction is rolled back. In the following code I am trying to insert a value too large for the data type so the outer transaction fails in both cases:

[font="Courier New"]CREATE TABLE dbo.testXa

    (

    a integer

    )

CREATE TABLE dbo.TestXb

    (

    b tinyint

    )

GO

CREATE TRIGGER [dbo].[ti_test] ON [dbo].[testXa]

after INSERT

AS

INSERT INTO dbo.TestXb

    SELECT

        *

    FROM

        inserted

GO

SELECT

    *

FROM

    testxa

--emtpy table

INSERT INTO testxa

    VALUES

        (

        1234

        )

--we got an error, not raised an error so no insert

SELECT

        *

FROM

    testxa

SELECT

    *

FROM

    testxb

GO

BEGIN try

    INSERT INTO testxa

        VALUES

        (

        12345

        )

END try

BEGIN catch

    SELECT

           'Error in Trigger' AS error,

            Error_Message() AS MESSAGE

END catch

GO

--no insert took place

SELECT

    *

FROM

    testxa

DROP TABLE dbo.testXa

DROP TABLE dbo.testXb

[/font]

Even if I use Try-Catch in the trigger I get a rollback:

[font="Courier New"]CREATE TRIGGER [dbo].[ti_test] ON [dbo].[testXa]

after INSERT

AS

    BEGIN try

        --will not work due to overflow

        INSERT INTO dbo.TestXB

            SELECT

                *

            FROM

                inserted

   END Try

   BEGIN Catch

        DECLARE @error VARCHAR(500)

        SET @error = Error_Message()

        SELECT @error

   END Catch[/font]