how to change colum values in a trigger

Hi

Does anyone of you know how I can create a trigger to do the following

Table tbs contain 3 columns : total, num_col and alpha_col

I'm doing an insert in table tbs:
insert into tbs (total) values ('111aaa')

Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.

I'm working on a MS SQL Server 7

Can anyone help ?!?!?I am using sql server 2000. I don't know if INSTEAD OF TRIGGERS are available in that version. But here is a piece of code that I came up with at least to deal with the parsing of the string. This assumes that the numeric part is always at the beginning. You could use a cursor inside the trigger to process the info or if you don't want to use cursors you could write the parsing of the string as a function and use that in your SELECT statement for inserting into your table.

DECLARE @.vInput VARCHAR(20);
DECLARE @.vNum VARCHAR(10);
DECLARE @.vAlpha VARCHAR(10);
DECLARE @.vPosition INTEGER;
DECLARE @.vNumPos INTEGER;

SET @.vInput = '111aaa';
SET @.vNum = '';
SET @.vAlpha = '';

SET @.vPosition = 1;
WHILE @.vPosition <= DATALENGTH(@.vInput)
BEGIN
WHILE ASCII(SUBSTRING(@.vInput, @.vPosition, 1)) BETWEEN 48 AND 57
BEGIN
SET @.vNum = @.vNum + SUBSTRING(@.vInput, @.vPosition, 1);
SET @.vPosition = @.vPosition + 1;
END -- while number
SET @.vAlpha = @.vAlpha + SUBSTRING(@.vInput, @.vPosition,1);
SET @.vPosition = @.vPosition + 1;
END -- while string

print @.vNum;
print @.vAlpha;sql

How to capture the entire DML statement in a DML trigger?

Hi,

In Yukon, is it possible to capture the entire DML statement with the parameter values that triggered the DML trigger inside the trigger body?

Basically trying to see the equivalent as the eventdata() in a DDL trigger, that provides the CommandText()

Rgds

No. This is not possible.|||Thanks for the quick reply UJ.
Was just trying to create a detailed Audit trace. (DDL, DML, App Login & Logout)

Can a CLR Trigger extract such data from the SQL Profiler - say using SMO in Yukon etc. - i'm not sure.
Though this would make it a very heavy trigger, but then we can run it async.

Rgds|||SMO cannot be used within CLR trigger right now. It is not supported. Also, using profiler to do these type of actions from trigger code is not ideal. Triggers should usually be very light weight and efficient. The more complex logic that you put inside your trigger the harder it is in terms of performance, development and management. Also, I am not sure how useful it will be to know which statement actually caused the trigger to fire. Note that this might not be very straight-forward too. For example, the update trigger might be fired by UPDATE statement from different SPs or triggers even. In this case, it might be useful to know the SP call that fired the trigger indirectly. What you are asking for is a call / stack trace which is not available in T-SQL now.|||Thanks UJ.
Shall keep you posted on this.

Can we expect SMO in triggers/ Call stack in Yukon?

How To call TRIGGER in VB.net 2005

i hav written a trigger in SQLEXPRESS DB but i dnt knw how to call in vb.net 2005...

Move the source code from trigger to stored proc, create the stored proc with moved code & call the SP from your ADO.NET.

|||

You do not call triggers. They fire when the event, it is related to, occurs. Check BOL for more information about triggers.

AMB

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.googlegroups.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.googlegroups.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.googlegroups.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...
> 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)
>>

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] fro
m
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.googlegroups.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] f
rom
> 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.googlegroups.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] f
rom
> 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.googlegroups.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] f
rom
: 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] f
rom
> 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:
>

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:

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.googlegroups.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.googlegroups.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.googlegroups.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...
> 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:
>

how to call a VB program from a trigger of SQL Server

Hi,

I have a VB program to sit somethere. I like to call that program with passing two parameters from a trigger on the SQL Server. The parameters are the contents of the table which the trigger is created on.

Is there any way to achieve that?

Thanks a lot!

Regards,

Kevin JinYou need create an external stored procedure.

how to call a batch file from trigger

Hi,
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
Sunil
Hi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi,
I have a requirement to invoke a Java application and update the remote MySQ
L database, when a particular field in MSSQL Server 2000 is updated. my idea
is to write a trigger on AFTER update of field and from trigger invoke a ba
tch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger
or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote mysql instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to mysql is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100
,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a Java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run Java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run java application
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated
Thanks In advance
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to calculate cost of a trigger?

I wanna used derived fields to improve select performance of my system.

How can I calculate the trigger cost in the system?

Triggers are resource intensive objects, you should look for alternatives before implementing them. You can run the profiler to find out the cost of any code you run through SQL Server. You will find the Profiler under tools in Management studio. Hope this helps.|||I wanna usedderived fields to improve select performance of my system.

How can I calculate the trigger cost in the system?

