Any way to find out which SP is updating data in a specific table?

Can you create an UPDATE TRIGGER and use some type
of code to figure out which SP just updated the current table?

If not how can i achieve what i want?

I tried to run SQL Profiler and i don't understand why i can't
simply have the Profiler filter events only for the specific database id
and the table's object id i chose?

What am i doing wrong with SQL Profiler? I was testing this
through SQL EM. I had the filters chosen for a specific database id
and a specific table's object id, yet when i open another table SQL
Profiler captures that information too.

Thank youIn a correct design, why would it matter? The event or conditions
rather than the agent should be what is important. Do not think in
terms of HOW, but in terms of WHAT.|||serge (sergea@.nospam.ehmail.com) writes:
> Can you create an UPDATE TRIGGER and use some type
> of code to figure out which SP just updated the current table?

No. At least not without changing all stored procedure to write their
name somewhere. That can be done in a general way, as the global variable
@.@.procid holds the object id of the currently executing SQL module.
But there is no way to get the entire call stack. Definitely a missing
a feature in SQL Server.

> I tried to run SQL Profiler and i don't understand why i can't
> simply have the Profiler filter events only for the specific database id
> and the table's object id i chose?

The problem with Profiler is that if there are entries that do not
populate the columns you filter on, the NULL values pass the filter
and give you a lot of noise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

any method find the objects changed

i want to know is there any way to check any objects (table schema, sp,
trigger) changed at a period of time. say 5 days before.
i want to prepare the scripts for those modified objectsMullin wrote:
> i want to know is there any way to check any objects (table schema,
> sp, trigger) changed at a period of time. say 5 days before.
> i want to prepare the scripts for those modified objects
Not really... The crdate column in the sysobjects table always contains the
create date, not the last updated date.
But see:
Compare SQL Server Databases with sp_CompareDB
http://www.sql-server-performance.c...mparison_sp.asp
There is also a bunch of tools avaliable on the market for database
comparing, from www.apexsql.com or www.red-gate.com
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sounds to me like you need change management for your SQL source code...
There are alot of tools out there that will compare, but for what you describe DB Ghost
is the solution. Check out www.dbghost.com|||Hi,
Try dbMaestro. It's a product that allows comparison, migration and archivin
g of database schema and data, and display the changes in tables with beauti
ful gui.
You can find it here:
http://www.extreme.co.il|||Have a look at this
http://www.nigelrivett.net/DMOScriptAllDatabases.html
It will give you the day things changed - depending on how often you run
it.
and this
http://www.nigelrivett.net/SQLServerReleaseControl.htm
Which will save you heving to do the previous one.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Any Column Updated / Inserted Trigger

Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.>Hi,
>I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
>this is possible. I'm running SQL Server 2000 on Win2k Server
>I have one table with a large number of columns. I have two pieces of
>logic that I'd like to execute depending upon whether an insert or an
>update statement was executed on that table. I'd prefer this
>execution to occur from within a single trigger. If a row is
>inserted, then I would like to execute logic A. If ANY column in the
>table is updated, then I'd like logic B to be executed.
>Is it possible to just determine if only "insert" or an "update"
>ocurred from within the a single Trigger, without specifying each
>individual column name? (I.E. not saying IF udpate(col1) or
>update(col2) or ect...) Is it possible to just perform a check on the
>process that occurred, irregardless of column? Like If INSERTED =
>TRUE then execute insert logic. If UPDATED = TRUE, then run the
>updated logic. I would like for all of this code to be stored within
>the same trigger.
>If anyone can provide some sample code on how to do this, if at all
>possible, I would be much appreciative.
>Thanks,
>-Rigs
>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Heres a real quick and dirty trigger example

CREATE TRIGGER trgtblClients ON tblClients
FOR INSERT, DELETE, UPDATE AS

DECLARE @.ChgInsert CHAR(1)
DECLARE @.ChgDelete CHAR(1)
DECLARE @.ChgCode CHAR(1)
SET @.ChgInsert = 'N'
SET @.ChgDelete = 'N'
SET @.ChgCode = 'N'

IF exists(select top 1 FROM inserted)
SET @.ChgInsert = 'Y'

If exists(select top 1 from deleted)
SET @.ChgDelete = 'Y'

/* Check for a insert */
IF @.ChgInsert = 'Y' AND @.ChgDelete = 'N'
Begin
SET @.ChgCode = 'I'
End

/* Check for a change */
IF @.ChgInsert = 'Y'AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'C'
End

/* Check for a delete */
IF @.ChgInsert = 'N' AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'D'
End

