Re: TEXT field in trigger; valid workaround?

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 07:03:17 -0500

> 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
>
>
>

Relevant Pages

  • Re: Trigger Problem, Please Help
    ... I haven't done any CE stuff, but those error messages look like they ... tried looking at the user and role security for your DB? ... > Yes all of the tables referenced exist, and the trigger works fine if I ... > Source:Microsoft Sql server 2000 Windows CE Edition ...
    (microsoft.public.sqlserver.programming)
  • Re: insert and update in trigger
    ... Tibor Karaszi, SQL Server MVP ... > If I define the trigger on update and insert only, ... >> An insert statement can insert a single row, or with an INSERT/SELECT, it ... >>>> Kalen Delaney ...
    (microsoft.public.sqlserver.server)
  • Re: chipscope
    ... I used core inserter, and I have a ICON and a ILA core. ... it found 1 core unit in the JTAG device Chain. ... "trigger now" button, it does not give me the status of the lines. ... We are talking about versions 7.1 os ISE and chipscope pro. ...
    (comp.arch.fpga)
  • Re: No trigger exception message
    ... Did you have the same problem and found a workaround? ... i have a problem with the handling of error messages produced by a ... >> trigger. ... >> When i use MS-Access on one PC to inserted records in the table the error ...
    (microsoft.public.access.adp.sqlserver)
  • Re: TEXT field in trigger; valid workaround?
    ... > You'll have to access it from the core table (hopefully you have a primary ... Triggers are performance problems as is, ... ON inserted.core_pk = core.core_pk) from within the trigger? ... could run into that are specific to accessing uncommitted data in the core ...
    (microsoft.public.sqlserver.programming)