Re: TEXT field in trigger; valid workaround?
From: Mike Jansen (mjansen_nntp_at_mail.com)
Date: 10/29/04
- Next message: Brian Moran: "Re: Statistics Time"
- Previous message: Vishal Parkar: "Re: Help with a select statement I creating."
- In reply to: Dan Guzman: "Re: TEXT field in trigger; valid workaround?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 29 Oct 2004 09:33:50 -0400
Thanks Dan. That's the confirmation I was hoping for. I understand an
error in the trigger will rollback the INSERT, that's why I want to ensure
that I'm not doing something screwy that could potentially cause an error.
>From what you are saying I am not. So I can now go forward with as much
confidence as anyone can in this modern digitally unstable world we live
in... :)
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:OeIyH9avEHA.2316@TK2MSFTNGP15.phx.gbl...
> > The two things I want to ensure are: 1) The error messages
> > are reliably sent with accurate information, and 2) No errors occur in
the
> > trigger that would cause the INSERT to rollback -- because we would lose
> > all
> > record of the error.
>
> #2 is problematic because an error in the trigger will rollback the entire
> transaction, including the base table INSERT. However, I don't see a
> problem with accessing the base table text column with this technique.
The
> data is uncommitted but the auxiliary table INSERT/SELECT runs in the same
> transaction as the trigger.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Mike Jansen" <mjansen_nntp@mail.com> wrote in message
> news:eei$sYSvEHA.3908@TK2MSFTNGP12.phx.gbl...
> >> You'll have to access it from the core table (hopefully you have a
> >> primary
> >> key so you can easily identify the modified row(s)). You can't access
> >> the
> >> TEXT data directly from inserted/deleted; I think only the pointer is
> >> available. Triggers are performance problems as is, I wouldn't want to
> >> throw in TEXT handling as well.
> >
> > That's what I'm doing. I put a simplified example in my first post.
I've
> > got everything working; that's not the issue. My original question was:
> > Apart from the concern that triggers in general are a performance
problem,
> > does anyone see any problems with accessing the inserted records
directly
> > in
> > the core table (which I link via the primary key: ...FROM inserted JOIN
> > core
> > ON inserted.core_pk = core.core_pk) from within the trigger? The
quantity
> > of records for the forseeable future isn't great; performance is not my
> > main
> > concern in this particular case. Are there other potential issues that
I
> > could run into that are specific to accessing uncommitted data in the
core
> > table from within a trigger (data that has been inserted within the same
> > transaction)? The two things I want to ensure are: 1) The error
messages
> > are reliably sent with accurate information, and 2) No errors occur in
the
> > trigger that would cause the INSERT to rollback -- because we would lose
> > all
> > record of the error.
> >
> > Thanks,
> > Mike
> >
> >
> >
>
>
- Next message: Brian Moran: "Re: Statistics Time"
- Previous message: Vishal Parkar: "Re: Help with a select statement I creating."
- In reply to: Dan Guzman: "Re: TEXT field in trigger; valid workaround?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
