Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    Buffalo, NY
    Posts
    9

    Question Unanswered: Table name from inside of a Trigger

    I am creating a set of triggers that are exactly the same for each table and I need to determine the table name when they fire. Is there a way to capture this easily?

    Thanks in advance for any help.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    First of all, a triggers cannot be exactly the same for different tables because command for creating trigger includes name of table.
    Another words, you can generate triggers for set of tables with name of table inside trigger:

    Example:
    Tables

    create table test1(id int,code varchar(10))
    create table test2(id int,code varchar(10))
    create table test3(id int,code varchar(10))

    You want to create triggers on these tables like this

    create trigger t_test1 on test1
    for insert,update,delete
    as
    select 'test1',* from inserted
    select 'test1',* from deleted

    Next query will generate script for creating triggers:

    select 'create trigger t_'+name+' on '+name+'
    for insert,update,delete
    as
    select '''+name+''',* from inserted
    select '''+name+''',* from deleted'+char(13)+'go' +char(13)
    from sysobjects where name like 'test%' and type='U'
    --------------------------------------------------------------------------
    create trigger t_test1 on test1
    for insert,update,delete
    as
    select 'test1',* from inserted
    select 'test1',* from deleted
    go create trigger t_test2 on test2
    for insert,update,delete
    as
    select 'test2',* from inserted
    select 'test2',* from deleted
    go create trigger t_test3 on test3
    for insert,update,delete
    as
    select 'test3',* from inserted
    select 'test3',* from deleted
    go

  3. #3
    Join Date
    Feb 2002
    Location
    Buffalo, NY
    Posts
    9
    I understand that the script creating the triggers will not be exactly the same, but for reasons that are not important here, the trigger itself will be the same.

    So I will ask again, is there a way to determine what the parent of the trigger that is currently executing?


    Originally posted by snail
    First of all, a triggers cannot be exactly the same for different tables because command for creating trigger includes name of table.
    Another words, you can generate triggers for set of tables with name of table inside trigger:

    Example:
    Tables

    create table test1(id int,code varchar(10))
    create table test2(id int,code varchar(10))
    create table test3(id int,code varchar(10))

    You want to create triggers on these tables like this

    create trigger t_test1 on test1
    for insert,update,delete
    as
    select 'test1',* from inserted
    select 'test1',* from deleted

    Next query will generate script for creating triggers:

    select 'create trigger t_'+name+' on '+name+'
    for insert,update,delete
    as
    select '''+name+''',* from inserted
    select '''+name+''',* from deleted'+char(13)+'go' +char(13)
    from sysobjects where name like 'test%' and type='U'
    --------------------------------------------------------------------------
    create trigger t_test1 on test1
    for insert,update,delete
    as
    select 'test1',* from inserted
    select 'test1',* from deleted
    go create trigger t_test2 on test2
    for insert,update,delete
    as
    select 'test2',* from inserted
    select 'test2',* from deleted
    go create trigger t_test3 on test3
    for insert,update,delete
    as
    select 'test3',* from inserted
    select 'test3',* from deleted
    go

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You can use this sample, substitute the trigger name in.
    Code:
    select	object_name(parent_obj)
    from	sysobjects
    where	id	= object_id(<trigger_name>)
    I think that snail may have meant this since you will need to change the trigger name for each, why not just put the table name into the trigger. Pull out all of the like code and put it into a procedure and have each trigger call it passing in the table name.

    Example:

    create my_proc (tb varchar(35), col1 int, ....)
    as
    .
    .
    .
    .

    create trigger tb1
    as
    exec my_proc @tb='tb1',....


    create trigger tb2
    as
    exec my_proc @tb='tb2',....



    But you can use the code above to get the name of the parent object.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •