Post reply

Executing a VB app from a insert trigger

  • November 22, 2002 at 2:30 pm

    #158693

    How do I go about executing a VB Application from a insert trigger

  • November 22, 2002 at 2:42 pm

    #442162

    To the extent I know about SQL server we cannot execute the VB app. inside a trigger.

  • November 22, 2002 at 3:39 pm

    #442171

    Here's a different angle, can you invoke any kind of executable in a trigger

  • November 22, 2002 at 4:05 pm

    #442176

    You can use xp_cmdshell to run an exe or use the sp_oa.. stored procedures to interact with a com dll.

    However doing this in a trigger is not advised. It will really hinder performance. you are better to populate a table and scan that table for inserts

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 22, 2002 at 6:27 pm

    #442185

    I need to have the ability to kick off my application when and only when a new record is inserted. Scheduling the application to kick off at intervals is not an option. How can I do a row level scan without using triggers

  • November 23, 2002 at 3:54 am

    #442194

    You can use triggers but the insert that caused the trigger to fire will have to wait for your app to finish before the insert will complete.

    If in your trigger you insert a row in another table and then you have a scheduled job that runs and checks every 10s if a record has been inserted into the other table

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 25, 2002 at 5:50 am

    #442250

    Scott,

    I'm shortly to be working on a project which needs to do just what you have descibed. My idea at the moment is to create a DLL which is called by the stored procedure (using sp_OA stuff). The DLL then in turn does some minimum processing, starts a VB app then ends - releasing the INSERT trigger. The VB app on the SQL server can then continue for as long as required.

  • November 25, 2002 at 5:59 am

    #442251

    Hey mhemp,

    Interesting angle. Two questions:

    1) If I create a insert trigger that calls a .bat or.exe that in turn calls up the VB app, will that have the same effect of releasing the trigger as soon as the .exe calls up the VB app.

    2) Could you define what you mean by "minimum processing"

  • November 25, 2002 at 6:28 am

    #442256

    It won't work. You would have to create a new thread under which the application runs.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 25, 2002 at 8:29 am

    #442276

    Simon,

    How would I get the VB application to operate under a new thread?

  • November 25, 2002 at 8:32 am

    #442278

    Don't know, anything like that gets messy with VB. I would definitely prefer the other mechanism of inserting into a second table and polling that table

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 25, 2002 at 8:38 am

    #442281

    On UNIX, you can execute a program in the background ( & at the end OR nohup ). This means the trigger ( xp_cmdshell ) gets a signal that the command execution is completed.

    You have to find out a similar way to execute your BAT or EXE file in the background on WINDOWS so that the trigger gets released immediately.

    I hope you will find it out and post back the result.

    -- Chandra

  • November 25, 2002 at 12:38 pm

    #442339

    How about triggering a scheduled job to run which can do the work and allows the trigger to return. Up scaling maybe a problem if trigger is slow!

  • November 25, 2002 at 4:37 pm

    #442365

    Starting the job is a good way. Lets you do async execution. Whether you start the job directly or put a row in a table and have the job run every 1 min to check, both work. Side note - this is a great way to let users run things indirectly for which they dont normally have access.

    Andy

    http://qa.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • December 2, 2002 at 3:34 am

    #442736

    quote:

    Could use a VB DLL, inserting a class that has a pulic procedure.

    Call the procedure and kick of an asynchronous task from a timer

    in VB and return to the procedure and exit.

    The focus should be returned to SQL Server immediately and the VB task is fired by the timer function in the VB bas module. I have done this successfully in VB over COM and DCOM in the past.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply