SQL Server - Create single Trigger that runs for ALL tables in the database

Question

I'm trying to create a trigger in SQL Server 2005 that runs on INSERT, UPDATE and DELETE, but for ALL tables in the database (for auditing purposes). Is it possible to do this?

Currently we have separate triggers for every table in the database, and since they all do the same thing, I'm looking to consolidate them into a single trigger.

I know it's possible to create Database triggers, but the only events I can hook into seem to be for schema changes to tables, sprocs etc. but not for inserts and updates to records, unless I'm missing something?

Answer 1

Generic table triggers don't exist in SQL so you'll need to loop through each of your tables (INFORMATION_SCHEMA.Tables) and create your triggers for each using dynamic SQL. (Or come up with another simple process to create triggers for each table.)