Post reply

Passing Source table to Stored Procedure

  • April 23, 2007 at 1:04 pm

    #178037

    I have a table with a trigger on it that sends some data to a stored procedure when data is inserted to the table. Since I have multiple tables that use the stored procedure I would like to also be able to send the name of the table that initiated the trigger. I can handle the modification of the stored procedure but how can I get the table name from within the trigger? 

     

  • April 23, 2007 at 1:27 pm

    #702005

    Only way I know, is to hard code the tablename inside the trigger.

  • April 26, 2007 at 11:13 am

    #702708

    The trigger is a child object of the table. The name of the trigger can be obtained from within the trigger using @@procid. In other words, you can get the trigger name by plugging in the following code within the trigger code

    DECLARE @TriggerName SYSNAME

    SET @TriggerName = OBJECT_NAME(@@PROCID)

    To get the table name that is the parent of the trigger, you simply need to do:

    DECLARE @ParentTableName SYSNAME

    SET @ParentTableName = OBJECT_NAME(parent_obj)

    FROM sysobjects

    WHERE id = @@PROCID

    No need to hardcode at all.

    Hope this helps.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply