Update trigger or ?

[Joerg Gempe]

Hello,

I've a problem where some data gets updated but I don't know which
process (SP) is responsible for it (it's an old installation which I've
taken over).
Is it somehow possible to know which process/user/SP performs an update
on a special table/column?
I was thinking about to implement an update trigger but unfortunately
I've no idea how to figure out the "parent process" which was
responsible for the update.

I'm using MS SQL Server 2000.
Thanks Joerg

[Lagbehind]

On 23 Apr, 11:25, Joerg Gempe <j_spam_filter_ [e-mail] te:

Hello,

I've a problem where some data gets updated but I don't know which
process (SP) is responsible for it (it's an old installation which I've
taken over).
Is it somehow possible to know which process/user/SP performs an update
on a special table/column?
I was thinking about to implement an update trigger but unfortunately
I've no idea how to figure out the "parent process" which was
responsible for the update.

I'm using MS SQL Server 2000.

Thanks Joerg

[Erland Sommarskog]

Joerg Gempe (j_************ *****@gmx.de) writes:

I've a problem where some data gets updated but I don't know which
process (SP) is responsible for it (it's an old installation which I've
taken over).
Is it somehow possible to know which process/user/SP performs an update
on a special table/column?

This query:

SELECT o.name, o2.name, c.name
FROM sysobjects o
JOIN sysdepends d ON o.id = d.id
JOIN sysobjects o2 ON d.depid = o2.id
JOIN syscolumns c ON d.depid = c.id
AND d.depnumber = c.colid
WHERE o2.name = 'yourtbl'
AND c.name = 'yourcol'
AND d.resultobj = 1

may return the information you need. I say may, because the dependency
information in a database is rarely complete. This is because if you
drop and recreate a table, without reloading the stored procedures,
the depencies are lost.

If it's possible for you to build the database from scripts, and
making sure that procedures and triggers are built after all tables,
then your odds are better.

I was thinking about to implement an update trigger but unfortunately
I've no idea how to figure out the "parent process" which was
responsible for the update.

DBCC INPUTBUFFER could address this, but:

1) it requires the user to have sysadm privileges.
2) it will only show you the command sent from the client. If procedures
nest in several levels, this information may not be sufficient.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Joerg Gempe]

Erland Sommarskog wrote:

This query:

SELECT o.name, o2.name, c.name
FROM sysobjects o
JOIN sysdepends d ON o.id = d.id
JOIN sysobjects o2 ON d.depid = o2.id
JOIN syscolumns c ON d.depid = c.id
AND d.depnumber = c.colid
WHERE o2.name = 'yourtbl'
AND c.name = 'yourcol'
AND d.resultobj = 1

Hello,

Thanks for this, this already helps a little bit and I know now all the
SPs, but it is unfortunately not 100% what I'm was looking for.

I want to know at runtime who or what changes a value in a specific
table,column,ro w.
Problem is that it might not only a SP but a plain SQL statement or
another trigger or ... ?

I want to create an Update trigger to capture the old value, the new
value and which process/user/sql command performed the update.
So roughly spoken I'm looking for something like:

create trigger getCaller
on MyTable
for update
as
--- problem how to figure out the caller who's responsible that the
trigger is called
select @caller = .?.?.?.
---
insert into MyCallerTable select @caller, getdate(), * from deleted,
inserted
....
Where @caller should give me as much information as possible about the
process which "runs" the trigger.

Thank you
Joerg

[Erland Sommarskog]

Joerg Gempe (j_************ *****@gmx.de) writes:

I want to know at runtime who or what changes a value in a specific
table,column,ro w.
Problem is that it might not only a SP but a plain SQL statement or
another trigger or ... ?

Triggers are not a problem. They are captured by the above.

If you have applications emitting loose SQL statements without stored
procedures, you now see one reason why you should not have this. I guess
you will have to first search the code for the table name, and then
weed out the UPDATE statements.

I want to create an Update trigger to capture the old value, the new
value and which process/user/sql command performed the update.
So roughly spoken I'm looking for something like:

create trigger getCaller
on MyTable
for update
as
--- problem how to figure out the caller who's responsible that the
trigger is called
select @caller = .?.?.?.
---
insert into MyCallerTable select @caller, getdate(), * from deleted,
inserted
...

As I said, to do this at run-time DBCC INPUTBUFFER is your only option. But
it will only work if the caller has sysadm privileges. And it's not going to
help the throughput of the application.

It's an uphill battle, I'm afraid.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx