Problems catching @@error from trigger on insert from openxml

Have kind of an oddball scenario that needs a solution.
I have a stored proc which is passed XML that pulls out sections of the xml
for inserts into 3 different tables.
The first insert has an "instead of" trigger that on rare occasions raises
an error.
After that first insert (using an "insert into ... select * from openxml"
form) I need to know if that error was raised. Testing @.@.ERROR always gives
me 0.
Is this an issue with the openxml insert form?
What I would like to do is rollback a transaction if any of the inserts fail.Just put all the 3 inserts in a transaction with
BEGIN TRANSACTION
INSERT 1
IF @.@.TRANCOUNT > 0
INSERT 2
IF @.@.TRANCOUNT > 0
INSERT 3
IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION
An error (any error) in a trigger will terminate and rollback the
transaction that fired it. The thing you want to avoid to do after that is
so the next inserts. The code above will give you some control over the
exact execution, but if you don't need that, you can use SET XACT_ABORT ON
before you start the transaction, and no code will be executed in the batch
after an error is encountered.
You probably don't see any value for @.@.ERROR because you don't check it
immediately after the statement that raises the error (easy mistake to
make).
For some more background information read these excellent articles by SQL
Server MVP Erland Sommarskog:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
--
Jacco Schalkwijk
SQL Server MVP
"D.Kratt" <DKratt@.discussions.microsoft.com> wrote in message
news:7EFFB116-37E6-4188-8500-2846C5233AEA@.microsoft.com...
> Have kind of an oddball scenario that needs a solution.
> I have a stored proc which is passed XML that pulls out sections of the
> xml
> for inserts into 3 different tables.
> The first insert has an "instead of" trigger that on rare occasions raises
> an error.
> After that first insert (using an "insert into ... select * from openxml"
> form) I need to know if that error was raised. Testing @.@.ERROR always
> gives
> me 0.
> Is this an issue with the openxml insert form?
> What I would like to do is rollback a transaction if any of the inserts
> fail.|||Tried both (thanks for the suggestions, eventually got me to a solution),
but didn't work until I placed an explicit ROLLBACK TRANSACTION right before
my raiseerror in the insert trigger on the first table.
"Jacco Schalkwijk" wrote:
> Just put all the 3 inserts in a transaction with
> BEGIN TRANSACTION
> INSERT 1
> IF @.@.TRANCOUNT > 0
> INSERT 2
> IF @.@.TRANCOUNT > 0
> INSERT 3
> IF @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> An error (any error) in a trigger will terminate and rollback the
> transaction that fired it. The thing you want to avoid to do after that is
> so the next inserts. The code above will give you some control over the
> exact execution, but if you don't need that, you can use SET XACT_ABORT ON
> before you start the transaction, and no code will be executed in the batch
> after an error is encountered.
> You probably don't see any value for @.@.ERROR because you don't check it
> immediately after the statement that raises the error (easy mistake to
> make).
> For some more background information read these excellent articles by SQL
> Server MVP Erland Sommarskog:
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D.Kratt" <DKratt@.discussions.microsoft.com> wrote in message
> news:7EFFB116-37E6-4188-8500-2846C5233AEA@.microsoft.com...
> > Have kind of an oddball scenario that needs a solution.
> >
> > I have a stored proc which is passed XML that pulls out sections of the
> > xml
> > for inserts into 3 different tables.
> >
> > The first insert has an "instead of" trigger that on rare occasions raises
> > an error.
> > After that first insert (using an "insert into ... select * from openxml"
> > form) I need to know if that error was raised. Testing @.@.ERROR always
> > gives
> > me 0.
> >
> > Is this an issue with the openxml insert form?
> >
> > What I would like to do is rollback a transaction if any of the inserts
> > fail.
>
>