locked
Run Program after table uptade RRS feed

  • Question

  • hi

    I have a monitoring tool that collects data from a server. these data are stored in a table.
    how can I run a function that calls an external program after an insert or update event on a table in my database?

    Wednesday, September 18, 2013 7:09 AM

Answers

  • It would be great if you can think of some ASYNCHRONOUS method rather than a synchronous(whenever a change happen, then the external call using TRIGGER or something). You can use SQL Agent Job to call the external function(CLR assembly) to do the job for all the records accumulated for the period defined. This way you can eliminate nay performance issues with synchronous method. 

    I wont suggest to go ahead with TRIGGER at all for its performance impact which will be huge for monitoring tools.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by SQLZealots Wednesday, September 18, 2013 7:20 AM edit
    • Proposed as answer by SimpleSQL Wednesday, September 18, 2013 8:23 AM
    • Marked as answer by Allen Li - MSFT Thursday, September 26, 2013 1:23 PM
    Wednesday, September 18, 2013 7:17 AM
  • As Latheesh I would like to suggest to implement an asynchronous method and that can be implemented with SQL Server Service Broker + External Activator.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by SimpleSQL Wednesday, September 18, 2013 8:24 AM
    • Marked as answer by Allen Li - MSFT Thursday, September 26, 2013 1:23 PM
    Wednesday, September 18, 2013 7:22 AM

All replies

  • See about triggers 

    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Wednesday, September 18, 2013 7:14 AM
  • It would be great if you can think of some ASYNCHRONOUS method rather than a synchronous(whenever a change happen, then the external call using TRIGGER or something). You can use SQL Agent Job to call the external function(CLR assembly) to do the job for all the records accumulated for the period defined. This way you can eliminate nay performance issues with synchronous method. 

    I wont suggest to go ahead with TRIGGER at all for its performance impact which will be huge for monitoring tools.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by SQLZealots Wednesday, September 18, 2013 7:20 AM edit
    • Proposed as answer by SimpleSQL Wednesday, September 18, 2013 8:23 AM
    • Marked as answer by Allen Li - MSFT Thursday, September 26, 2013 1:23 PM
    Wednesday, September 18, 2013 7:17 AM
  • ALTER TRIGGER [dbo].[Notifications]
    on [dbo].[Company]
    INSTEAD OF INSERT
    AS
    
    EXEC xp_cmdshell 'notepad.exe'
    it can be something like this, (not tested)

    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog


    Wednesday, September 18, 2013 7:17 AM
  • As Latheesh I would like to suggest to implement an asynchronous method and that can be implemented with SQL Server Service Broker + External Activator.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by SimpleSQL Wednesday, September 18, 2013 8:24 AM
    • Marked as answer by Allen Li - MSFT Thursday, September 26, 2013 1:23 PM
    Wednesday, September 18, 2013 7:22 AM