SQL Server Create a single trigger that runs for ALL tables in the database.

Question

I am trying to create a trigger in SQL Server 2005 that runs on INSERT, UPDATE and DELETE, but for ALL tables in the database (for audit purposes). Can this be done?

We currently have separate triggers for each table in the database, and since they all do the same, I want to combine them into one trigger.

I know that you can create database triggers, but the only events that I can hook up seem to be related to schema changes in tables, sprocs, etc., but not for inserts and updates for records if I don’t enough?

Answer 1

Shared table triggers do not exist in SQL, so you need to scroll through each of your tables (INFORMATION_SCHEMA.Tables) and create triggers for each of them using dynamic SQL. (Or come up with another simple process for creating triggers for each table.)

Answer 2

SET NOCOUNT ON; DECLARE @cr VARCHAR(2) = CHAR(13) + CHAR(10), @t VARCHAR(1) = CHAR(9), @s NVARCHAR(MAX) = N''; ;WITH t AS ( SELECT [object_id], s = OBJECT_SCHEMA_NAME([object_id]), n = OBJECT_NAME([object_id]) FROM sys.tables WHERE is_ms_shipped = 0 ) SELECT @s += 'IF OBJECT_ID(''dbo.ioTrigger_' + ts + '_' + tn + ''') IS NOT NULL DROP TRIGGER [dbo].[ioTrigger_' + ts + '_' + tn + ']; G' + 'O CREATE TRIGGER ioTrigger_' + ts + '_' + tn + ' ON ' + QUOTENAME(ts) + '.' + QUOTENAME(tn) + ' INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; -- surely you must want to put some other code here? INSERT ' + QUOTENAME(ts) + '.' + QUOTENAME(tn) + ' ( ' + ( SELECT @t + @t + name + ',' + @cr FROM sys.columns AS c WHERE c.[object_id] = t.[object_id] AND is_identity = 0 AND is_rowguidcol = 0 AND is_computed = 0 AND system_type_id <> 189 FOR XML PATH(''), TYPE ).value('.[1]', 'NVARCHAR(MAX)') + '--' + @cr + @t + ')' + @cr + @t + 'SELECT ' + ( SELECT @t + @t + name + ',' + @cr FROM sys.columns AS c WHERE c.[object_id] = t.[object_id] AND is_identity = 0 AND is_rowguidcol = 0 AND is_computed = 0 AND system_type_id <> 189 FOR XML PATH(''), TYPE ).value('.[1]', 'NVARCHAR(MAX)') + '--' + @cr + @t + 'FROM inserted; END' + @cr + 'G' + 'O' + @cr FROM t ORDER BY ts, tn; SELECT @s = REPLACE(@s, ',' + @cr + '--' + @cr, @cr); 

- you can check at least part of the script by running - in text mode:

 SELECT @s; 

- if you want to see most of all (but not necessarily - all this), run it in grid mode and click on the result:

 SELECT CONVERT(XML, @s); 

original page: