December 8, 2003 at 2:23 pm
Gurus,
My problem is I am trying to find out name of stored procedure which updates a given table. The problem is there are several stored proc called by VB application updates the same table/Column and I want to capture the name of sp and not the generic name of program/username via trigger.
December 8, 2003 at 3:28 pm
Take a look at the Meta Data functions.
@@PROCID returns the ID of the current procedure, OBJECT_NAME() returns the name of the database object. Used together OBJECT_NAME(@@PROCID) gives you the name of the currently executing stored procedure.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
December 8, 2003 at 3:32 pm
From My suggestion you will have to modify a little the stored procedures as:
ALTER PROCEDURE Proc1 (@var1 ....
DECLARE @TrackValue varbinary(128)
SET @TrackValue = CAST( @@PROCID as binary(4) )
SET CONTEXT_INFO @TrackValue
-- Your Proc Code
...
INSERT
...
SET CONTEXT_INFO 0x0 --Clear at the end of the Proc
Then you can Write the trigger as:
CREATE TRIGGER AuditTrigger
ON TableName
AFTER INSERT
AS
BEGIN
DECLARE @ProcName varchar(128)
SELECT @ProcName = OBJECT_NAME( SUBSTRING
( p.context_info, 1, 4 ) )
FROM master..sysprocesses as p
WHERE p.spid = @@SPID
PRINT @ProcName
-- Or Insert On Audit Table
-- Ps The Value on the parameters are on the inserted table
END
HTH
* Noel
December 9, 2003 at 10:43 am
Thanks, all of you for your input, but I found solution yesterday itself my self, to share what i did is in the trigger I used dbcc inputbuffer(@@spid) using dynamic sql, since you can not do insert into table with dbcc... so created a table and brought the output in that table from trigger and it worked.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy