Posted by Joerg Gempe on 04/25/07 10:23

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,row.
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

[Back to original message]