SQL Server - Create single Trigger that runs for A

问题:

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?

回答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.)

回答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_' + t.s + '_' + t.n + ''') IS NOT NULL
    DROP TRIGGER [dbo].[ioTrigger_' + t.s + '_' + t.n + '];
G' + 'O
CREATE TRIGGER ioTrigger_' + t.s + '_' + t.n + '
    ON ' + QUOTENAME(t.s) + '.' + QUOTENAME(t.n) + '
    INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

-- surely you must want to put some other code here?

    INSERT ' + QUOTENAME(t.s) + '.' + QUOTENAME(t.n) + '
    (
' + 
(
    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 t.s, t.n;

SELECT @s = REPLACE(@s, ',' + @cr + '--' + @cr, @cr);

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

SELECT @s;

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

SELECT CONVERT(XML, @s);

source page: