Know Stored Procedure which is Updating/Manipulating(DML) a Table ?

How to know SP's which are Manipulating/Updating(DML) a Table?

Suppose we have a table Employee, now we want to know the list of DB objects and users which are manipulating Employee Table
-- Create Table Employee
CREATE TABLE EMPLOYEE
(
EMPID INT,
EMPNAME VARCHAR(100),
Address VARCHAR(200)
)

-- Create Log Table to store the calling sp's/DB objects and Users.
CREATE TABLE TBL_CALLINGOBJECTS
(
ID INT IDENTITY(1, 1),
DTIME DATETIME DEFAULT(GETDATE()),
EMPID INT,
EMPNAME VARCHAR(100),
USERNAME VARCHAR(100) DEFAULT(SUSER_SNAME()),
SPID INT DEFAULT(@@SPID),
SPNAME VARCHAR(200)
)

-- Create Trigger Start on Table Employee
CREATE TRIGGER TRIGGEREMPLOYEE_INSERT
ON EMPLOYEE
FOR INSERT -- ,UPDATE, DELETE
AS
DECLARE @Query VARCHAR(100)
DECLARE @SpName VARCHAR(255)

CREATE TABLE #SESSIONOBJECTS
(
EVENTTYPE NVARCHAR(30),
PARAMETERS INT,
EVENTINFO NVARCHAR(255)
)

SET @Query = 'DBCC INPUTBUFFER(' + Str(@@SPID) + ')'

INSERT INTO #SESSIONOBJECTS
EXEC (@Query)

SELECT @SpName = EVENTINFO FROM #SESSIONOBJECTS

INSERT INTO TBL_CALLINGOBJECTS
(EMPID,
EMPNAME,
SPNAME)
SELECT EMPID,
EMPNAME,
@SpName
FROM INSERTED
GO
-- Create Trigger End on Table Employee

-- Now select the data from table TBL_CALLINGOBJECTS and check calling objects in column SPName
-- Note:
Once we know the calling sp's /objecs we have to delete/disable this trigger as per requirement
else trigger will keep on inserting data in log table(TBL_CALLINGOBJECTS)
right now trigger is defined for Insert command, we can modify it for Delete and Update if reqiured.