IF @.ChgCode = 'C'
BEGIN
/* DO YOUR CHANGE PROCESSING HERE */
END
IF @.ChgCode = 'I'
BEGIN
/* DO YOUR INSERT PROCESSING HERE */

END
IF @.ChgCode = 'D'
BEGIN
/* DO YOUR DELETE PROCESSING HERE */
END

Randy
http://members.aol.com/rsmeiner|||On 15 Apr 2004 13:01:40 -0700, Rigs wrote:

>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Hi Rigs,

Why are you trying to avoid using 2 seperate triggers?

I see a case for combining insert and update triggers if use need to
execute the SAME code on insert and on update. But since you have to
execute different code for each case, I'd think that using two
triggers provides a better documented system that's easier to
understand and easier to maintain. And your performance will improve
as well (allthough so little that you won't notice).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Any Column Updated / Inserted Trigger

Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.>Hi,
>I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
>this is possible. I'm running SQL Server 2000 on Win2k Server
>I have one table with a large number of columns. I have two pieces of
>logic that I'd like to execute depending upon whether an insert or an
>update statement was executed on that table. I'd prefer this
>execution to occur from within a single trigger. If a row is
>inserted, then I would like to execute logic A. If ANY column in the
>table is updated, then I'd like logic B to be executed.
>Is it possible to just determine if only "insert" or an "update"
>ocurred from within the a single Trigger, without specifying each
>individual column name? (I.E. not saying IF udpate(col1) or
>update(col2) or ect...) Is it possible to just perform a check on the
>process that occurred, irregardless of column? Like If INSERTED =
>TRUE then execute insert logic. If UPDATED = TRUE, then run the
>updated logic. I would like for all of this code to be stored within
>the same trigger.
>If anyone can provide some sample code on how to do this, if at all
>possible, I would be much appreciative.
>Thanks,
>-Rigs
>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Heres a real quick and dirty trigger example

CREATE TRIGGER trgtblClients ON tblClients
FOR INSERT, DELETE, UPDATE AS

DECLARE @.ChgInsert CHAR(1)
DECLARE @.ChgDelete CHAR(1)
DECLARE @.ChgCode CHAR(1)
SET @.ChgInsert = 'N'
SET @.ChgDelete = 'N'
SET @.ChgCode = 'N'

IF exists(select top 1 FROM inserted)
SET @.ChgInsert = 'Y'

If exists(select top 1 from deleted)
SET @.ChgDelete = 'Y'

/* Check for a insert */
IF @.ChgInsert = 'Y' AND @.ChgDelete = 'N'
Begin
SET @.ChgCode = 'I'
End

/* Check for a change */
IF @.ChgInsert = 'Y'AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'C'
End

/* Check for a delete */
IF @.ChgInsert = 'N' AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'D'
End

IF @.ChgCode = 'C'
BEGIN
/* DO YOUR CHANGE PROCESSING HERE */
END
IF @.ChgCode = 'I'
BEGIN
/* DO YOUR INSERT PROCESSING HERE */

END
IF @.ChgCode = 'D'
BEGIN
/* DO YOUR DELETE PROCESSING HERE */
END

Randy
http://members.aol.com/rsmeiner|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> On 15 Apr 2004 13:01:40 -0700, Rigs wrote:
> >PS I know I could do this with 2 seperate triggers, but I'm trying to
> >avoid that.
> Hi Rigs,
> Why are you trying to avoid using 2 seperate triggers?
> I see a case for combining insert and update triggers if use need to
> execute the SAME code on insert and on update. But since you have to
> execute different code for each case, I'd think that using two
> triggers provides a better documented system that's easier to
> understand and easier to maintain. And your performance will improve
> as well (allthough so little that you won't notice).
> Best, Hugo|||On 15 Apr 2004 13:01:40 -0700, Rigs wrote:

>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Hi Rigs,

Why are you trying to avoid using 2 seperate triggers?

I see a case for combining insert and update triggers if use need to
execute the SAME code on insert and on update. But since you have to
execute different code for each case, I'd think that using two
triggers provides a better documented system that's easier to
understand and easier to maintain. And your performance will improve
as well (allthough so little that you won't notice).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> On 15 Apr 2004 13:01:40 -0700, Rigs wrote:
> >PS I know I could do this with 2 seperate triggers, but I'm trying to
> >avoid that.
> Hi Rigs,
> Why are you trying to avoid using 2 seperate triggers?
> I see a case for combining insert and update triggers if use need to
> execute the SAME code on insert and on update. But since you have to
> execute different code for each case, I'd think that using two
> triggers provides a better documented system that's easier to
> understand and easier to maintain. And your performance will improve
> as well (allthough so little that you won't notice).
> Best, Hugo