November 15, 2010 at 1:20 pm
SQL 2K sp4
Suppose I insert into a table having an Insert Trigger (and not an Instead Of trigger).
INSERT table VALUES (x, y, z)
SELECT @nErrorVar = @@ERROR
Is @@ERROR set after the insert and before the trigger, or after the insert and after the trigger?
Thanks,
Paul
November 15, 2010 at 1:25 pm
The trigger fires as part of the insert, so the error value would be after the trigger, I think.
Test it. Put a trigger on a test table that has a raiserror with a severity of 10 in it (10 = information message), and have a message in the script after that. See what sequence they're in.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2010 at 1:39 pm
Boooo: I added RAISERROR ('test', 11' 1) at the end of the trigger (10 is converted to 0 by SQL Server -- See Erland Sommerskog's website http://www.sommarskog.se/error-handling-I.html#@@error)
The Insert occurs successfully, @@error = 50000 is returned after the trigger
One more reason to avoid triggers...
Thanks for the quick response!
P
November 16, 2010 at 2:51 pm
Yeah, that works too. I was just talking about reading error messages in SSMS, but getting an actual error value makes more sense for what you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply