Trigger Tricks ?

8 Sep 2006 4:48 PM

Rob

I understand that if the code assiciated with a trigger fails, then the
action that called the triger in the first place fails as well. Any way to
circumvent this (and not have the underlying action fail) ?

8 Sep 2006 4:56 PM

Hilary Cotter

You should be able to use the try catch syntax in SQL server 2005 to prevent
this rollback.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quote

"Rob" <rwch***@comcast.net> wrote in message
news:w6GdneyuDvmSA5zYnZ2dnUVZ_qednZ2d@comcast.com...
>I understand that if the code assiciated with a trigger fails, then the
>action that called the triger in the first place fails as well. Any way
>to circumvent this (and not have the underlying action fail) ?
>

8 Sep 2006 5:07 PM

Rob

Thanks, but I should have been more specific... using Sql server 2000

Show quote

"Hilary Cotter" <hilary.cot***@gmail.com> wrote in message
news:uxeSEe20GHA.4044@TK2MSFTNGP04.phx.gbl...
> You should be able to use the try catch syntax in SQL server 2005 to
> prevent this rollback.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
>
>
> "Rob" <rwch***@comcast.net> wrote in message
> news:w6GdneyuDvmSA5zYnZ2dnUVZ_qednZ2d@comcast.com...
>>I understand that if the code assiciated with a trigger fails, then the
>>action that called the triger in the first place fails as well. Any way
>>to circumvent this (and not have the underlying action fail) ?
>>
>
>

8 Sep 2006 5:54 PM

Tracy McKibben

Rob wrote:

> I understand that if the code assiciated with a trigger fails, then the
> action that called the triger in the first place fails as well. Any way to
> circumvent this (and not have the underlying action fail) ?
>
>

Can you be more specific about what your potential failure might be, and
why you wouldn't want it rolled back? Typically, the processing done by
a trigger is intended to be part of the overall transaction.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

8 Sep 2006 6:09 PM

Rob

Thanks Tracy,

I have since posted a better description of the what I really want to
accomplish under a separate thread... content posted below....

We are currently having the following process finger-pointing issue...

Assume you have the following situation...

An Orders table
A Shipments table
An Invoice table

A separate program takes Orders information and Inserts rows into the
Shipments table (at time of shipping)

When rows are inserted into the Shipments table via "the Shiping Process", a
trigger "for Invoicing" is fired.

Currently, it appears that some random rows are "missing" from the Shipment
table after the shipping process occurs.

There are at least 2 distinct possibilities...

1. The Shipping process failed to insert the row

or

2. The trigger failed, and thus the underlying action (writing the shipment
record) fails as well.

Is there any way to construct (or error trap) the trigger, such that the
underlying row gets written to the Shipments table (even if the subsequest
actions fail) ? Also, how to log the failure ?

Show quote

"Tracy McKibben" <tr***@realsqlguy.com> wrote in message
news:4501AE4B.8070601@realsqlguy.com...
> Rob wrote:
>> I understand that if the code assiciated with a trigger fails, then the
>> action that called the triger in the first place fails as well. Any way
>> to circumvent this (and not have the underlying action fail) ?
>
> Can you be more specific about what your potential failure might be, and
> why you wouldn't want it rolled back? Typically, the processing done by a
> trigger is intended to be part of the overall transaction.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com