how to call external exe from Database Trigger

Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)
Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs
|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh
|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:

> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...[vbcol=seagreen]
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote: