Post reply

One trigger on all tables in dbase?

  • January 9, 2004 at 3:11 am

    #81866

    I want to set a trigger on all the tables. Is this possible and, if yes, how?

    For every record that changes, I would like to add a last modification date...

    thx

    I use SQLserver2000

  • January 9, 2004 at 3:21 am

    #489310

    I might be wrong, but I have never heard of such a 'global' trigger.

    I guess you have to use one trigger per table

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

  • January 9, 2004 at 4:42 am

    #489318

    No way to do it other than a trigger on every table. Or to include the same date stamping in all your update stored procs. If you just want to know the row is changed by not when, you could add a timestamp (rowversion) column to each table.

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • January 9, 2004 at 5:27 am

    #489329

    ok, guess I have to add triggers to all the tables ...

     

    daim 57 tables ... 🙁

  • January 9, 2004 at 6:26 am

    #489341

    Is there a way to automate the creating of triggers?

    and ...

    Anybody who knows a sp or something else to see all the triggers voor a specified database?

     

    would help a lot ...

     

  • January 9, 2004 at 11:41 am

    #489430

    Try http://www.sqlservercentral.com/scripts/contributions/153.asp for an automated triggering solution.  This could be the basis of what you need.

    Francis

  • January 11, 2004 at 4:50 pm

    #489553

    I believe the next version of SQL Server (Yukon) will have system level triggers. In the meantime, you might consider a third party tool by Lumigent called Entegra to see data mods in an easy to query way.

    HTH

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • January 12, 2004 at 4:30 am

    #489584

    Or simply wright your trigger in a method that is completely reusable then you can do something like so.

    Suppose I have a column in every table called ModCol for the modified date (note I would make it default to GETDATE() for insert) and I had an idenitity column IdCol in every table I may write something like this

    -- Test Trigger

    CREATE TRIGGER tr_testtrigger_dbo_tableX on dbo.tableX

    FOR UPDATE

    AS

    UPDATE dbo.tableX SET ModCol = GETDATE() WHERE IdCol In (SELECT IdCol FROM inserted)

    GO

    to quickly create for all tables I could do like so to create a print of all the items I need that I can then run.

    EXEC sp_msforeachtable '

    PRINT (''

    -- Test Trigger

    CREATE TRIGGER tr_testtrigger_'' + REPLACE(REPLACE(REPLACE(''?'',''.'',''_''),''['',''''),'']'','''') + '' on ?

    FOR UPDATE

    AS

    UPDATE ? SET ModCol = GETDATE() WHERE IdCol In (SELECT IdCol FROM inserted)

    GO'')

    '

    Or with the new QA (SQL 2000) you could eaisly create a template item to speed things along.

    These option although maybe not as grand as a third party app do have a cost advantage.

Viewing 8 posts - 1 through 7 (of 7 total)

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