Menu
We're trying to pinpoint the source of some unexpected updates happening on a SQL Server table. What I'd like to do is create a trigger on that table monitoring that column we're interested in, and when updates occur, write some audit info into a separate table.
Works great and fine for things like user name, date, old and new columns values (from the Inserted and Deleted trigger tables) - but I'd like more :-)
In SQL Profiler, you can see the name of the app that's connected to SQL Server in the profiles - the part that can be defined in the connection string as Application Name:
Data Source=(local);Initial Catalog=AdventureWorks;
Integrated Security=True;Application Name="My Application"
Is there any way in a T-SQL FOR UPDATE trigger to get at this information?
350
SQL Server COLUMNS_UPDATED() Function for Triggers. This function is used to know the inserted or updated columns of a table or view. It returns a VARBINARY stream that by using a bitmask allows you to test for multiple columns.
AFTER UPDATE Trigger in SQL is a stored procedure on a database table that gets invoked or triggered automatically after an UPDATE operation gets successfully executed on the specified table. For uninitiated, the UPDATE statement is used to modify data in existing rows of a data table.
To view database level triggers, Login to the server using SQL Server management studio and navigate to the database. Expand the database and navigate to Programmability -> Database Triggers. To view triggers at the server level, Login to Server using SSMS and navigate to Server Objects and then Triggers folder.
A trigger is a special type of stored procedure that automatically runs when a language event executes. Because of the Microsoft SQL Server integration with the . NET Framework common language runtime (CLR), you can use any . NET Framework language to create CLR triggers.
SELECT APP_NAME()
Marc_S the sP_whoIsActive stored Procedure probably will solve Your problems as posted here
and it's awesome part of T-SQL by the way
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
