quastion about triggers

hi,

I have a few quastions about triggers:

1) I want that to run a specific function or exe, after a row update.

How can I run a function via the trigger?

2) Is ther any trigger that for an updae of a culomn (not a row)?

thanks,

moria

Hi Moria,
First of all, there are differences id you implement the trigger on a CLR basis (2005) or on a TSQL basis (<2005), so next time, try to give some information about your system, that the proposed solution will better fit your needs.
"How can I run a function via the trigger?"
You talked about executing a EXE, therefore you cann call the extended procedure SP_CMDSHELL which is able to execute a program via the command line (More information about that can be found in the BOL)
COMMENT: It′s odd to execute a command in a trigger. Trigger bahve synchronously, while executing the trigger other people using the data won′t be able to change the lock data (depends on your locking, worst case would be table locking). If there is an error in your command (or a long running command), like waiting for input or something, the trigger will never come back, so you have to
keep that in mind.

"Is ther any trigger that for an updae of a culomn (not a row)?"
Well, yes and no. A trigger is fired per statement (not per ROW) and regardless if your "watched" column changed or not, but if you are in the trigger you have the possibility to use the
IF UPDATE(SomeColumn)
DoSomething
But the trigger is fired regardless, if the column changed or not, keep that in mind, if you have more than this statement in your trigger.
HTH, Jens Suessmeyer.

|||

first of all, thank you for the detailed answer.

I am working with SQL 2000, and .NET 2005.

>>"More information about that can be found in the BOL"

what's BOL, and where can I find it?

>>we understand that an exe can block the SQL, but we want to know when a user updates a table so that we will run a certain program to take the new data, analyze it and reload it to another table. if not by an exe, how do you recommend to do it?

>>In case of deleted a column, is the trigger fired? which one?

thanks a lot,

moria.

|||

Hello again,

"what's BOL, and where can I find it?"
http://www.aspfaq.com/show.asp?id=2229

If this is an external program you sure have to use the exe, but what about wrting a entry in a table that something changed on the program running on an at basis or as a service checks the data, does its recalculation (or whatever) and marks the process as done in the table ? Thats are designing question which cannot be decided on the basis of the only few information given.

HTH, Jens Suessmeyer.

Quastion about Temp Tables

I'm relatively new to SQL-Server, and I have a quick question about
temporary tables. I am using a function from Erland Sommarskog's
excellent article about arrays in SQL server.

Erland's approach uses a user-defined function, which parses a string
of delimited values (integers) into a temporary table. The temporary
table is joined to tables in a stored procedure select statement to
limit the records returned.

The question is, what happens to the temporary table generated by the
function? Is it implicitly destroyed when the stored procedure
finishes? Does it need to be dropped explicitly (something not done in
his examples)?

Also, what are the implications of scope, if multiple users are
accessing the stored procedure through a web application?

Thanks in advance,

TimA temporary table created within a stored procedure is automatically dropped
when the procedure exists.

On 12 Feb 2004 12:59:53 -0800, tim.pascoe@.cciw.ca (Tim Pascoe) wrote:

>I'm relatively new to SQL-Server, and I have a quick question about
>temporary tables. I am using a function from Erland Sommarskog's
>excellent article about arrays in SQL server.
>Erland's approach uses a user-defined function, which parses a string
>of delimited values (integers) into a temporary table. The temporary
>table is joined to tables in a stored procedure select statement to
>limit the records returned.
>The question is, what happens to the temporary table generated by the
>function? Is it implicitly destroyed when the stored procedure
>finishes? Does it need to be dropped explicitly (something not done in
>his examples)?
>Also, what are the implications of scope, if multiple users are
>accessing the stored procedure through a web application?
>Thanks in advance,
>Tim|||[posted and mailed, please reply in news]

Tim Pascoe (tim.pascoe@.cciw.ca) writes:
> I'm relatively new to SQL-Server, and I have a quick question about
> temporary tables. I am using a function from Erland Sommarskog's
> excellent article about arrays in SQL server.

Glad to hear that you liked it!

> Erland's approach uses a user-defined function, which parses a string
> of delimited values (integers) into a temporary table. The temporary
> table is joined to tables in a stored procedure select statement to
> limit the records returned.
> The question is, what happens to the temporary table generated by the
> function? Is it implicitly destroyed when the stored procedure
> finishes? Does it need to be dropped explicitly (something not done in
> his examples)?

If you are using the version with the user-defined function, the table
is never stored on disk (at least conceptually), so you don't have to
bother.

If you are using the version where a stored procedure fills in a temp
table that was created prior to the call, that table is stored in disk.
However, as Steve J pointed out, a temp table disappears when the scope
it was created in goes away, so neither in this case you need to worry.

> Also, what are the implications of scope, if multiple users are
> accessing the stored procedure through a web application?

There are no issues with this. A temp table of the # variety are private
to the connection that created it. This applies as well to the return
table created by multi-step function.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp