SQL Server Performance

Creating a Trigger That Is Called By More Than One Table?

Discussion in 'ALL SQL SERVER QUESTIONS' started by meek1977, Aug 21, 2007.

  1. meek1977 New Member

    Hello everyone.
    I have been trying to create a trigger that can be called by more than one table. I have 7 tables that when a record is updated on any of the tables, I want to take the values in the old record and insert them into an archive table. Each of the 7 tables have their own archive table.
    Now instead of creating a trigger on each of the tables, I've been trying to figure out if there is a way to create one trigger that can be called by the same event on each of the 7 tables. I looked into creating a trigger on a system table, but I found out that will not work. Does anyone know if this possible??

  2. hernan93 Member

    Hello
    Create a storeprocedure with parameters and call it from each table trigger.
    Hernan
  3. FrankKalis Moderator

    No, that's not possible. Each trigger can have exactly one parent object to which it belongs. But what about writing a small tool that will generate the source code for you and you then just have to execute the script? Something along the lines:
    <pseudocode>
    SELECT 'CREATE TRIGGER dbo.My' + TABLE_NAME + ' ....
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME IN( .... put in the tablenames you want to create a trigger for...)
    Then you just have to copy the text from the grid into the query window and execute it.
  4. meek1977 New Member

    Thanks for the suggestions, I really appreciate it.
  5. Ravindra Singh New Member

    @Frankkalis : Can u just illustrate this pseudocode thing using a simple example....because i have been asked to create a global trigger...that will do the audit trial for more than 100 tables...
  6. davidfarr Member

    I think what Frank means is that;
    If you are creating the same trigger syntax on many tables to perform the same function on each table, then you will need a trigger for each table, but you can save time by using the information and metadata in the SQL Server system tables to help you dynamically create each trigger syntax without typing out each trigger by hand.

    Try the query example below on your database;

    select top 50 'CREATE TRIGGER [tr_'+t1.[name]+'_Archive] ON '+t2.[name]+'.['+t1.[name]+
    '] FOR UPDATE AS INSERT ['+t1.[name]+'_Archive] SELECT * from Deleted'
    from sysobjects t1
    inner join sysusers t2 on t1.[uid]=t2.[uid]
    where xtype='U' order by t1.crdate desc

    You can take this query result and execute each line of the result as a separate SQL statement to create 50 triggers on 50 tables that will insert the old row of each updated table into 50 Archive tables. Of course, you will need to create the archive tables first before the triggers will work, but even that can be done smartly using the same idea that is suggested above.
  7. Luis Martin Moderator

    Welcome to the forums Ravindra!:)
    My suggestion is to open a new thread instead to continue with one almost 5 years old.
  8. Luis Martin Moderator

    Welcome to the forums Ravindra!:)
    My suggestion is to open a new thread instead to continue with one almost 5 years old.
  9. Ravindra Singh New Member

    Thanks for the help david . Yes i have to perform the same function on each table , so i have to write separate triggers.
    Thanks luis soon post a new question and will use a new thread
    :)

Share This Page