why won't my trigger rollback?

  • I've got a table where one of the fields, from a business perspective should not be null, even though the column definition allows null.

    Some portion of our app is updating the field to null when it's not supposed to.

    I haven't been able to find the offending update in an application, so i figured I'd throw a trigger on the table to rollback the offending transaction. that way when someone actually runs the process that does the bad business logic, they'd call and say i got a problem.

    i created the trigger below, but if you run the code, you'll see that even thought he RaseError occurs, the table is still updated to null....

    what am i doing wrong here? i thought a raiseError of 16 or hugher rolled back a transaction in a trigger.

    target platform is SQL2000, but the same thing happens on SQL2K5 as well

    [font="Courier New"]CREATE TABLE XTEST(TESTID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

                       OTHERID INT )

    CREATE TRIGGER [TR_CRASHPROJ_XTEST] ON XTEST

    FOR UPDATE

    AS

    BEGIN

       IF EXISTS(SELECT 1 FROM INSERTED WHERE INSERTED.OTHERID IS NULL)

           BEGIN

               RAISERROR(N'RollBack Of XTEST UPDATE; OTHERID was Null. Please Report to your Project Administrator',16,1)

               RETURN

           END

    END

    --add some crappy default data

    INSERT INTO XTEST(OTHERID)

    SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4

    --sample code which rolls back

    UPDATE XTEST SET OTHERID = NULL WHERE TESTID BETWEEN 2 AND 3

    --see the bad data

    SELECT OTHERID,* FROM XTEST WHERE TESTID BETWEEN 2 AND 3

    --put some value back in it for the next pass

    UPDATE XTEST SET OTHERID = 3 WHERE TESTID BETWEEN 2 AND 3[/font]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I have a trigger that is almost identical with the RAISERROR of 16, and I had to add an explicit ROLLBACK TRAN before the return. I was of the same impression as you, but it does not roll back without the ROLLBACK.

    Copied directly out of my trigger:

    BEGIN

    RAISERROR ('Cat + Tag + Designator cannot be duplicated at site.',16, 1)

    ROLLBACK TRAN -- Should be Implied due to Error Level 16.

    END

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • I used to think the same thing. But I cannot find any documentation that states a RaisError at any level causes a rollback.

    Here is all there is in BOL regarding Severity levels:

    Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.

    Caution:

    Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

    Basically if you are less than 20 you need an explicit rollback. Of course terminating the connection is not what you want to do.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply