When a TRIGGER executes, it may be useful to have the TRIGGER code know which Stored Procedure caused the action that is executing the TRIGGER code. It may be that there is a need to log the entry point, or perhaps, there is conditional logic in the TRIGGER that depends upon the entry point. In this paper, two different scenarios are addressed. First, How to Track Which Stored Procedure Fired the Trigger, and Second, How to Limit a TRIGGER to Fire ONLY from Certain Stored Prodedures. This approach has been tested with SQL Server 2005 and 2008. SQL Server 2000 requires a minor modification that is presented at the end of the paper.
Check my paper in MSDN Wiki page