calling external program from SQL Trigger.....

calling external program from SQL Trigger.....

Post by Sandy Rya » Sat, 15 Nov 2003 22:06:13



here's what I'd like to do  --- but don't know if I can do - or how
I want to write a trigger that monitors a table, based on the information on the table (an event) I want the trigger to call an external (non SQL) application - say one that deletes a file from the directory.....

can this be done? Any examples?
thanks

 
 
 

calling external program from SQL Trigger.....

Post by Tom Morea » Sat, 15 Nov 2003 22:24:07


Calling an external program inside a trigger is generally a bad practice.
Triggers are supposed to be fast and running an external program can impact
its speed.  I'd opt for inserting a row into a table and then having a
scheduled job that kicks in every minute or so and processes the rows in the
table, deleting them when it's done.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


here's what I'd like to do  --- but don't know if I can do - or how
I want to write a trigger that monitors a table, based on the information on
the table (an event) I want the trigger to call an external (non SQL)
application - say one that deletes a file from the directory.....

can this be done? Any examples?
thanks

 
 
 

calling external program from SQL Trigger.....

Post by Eric Sabin » Sat, 15 Nov 2003 22:29:26


You don't have to call another application, you can do it from SQL, i.e.,
xp_cmdshell.  But you could use sp_oacreate, or even MSMQ.  But remember, if
the deletion gets hung up, by maybe a concurrent use issue, don't let your
trigger and your user hold on waiting.  That's why I'd probably go for MSMQ
if the frequency is high.

hth,
Eric


Quote:> here's what I'd like to do  --- but don't know if I can do - or how
> I want to write a trigger that monitors a table, based on the information

on the table (an event) I want the trigger to call an external (non SQL)
application - say one that deletes a file from the directory.....
Quote:

> can this be done? Any examples?
> thanks

 
 
 

calling external program from SQL Trigger.....

Post by oj » Sat, 15 Nov 2003 22:34:24


something like this...

create table t1(c1 int)
go
create trigger _tr on t1
for insert
as




go

--create file
exec master..xp_cmdshell 'echo this is a test >c:\file.txt'
-- insert into t1 to raise the trigger
insert t1 values(1)

--clean up
drop trigger _tr
drop table t1
go

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


Quote:> here's what I'd like to do  --- but don't know if I can do - or how
> I want to write a trigger that monitors a table, based on the information

on the table (an event) I want the trigger to call an external (non SQL)
application - say one that deletes a file from the directory.....
Quote:

> can this be done? Any examples?
> thanks

 
 
 

1. Calling an external program from a Trigger

I have an application (over which I have no control) that inserts
records into a SQL Server 6.5 hosted table.
Some of these records I would like to send of to an external program
as soon as they're inserted.
I was thinking that this might be possible with an INSERT trigger but
I'm not sure if a trigger can invoke an external program.
How would I go about doing this?
Regards,
Matthias

--

---------------------------------------------------------------------

2. How do make "communicating" datacombo:s

3. How do I call an external program from a trigger

4. ISQL and Linesize - newbie

5. Call an external program from a trigger

6. Datetime in group and order by clauses.

7. Calling external programs from triggers/stored procedure

8. Row Update Time Question

9. Calling external program from trigger

10. Newbie, How do I call external program/function from a SQL 2000 UDF

11. Help : Calling external programs in PL/SQL

12. calling an external program from pl/sql

13. calling external programs from PL/SQL