Re: Triggering an application from SQL Server?

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Linn Kubler" <lkubler@xxxxxxxxxxxxxxxxxx> wrote in message
news:e2fJeFX%23HHA.748@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

I was just wondering would it be possible to have a trigger in a SQL 2000
database launch a VFP application to print a label? I think setting up
the trigger is pretty straight forward but having it launch a VFP
application is more tricky. I guess the trigger would have to pass a
couple parameters that the application would use for a look up.

Is this possible and what would I have to do on the VFP end to make it
work?

Thanks in advance,
Linn




For a number of reasons it wouldn't be sensible to attempt such a thing from
a trigger. A trigger executes in the context of a transaction - ie. *before*
that transaction has committed, so you could end up printing data for a
phantom transaction that later gets rolled back. You'll cause unnecessary
blocking by holding the transaction longer than you need to. Also, if your
VFP application opens a new connection to the database then you could
possibly induce an undetectable deadlock.

I'd say the "right" place to do this is within whatever process caused the
update in the first place - not within a trigger.

If you must do it from SQL code then do so *outside* a transaction. Use
xp_cmdshell to execute your program in a DOS shell. Another alternative
would be to use COM via the sp_OA procs (see sp_OACreate in Books Online)
but in my experience that approach is too prone to memory leakage.

Finally, have you considered using SQL Server Reporting Services to print
your labels?

Hope this helps.

--
David Portas



.

Relevant Pages

  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: Distirbuted Transaction and ODBC connection pooling
    ... That what the linked VFP tables should give you. ... COMMIT on ODBC connections that have a pending transaction at application ... And VFPs nested transactions have different semantics than SQL ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... creating another transaction within the trigger. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Error handling
    ... If you want to ignore errors in the trigger, you can add SET XACT_ABORT OFF ... transaction to be rolled back. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL BEFORE puzzle
    ... trying to figure out the justification for why a BEFORE trigger should ... On a pure SQL perspective, I am going to play devil's advocate... ... subsequent tables before the main commit would occur. ... commits to wait for the main transaction block to commit. ...
    (comp.databases.theory)