Re: Transaction Abort in a trigger SQL 2008, writing to event log
- From: John Couch <JohnCouch@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 4 Aug 2009 08:46:02 -0700
Erland,
Thank you for all the information. This is the post where I pulled some of
my logic from in regards to the error info processing. Based on what you said
though, this will not work entirely wither because the transaction could
still be doomed at some point, and need a rollback. You are saying to turn
off xact_abort, would I then need to remove my check for xact_state?
http://blogs.msdn.com/anthonybloesch/archive/2009/03/10/SQL-Server-error-handling-best-practice.aspx
and which I think is still similar to what I was doing, except they are not
creating another transaction within the trigger. Based on what you said
though, there are still some situations where this process will not work
"Erland Sommarskog" wrote:
John Couch (JohnCouch@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:.
I have seen tons of things out there telling oyu to rollback a
transaction in the catch block of a trigger, capture the error
information and write it out to a table, but I have yet to find a way to
make it work. I have 2 tables, a code table and ServerOption table, and
a trigger on ServerOption that attempts to validate the entry into the
table, and process an error if it occurs. If you make a change from
enterprise manager, I always get the message that the transaction was
aborted in the trigger, and you can't insert into the widnows
application log or even a table. How is this supposed to work???
You can add this command to the trigger:
SET XACT_ABORT OFF
now the trigger will behave like regular code will. That is, normally
XACT_ABORT is off by default, but it's the other round in triggers for
compatibility reasons.
However, there are still some errors that will cause the transaction to
be doomed, in which case you cannot perform any updates until you have
rolled back the transaction.
I note that in the error-handling procedure you do RAISERROR WITH LOG.
Note that this requires sysadmin permission, so if this is to be run by
regular users, this will not work out in its current form. To get it to
work, see this article of mine: http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
- Follow-Ups:
- Re: Transaction Abort in a trigger SQL 2008, writing to event log
- From: Erland Sommarskog
- Re: Transaction Abort in a trigger SQL 2008, writing to event log
- References:
- Transaction Abort in a trigger SQL 2008, writing to event log or t
- From: John Couch
- Re: Transaction Abort in a trigger SQL 2008, writing to event log or t
- From: Erland Sommarskog
- Transaction Abort in a trigger SQL 2008, writing to event log or t
- Prev by Date: Re: xp_cmdshell copy from ftp site
- Next by Date: Re: Transaction Abort in a trigger SQL 2008, writing to event log
- Previous by thread: Re: Transaction Abort in a trigger SQL 2008, writing to event log or t
- Next by thread: Re: Transaction Abort in a trigger SQL 2008, writing to event log
- Index(es):
Relevant Pages
|
