why won't my trigger rollback?

Question

why won't my trigger rollback?

Lowell

SSC Guru

Points: 323518
More actions
December 18, 2008 at 1:00 pm

#126548

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!

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

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]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question