-- Tabellen bestimmen
DECLARE @table_main VARCHAR(40)
-- Haupttabelle, auf die sich das Logging bezieht
SET @table_main = 'unt'
-- Spalteneigenschaften
DECLARE @column VARCHAR(40),
@datatype VARCHAR(20),
@length SMALLINT
-- Trigger-Body erstellen
DECLARE @trigger VARCHAR(max),
@trigger_start VARCHAR(8000),
@trigger_end VARCHAR(8000),
@trigger_key VARCHAR(8000)
SET @trigger_start =
'-- ================================================================
-- Created by wusa
-- Date: ' + convert(CHAR(10),getdate(),20) + '
-- ================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[' + @table_main + '_log_]
ON [dbo].[' + @table_main + ']
FOR INSERT, UPDATE, DELETE
AS
IF NOT EXISTS ( SELECT lastuser
FROM INSERTED
WHERE lastuser LIKE ''SQL%'' )
BEGIN
SET NOCOUNT ON;
-- Datensatz
INSERT INTO ' + @table_main + '_log(pk,fk_' + @table_main + ',[user],datum,aktion,maske,feld)
SELECT newid() AS pk,
DELETED.pk AS fk_' + @table_main + ',
SYSTEM_USER AS [user],
getdate() AS datum,
''Delete'' AS aktion,
''' + @table_main + ''' AS maske,
''Datensatz'' AS feld
FROM INSERTED
FULL JOIN DELETED ON INSERTED.pk = DELETED.pk
WHERE INSERTED.pk IS NULL
AND DELETED.pk IS NOT NULL
'
SET @trigger_key =
' -- Schlüssel (einfach Eintragung)
'
SET @trigger_end =
'END
GO
'
-- Cursor zum durchlaufen der Spalten
DECLARE cursor_gen_trigger
CURSOR FOR
SELECT syscolumns.name AS [column],
systypes.name AS datatype,
syscolumns.[length] AS [length]
FROM sysobjects
JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.[xtype]='U'
AND sysobjects.name = @table_main
AND syscolumns.name NOT IN ( 'pk','lastuser','ID','fk_import' )
AND syscolumns.name NOT LIKE 'insert_%'
AND syscolumns.name NOT LIKE 'gru_%'
AND syscolumns.name NOT LIKE 'rec_%'
AND syscolumns.name NOT LIKE sysobjects.name + '_%'
ORDER BY syscolumns.name,syscolumns.colid
-- Spalteneigenschaften holen
OPEN cursor_gen_trigger;
FETCH NEXT
FROM cursor_gen_trigger
INTO @column,@datatype,@length
WHILE @@FETCH_STATUS = 0
BEGIN
-- generiere Trigger-Code zur jeweiligen Spalte
IF @datatype = 'uniqueidentifier'
AND @column != 'fk_' + @table_main
BEGIN
SET @trigger_key = @trigger_key +
' IF UPDATE(' + @column + ')
BEGIN
INSERT INTO ' + @table_main + '_log(pk,fk_' + @table_main + ',[user],datum,aktion,maske,feld,wert_neu,wert_alt,wert_key_neu,wert_key_alt)
SELECT newid() AS pk,
t.fk_' + @table_main + ',
( CASE WHEN t.[user] LIKE ''SQL %'' THEN t.[user] ELSE SYSTEM_USER END ) AS [user],
getdate() AS datum,
t.aktion,
''' + @table_main + ''' AS maske,
''' + @column + ''' AS feld,
t.wert_neu,
t.wert_alt,
t.wert_key_neu,
t.wert_key_alt
FROM (
SELECT INSERTED.pk AS fk_' + @table_main + ',
INSERTED.lastuser AS [user],
( CASE WHEN INSERTED.pk IS NOT NULL AND DELETED.pk IS NOT NULL THEN ''Update''
WHEN INSERTED.pk IS NOT NULL AND DELETED.pk IS NULL THEN ''Insert'' END ) AS aktion,
( SELECT ''#'' + cast(isnull(id,0) AS VARCHAR(10)) + '': '' + ' + right(@column,len(@column)-3) + '_bez_kurz FROM ' + right(@column,len(@column)-3) + ' WHERE pk = INSERTED.' + @column + ' ) AS wert_neu,
( SELECT ''#'' + cast(isnull(id,0) AS VARCHAR(10)) + '': '' + ' + right(@column,len(@column)-3) + '_bez_kurz FROM ' + right(@column,len(@column)-3) + ' WHERE pk = DELETED.' + @column + ' ) AS wert_alt,
INSERTED.' + @column + ' AS wert_key_neu,
DELETED.' + @column + ' AS wert_key_alt
FROM INSERTED
FULL JOIN DELETED ON INSERTED.pk = DELETED.pk
WHERE INSERTED.' + @column + ' IS NOT NULL
OR DELETED.' + @column + ' IS NOT NULL
) t
END
'
END
-- Ende Trigger-Code
FETCH NEXT
FROM cursor_gen_trigger
INTO @column,@datatype,@length
END;
CLOSE cursor_gen_trigger;
DEALLOCATE cursor_gen_trigger;
-- Ende Cursor
-- Trigger-Code anzeigen
PRINT @trigger_start
PRINT @trigger_key
PRINT @trigger_end