We are designing the DATA MODELand The TASK table it has two mutually exclusive fields.
WHOID > PersonID,OrganizationID
WHATID > DocumentID,EstateID,LeadID,RequirementID,

and on the Task UI we display the names of the Related EntityTypes and this causes us 10 inner/Joins to pull the data from database.

One of the designer in the team suggested putting derived fields such as
WHONAME > PersonName,OrganizationName
WHATNAME > DocumentName,EstateType,LeadName,RequirementName

So from performance perspective,We are really confused as putting triggers cause implicit locks on the system where as the other option causes 10 joins.

There is one to many relationship between WHOID and TASK(..3 related entitytypes mutually exclusive)
and also there is one to many relationship between WHATID and TASK(..7 related entitytypes mutually exclusive)

7 joins + 3 joins = 10 joins.

it is a really simple UI infact.
The Edit screen of TASK EDIT
--------------
Subject > char
FromDate > datetime
Todate > datetime
Priority > char
State > char
Email > char
Whoname> Related Person,Organization or Lead NAME
WhatName>Related Document,Estate,Requirement,Case,Oppurtunity Name ..etc

------
I would love not to have ten joins but the requirement forces us that way|||

What you need is simple DRI(declarative referenctial integrity) enabled on the tables in Management studio, so Updates will Cascade and if needed Deletes will Cascade. I know you need a DDL( data definition langauge) person in your team because a Table design comes down to files and association, while relationship is determined by upper and lower bound Cardinality. If you follow that most of your tables will disappear and what is now a JOIN will become a simple additional condition which in SQL Server can be added with the AND operator to a JOIN operation.

Some people do just DDL(data definition language) for a living. Run a search for DRI(declarative referential integerity) in SQL Server BOL (books online). Hope this helps.

|||

thanks caddre.that looks interesting.

http://www.cvalde.net/document/declaRefIntegVsTrig.htm

but I have a hard time understanding how it will change my WHONAME and WHATNAME.

if the it does it would be great.

|||

I could be wrong but what you have looks like objects to me because if those are two tables you need just two IDENTITY or it becomes a composite. Try the link below and download and install AdventureWorks 2005 most tables you need in a business application is in there. Look at the tables pay attention to the constraints and DRI (declarative referential integrity). Hope this helps.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||

Is there a problem with the 10 joins?

Personally, I would just create a view to simplify the select statement that has all your required joins in it. Just make sure your tables are indexed properly, and you shouldn't have an issue.

|||

Hmmm I have tested it with 10 million records and the derived approach is 20X faster.

I really don't wanna use triggers but for now I'll just try the links caddre send.

|||

If you aren't displaying all 10 million rows at a time within the UI, you may find it beneficial to do your limiting select first, then doing the 10 joins once your have your limited resultset back, kinda like:

SELECT ...
FROM (Your limited query here)
JOIN ...
JOIN ...
JOIN ..

although why SQL Server isn't already doing that for you is odd. A bad query plan, or inaccurate statistics most likely. Before spending the time on trying to work around SQL, I'd first run your query through the tuning wizard and see what it comes up with, you may be suprised. If you've already done that, my appologies.

You want to try an indexed view approach as well, which may give you what you are looking for. Something like:

CREATE MyView WITH SCHEMABINDING AS

SELECT (Your 10 fields here),t1.Name,t2.Name,t3.Name,t4.Name,...
FROM MyTable mt
JOIN EntityRef t1 ON (mt.key=t1.key)
...

Then create a primary key on the view, then create your indexes (Whatever you are limiting/searching on)

You'll incur much of the same overhead as triggers though, so test your results.

|||

As a side note, Caddre has mentioned a lot of differing technologies, one of which is DRI. DRI by itself won't help to solve your issue, but by leveraging it, if possible, it could reduce the complexity of your database design, and increase your performance.

For example, take table1, and table2 here:

Table Name > Fields

Table1>EntityID,col1,col2,col3,col4...
Table2>EntityID,Name

Normally DRI is used to link both entityIDs in table1 and table2 in a foreign key relationship. This by itself will not gain you any performance at all, infact in many operations it will decrease overall performance substantially. However, if you redesign your tables, like so:

Table1>Name,col1,col2,col3,col4...
Table2>Name

Now by using DRI to maintain the relationship between Name (via a foreign key), then it's quite possible you will see a performance increase since you have just eliminated a join in your selects. Foreign keys can be used to "replicate" changes in the name field done in table2 to all records in table1 that use that name Now that's one VERY expensive update, but, it does insure that all instances in table1 that link to a particular name all use the exact same name. This is usually why you use identifiers in your base tables to begin with, and since those changes are very infrequent (Or should be), it's a decent trade off in performance, but you can make a pretty big mess of your database structure (and data) by taking this route if you aren't careful though. These in SQL Server are also called Cascading Deletes and Updates.

Using our above new schema, if you issue this:
UPDATE table2 SET Name='new value' where Name='old value'
SQL Server will actually do something like this:
BEGIN TRANSACTION
UPDATE table2 SET Name='new value' where Name='old value'
UPDATE table1 SET Name='new value' where Name='old value'
COMMIT TRANSACTION

With better error handling of course. If there are any errors, it's all rolled back, but it's called cascading be cause a delete in table2 would delete some records in table1, which in turn may delete some records in table3, etc.

Now with all that out of the way, you have to realize by replacing your small little ID field with a probably much larger field, sure you lose the join, but you've increased your row size. Indexes will be larger, and pages will contain less rows. Operations that require to scan large portions of your table will take longer, as well. So test a lot, on not just on what you need to do now, but think and test the ramifications on all your other queries as well.

|||

I have thought another method today which might be wrong ...instead of using DRI and triggers to maintain the data I am thinking about writing a class just for the purpose of putting data and updating data into my derived field.And I will tell from code behind that I need to update in anycase the update happens in my main tables of derived coloumns.

The advantages of this approach over triggers.

1.No implicit transactions

2.easy to debug

3.Faster than triggers

4.No unneccessary updates

5.Could write code to maintain and check the alldata.

Disadvantages I see

1.Triggers are more safe in terms of integrity.

2.Less chance to forget to update the derived fields.

*I am really confused some how but the requirement of the application forces us not to use three things that will load the SQL CPU load.

A:Triggers

B.UNIONS,Exits

C.Joins

|||

I fail to see how most of what you've listed as "advantages" are advantages. I would say:

Advantages:
1. No implicit transactions (All SQL Statements are an implied transaction, you haven't eliminated them, just simplified it -- But that's an advantage still)
2. Easy to debug -- For your application, if it's the only one accessing the data, yes.
3. Faster than triggers -- I doubt it. Sounds like you are going to have to make an extra round trip between the server and client to pull back the name field that you want to put in the field, which will make it many times slower, but holding locks for slightly less time. Unless you are caching the table(s) the 10 joins are getting their names from, in which case, you need to add the added application/web server memory cost as a disadvantage as well.
4. No unneccessary updates.
5. Could write code to maintain and check the alldata. -- This is not an advantage. It's a solution to the problem caused by the derived fields. It should be listed under disadvantages as "Have to write code to maintain and check the alldata".

Disadvantages:
1. Less safe in terms of integrity than triggers.
2. Possible data out of sync.
3. Increased application code, and extra logic that must be placed into every application that might need to update the data (or each routine within your application).
4. Have to write code to periodically maintain and check database integrity.

--

Please look into the indexed views I mentioned earlier, which doesn't suffer from the integrity issues, doesn't require more application logic (Other than you select from one view, and update a base table, but you're doing that from stored procedures now anyhow, right?) If the indexed views don't work for you, you could also use a BEFORE UPDATE/INSERT trigger, which aren't the nicest thing in the world to write, but keeps many of your listed advantages, and eliminates the disadvantages. (Guaranteed database integrity + no unneccessary updates + smaller lock time + faster than a client-side lookup)

As for your confusion on what you shouldn't use... (Triggers, UNIONS, Exits (did you mean EXISTS?), and joins)... Sounds like you've gotten information from someone who fell in love with a dumb record manager from the 1980's (Paradox, BTrieve, ISAM, or MySQL -- granted the latter is evolving beyond it's dumb record manager roots).

|||

I agree with Motley, because there is a reason the Object world have not created a replacement for Peter Chen 1976, the math that gave us what is now DRI(declarative referential integrity). I think you will run into data integrity problems as your application grow.

But the last part of your post is ok except JOINS because INNER JOINS with the AND operator will not eat up your CPU if you use management studio to show you execution cost and use the Profiler to optimize the cost of your SQL statement.

|||

indexed views > I would Love to use them butif I am not mistaken they have lots of rules to follow and I got to buy the enterprise editon and the 4 CPU price of SQL is4 X 20.000$where the standart edition is 4 X 6.000 USD for web applications.

for now I 'll be taking the risk of replication data inorder to have performance.

*I prefered to have a normalized and a clean data model where I can reuse the data logic without dublicating it.Pratically I could not find a way out for that in the physical design.

**Faster than triggers --> I might use threads.(which might mean more trouble but...)

|||

You are mistaken. They work in standard edition (Even SQL Express/MSDE), however, you must name the view directly. In enterprise edition if you have an indexed view, and a select/update doesn't mention the view, but it would benefit from it, it will automatically use it.

However, you are correct, there are some limitations, one of which is no LEFT JOINS, and no UNION. Just do them later (In another view that bases itself off the indexed view and left joins/unions with other data).

The nice part is, you can build it, test it, and see if it'll fit your needs in a matter of minutes.