Post reply

How to get the trigger table ?

  • June 16, 2004 at 7:34 am

    #82994

    Hi,

    can somebody told me how to access the related

    table from inside a trigger ?

    I want to use the trigger table without coding

    the table name as a constant - any idea ?

    Thanks in advance.

  • June 16, 2004 at 7:38 am

    #510635

    Check BOL "Using the inserted and deleted Tables"

  • June 16, 2004 at 7:58 am

    #510643

    Hey,

    thanks for the tip... but...

    I don't want to use the inserted and deleted table-I

    want to get the name of the base table so that

    I can use it in a dynamic sql string.

  • June 17, 2004 at 7:44 am

    #510835

    Look up sysobjects in BOL. 

     

    The parent_obj  column contains the parent table of triggers.

  • June 18, 2004 at 3:49 am

    #510977

    that's true, but I still need the name of the trigger as a

    constant, isn't it ? The point is, I don't want to use a constant in the trigger code, so that the trigger himself can get the own table name.

  • June 18, 2004 at 7:41 am

    #511025

    Ummm, your original post said you didn't want to have to use the TABLE name as a constant. 

    I can't imagine why using the trigger name inside the trigger would be a problem, but if it is, I don't know of any other way for a trigger to reference itself, so you may be stuck with it.

  • June 22, 2004 at 5:39 am

    #511382

    Using the trigger name inside of the trigger is not the problem. I have more than 100 tables, and only one trigger code (the trigger will be defined on each table, all triggers has the same code). Inside the trigger, the name of the trigger table will be used to query some data, so that I want to get them without coding the table name or the trigger name in the trigger body. Doing so, I will be able to generate the create script for the triggers by querying the system tables.

    Anyway, best thanks for Your answer.

  • April 1, 2005 at 3:27 am

    #550035

    Has anybody got any further ideas on this? its something I am trying to do as well, but with little success.

  • April 1, 2005 at 3:52 am

    #550036

    Sussed it..

    DECLARE @TableName varchar(256)

    SELECT @TableName=s2.[name]

    FROM sysobjects s

    INNER JOIN sysobjects s2 ON s2.id=s.parent_obj

    WHERE s.id=@@PROCID AND s.type='tr' AND s.name='[[name of trigger]]'

  • April 1, 2005 at 3:59 am

    #550037

    See if this helps:

    http://www.umachandar.com/technical/SQL6x70Scripts/Main47.htm

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 2, 2005 at 6:30 am

    #550234

    Thanks, great link - had helped.

Viewing 11 posts - 1 through 10 (of 10 total)

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