|
|
Posted by Erland Sommarskog on 04/25/07 15:31
Joerg Gempe (j_spam_filter_gempe@gmx.de) writes:
> 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 ... ?
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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|