CODEWITHSUNDEEP
sqlt-sqlsql-server-2008triggers
Ryan
Ryan

Reputation: 6866

Need help with generic update trigger

I'm using SQL Server 2008 and would like to know if there is a way to write a generic update trigger that checks to see if the table being updated has a field called 'Last_Updated' and if so updates that field with the getDate() function.

Can I write a single generic trigger like that that that works for all tables in my db?

Thanks for your time!

Upvotes: 1

Views: 510

Answers (2)

KM.
KM.

Reputation: 103727

no, you need to have a trigger for each table, or include Last_Updated=GETDATE in every UPDATE.

try this:

SELECT 
    'CREATE TRIGGER [tr_u_'+TABLE_SCHEMA+'_'+TABLE_NAME+'] ON ['+TABLE_SCHEMA+'].['+TABLE_NAME+']'
        +'AFTER UPDATE AS '
        +'UPDATE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] '
        +'    SET Last_Updated=GETDATE() '
        +'    FROM ['+TABLE_SCHEMA+'].['+TABLE_NAME+']  A '
        +'    INNER JOIN INSERTED  i ON a.pk=i.pk '
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME='LastChgDate' --'Last_Updated'

This query will produce a Create Trigger command for every table that has a Last_Updated column. You MUST edit each command and and fix the a.pk=i.pk for each table to be the proper columns. Then run all the commands and then you'll have all the triggers. you'll need to add triggers as you add new tables.

Upvotes: 2

Kell
Kell

Reputation: 3327

You could interogate the schema_information.tables view in your trigger and asign the trigger to all tables, but this is an ugly way of doing things. You'd be better off writing some code generator to do this for each table rather than having a universal trigger.

Upvotes: 1

Related Questions