March 5, 2009 at 2:35 pm
I am trying to implement table change log. For this we decided to create a BEFORE_UPDATE trigger. Is there a way to find out which stored procedure called the update on the table which in turn invoked the trigger?
Furthermore how can the data be sent back to the web application.
Any help would be much appreciated.
Thanks
March 5, 2009 at 2:44 pm
Hi
IF (OBJECT_ID('udp_test') IS NOT NULL)
DROP PROCEDURE udp_test
GO
CREATE PROCEDURE udp_test
AS
SELECT * FROM sys.procedures WHERE object_id = @@PROCID
GO
EXECUTE udp_test
Greets
Flo
March 5, 2009 at 4:46 pm
Hi,
If you have only couple of procedure updating a table and you are using SQL 2005 or 2008. You can look into output clause instead of trigger and use @@ProcId to manage your audit log.
I do not think it is possible to know the name of the stored procedure which has initiate the update on the table in a trigger (without modifying the procedure). If you find an answer please do post in the forum to increase my limited knowledge 🙂
Amit Lohia
March 5, 2009 at 4:56 pm
I modified Florian.Reischl's code to
IF (OBJECT_ID('udp_test') IS NOT NULL) DROP PROCEDURE udp_test
GO
CREATE PROCEDURE udp_test
AS
SELECT * FROM sys.procedures WHERE object_id = @@PROCID
SELECT OBJECT_NAME(@@PROCID) AS 'Procedure Name' -- added this line
GO
Then
EXECUTE udp_test
Result from added line is:
Procedure Name
--------------
udp_test
Result from original code is:
name object_id multiple additonal columns too numerous to show.
----- ---------
udp_test 219147826
In other words it returns the data for the procedure which executes the code. -- and it will not function properly with a dynamic T-SQL statement. I do not think that this is what you want or is it?
March 5, 2009 at 5:00 pm
The only way I can think of to do it is to use one of the classic statement capture scripts in the trigger, eg.
SELECT B.text
FROM sys.dm_exec_requests A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
where A.session_id = @@spid
You'd then need to parse the resultant text to find the procedure name.
March 6, 2009 at 8:06 am
Firstly, Thanks to everyone for their suggestions. I am still unable to get the procname which initiated the trigger. Since I have limited knowledge about the built in SQL functions and the tricks you can do this action might not be possible.
What I am trying to do is maintain a change/audit log for a table whether it changes through a direct call to update proc of the table from my asp.net app or it gets updated from another proc.
It is easy to keep track of an update log from the application, but it is little trickier if the table gets updated from another proc.
March 6, 2009 at 8:34 am
Hi
So you do not only need the name of the current procedure but the complete callstack to your current position.
Maybe this helps (did not evaluate):
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124537
Greets
Flo
March 6, 2009 at 9:04 am
I believe that is what I am looking for. It seems they dont have a real solution yet for these situations.
March 6, 2009 at 9:44 am
amujtaba
May I ask you to elaborate more on what you want, and the scenario that you want it in.
1. Do you apply all updates using only Stored Procedures?
2. If answer to question 1 is yes - how many different stored procedures are there for this one particular table.
3. Are there more than one table to be audited, if yes how many?
5. Must the auditing be performed by a trigger, or are you open to another technique?
Now my reason for all the questions is. I had developed a somewhat elaborate method to determine the most used SPs in a SQL 2000 database that with modification might satisfy your request.
March 6, 2009 at 3:36 pm
Just to elaborate a little more I only want to track an update to the record, not insert or delete
1) There is only one update proc where the data is updated
2) Other procs might be calling this update proc for the table
3) There is only one table
4) Any technique would work for me
Thanks
March 6, 2009 at 8:10 pm
Thank you for the additional information
amujtaba
2) Other procs might be calling this update proc for the table
This poses other questions:
6. Do you want the name of the Other procs? (Which seems self obvious since there is only one proc that performs the actual update.) But I have to ask.
7. If yes how many Other procs are there?
8. Can you post the code for the one proc that performs the update.
9. Post the code for one of the Other procs
10. The hardest question - why do you want to do this, what is your ultimate goal?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply