Announcement

**openshinok** · 03.09.2011, 09:07

hallo,
ich habe so einen Trigger der nur die geänderten Spalten berücksichtigt. Allerdings die Änderungen nicht in xml sondern separat pro Feld speichert. (Den ursprünglichen Author des Triggers habe ich mir leider nicht im Trigger vermerkt. Nach kurzer Suche nach den englischen Kommentaren kam ich auf folgende Seite http://www.bryanavery.co.uk/post/201...-with-SQL.aspx. Die unten gezeigten SQLs zeigen jedoch eine für meine Zwecke verbesserte Version.)

Möchtest du den Benutzer aus der Anwendung loggen? Dieser Trigger funktioniert nur mit den MSSQL-Benutzern mit dem auf die DB zugegriffen wird. Wenn das eine Webanwendung ist wie bei mir, dann kann man natürlich nicht mehr sagen wer es tatsächlich war. Ich suche also auch nach einer Lösung dort eine Variable mit dem Benutzer unabhängig vom DB-Benutzer reinzubekommen.....

Performance kann ich nichts sagen, aber wir haben den Trigger schon seit Anfang der Entwicklung der Anwendung bei Haupttabellen mitlaufen und "kennen" die Anwendung nicht schneller, haben aber auch keine Geschwindigkeitsprobleme. Sieht evtl. anders aus wenn mehr Leute damit arbeiten.

Wichtiger Hinweis, der Trigger funktioniert nicht mit dem Datentyp Text und Varbinary glaube ich auch nicht. Da ich eine neue Anwendung erstellen konnte, wurden alle Felder gleich auf VARCHAR gestellt.

Logtabelle

Code:

CREATE TABLE [dbo].[tbl_log_audit](
	[Typ] [char](1) NULL,
	[Tabellenname] [nvarchar](128) NULL,
	[PK] [nvarchar](1000) NULL,
	[Feldname] [nvarchar](128) NULL,
	[WertAlt] [nvarchar](max) NULL,
	[WertNeu] [nvarchar](max) NULL,
	[Datum] [datetime] NULL,
	[Benutzer] [nvarchar](250) NULL
) ON [PRIMARY]

Trigger (es ist stets nur an 2 Stellen der Tabellenname zu ändern, wenn man für die Erstellung eine Prozedur verwendet ist das genau ein Parameter)

Code:

CREATE TRIGGER [dbo].[_audit_tbl_auftrag] on  [dbo].[tbl_auftrag] for insert, update, delete
-- Info zur Veröffentlichung: 
-- 1) Triggernamen anpassen (nach CREATE TRIGGER)
-- 2) Tabelle anpassen (nach ON im CREATE TRIGGER - Statement)
as



SET NOCOUNT ON


declare @bit int ,
        @field int ,
        @maxfield int ,
        @char int ,
        @fieldname varchar(128) ,
        @TableName varchar(128) ,
        @PKCols varchar(1000) ,
        @sql varchar(2000), 
        @UpdateDate varchar(21) ,
        @UserName varchar(128) ,
        @Type char(1) ,
        @PKSelect varchar(1000)

        




-- automatisches Ermitteln des Tabellennamens
SELECT 
@TableName = OBJECT_NAME(parent_id) 
FROM         sys.triggers
WHERE     (object_id = @@procID)



		/* Wenn keine Tabellenname existiert funktioniert der Trigger nicht */
        if @TableName is null
        begin
               raiserror('kein Tabellenname feststellbar', 16, -1)
               return
        end







        -- Datum und Benutzer in Variablen speichern
        select @UserName = system_user,
               @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

 

        -- Bestimmen der SQL Aktion (Update, Insert oder Delete)
        if exists (select * from inserted)
               if exists (select * from deleted)
                       select @Type = 'U'
               else
                       select @Type = 'I'
        else
               select @Type = 'D'

        

        -- Die Liste der Felder Zwischenspeichern
        select * into #ins from inserted
        select * into #del from deleted

        

        -- Get primary key columns for full outer join
        select  @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
        from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where   pk.TABLE_NAME = @TableName
        and     CONSTRAINT_TYPE = 'PRIMARY KEY'
        and     c.TABLE_NAME = pk.TABLE_NAME
        and     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

        

        -- Get primary key select for insert
        select @PKSelect = coalesce(@PKSelect+'+','') + '''' + '<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' 
        from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where   pk.TABLE_NAME = @TableName
        and     CONSTRAINT_TYPE = 'PRIMARY KEY'
        and     c.TABLE_NAME = pk.TABLE_NAME
        and     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

        
		-- Prüfen ob ein PK vorhanden ist
        if @PKCols is null
        begin
               raiserror('no PK on table %s', 16, -1, @TableName)
               return
        end

        

        select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
        while @field < @maxfield
        begin
               select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
               select @bit = (@field - 1 )% 8 + 1
               select @bit = power(2,@bit - 1)
               select @char = ((@field - 1) / 8) + 1
  if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
               begin

                       select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
                       select @sql =          'insert tbl_log_Audit (Typ, Tabellenname, PK, Feldname, WertAlt, WertNeu, Datum, Benutzer)'
                       select @sql = @sql +   ' select ''' + @Type + ''''
                       select @sql = @sql +   ',''' + @TableName + ''''
                       select @sql = @sql +   ',' + @PKSelect
                       select @sql = @sql +   ',''' + @fieldname + ''''
                       select @sql = @sql +   ',convert(nvarchar(MAX),d.' + @fieldname + ')'
                       select @sql = @sql +   ',convert(nvarchar(MAX),i.' + @fieldname + ')'
                       select @sql = @sql +   ',''' + @UpdateDate + ''''
                       select @sql = @sql +   ',''' + @UserName + ''''
                       select @sql = @sql +   ' from #ins i full outer join #del d'
                       select @sql = @sql +   @PKCols
                       select @sql = @sql +   ' where i.' + @fieldname + ' <> d.' + @fieldname 
                       select @sql = @sql +   ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
                       select @sql = @sql +   ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 

                       exec (@sql)

               end

        end

Sicht um alle diese Trigger über die DB anzuzeigen

Code:

CREATE VIEW [dbo].[qry_audit_trigger_liste]
AS
SELECT     TOP (100) PERCENT o_tabelle.name AS Tabellenname, o_trigger.name AS Triggername, o_trigger.crdate
FROM         sys.sysobjects AS o_trigger INNER JOIN
                      sys.sysobjects AS o_tabelle ON o_trigger.parent_obj = o_tabelle.id
WHERE     (o_trigger.type = 'TR') AND (o_trigger.name LIKE N'%_audit_%')
ORDER BY Tabellenname, Triggername


GO

**tinof** · 05.09.2011, 07:45

Hallo,

vielen Dank, das sieht doch richtig schön aus! Genau so etwas 'unabhängiges', das man weitestgehend per copy & paste in jede Tabelle übernehmen kann, suche ich.
Da muss ich mich einmal richtig hineindenken in diese Trick & Kniffe und quirky updates.

Vielen Dank für diesen Post!

Tino

**tinof** · 15.08.2012, 07:35

So, hier nun nach fast einem Jahr die Lösung, die wir inzwischen produktiv im Einsatz habe - falls sich jemand weitere Anregungen holen möchte.

Um die Trigger übersichtlicher zu halten, haben wir die Hauptfunktion in eine Procedure ausgelagert. Auch ist sie so zentraler pfleg- und wartbar.

Die Zugriffe werden durch dieses Protokoll merklich, aber akzeptabel langsamer; insbesondere wenn im Rahmen von Businessobjekten gleich mehrere Tabellen zurückgeschrieben werden.

[highlight=sql]
-- die Protokolltabelle
CREATE TABLE [dbo].[AuditLog](
[Typ] [char](1) NULL,
[Tabellenname] [nvarchar](128) NULL,
[PK] [nvarchar](1000) NULL,
[FK] [nvarchar](1000) NULL,
[Feldname] [nvarchar](128) NULL,
[WertAlt] [nvarchar](max) NULL,
[WertNeu] [nvarchar](max) NULL,
[Datum] [datetime] NULL,
[Benutzer] [nvarchar](250) NULL
) ON [PRIMARY]

--- die allgemeine Procedure zur Protokollierung

CREATE PROC [dbo].[DoAuditLog] @TableName Varchar(128), @UColumns VarBinary(128)
AS
BEGIN
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@PKSelect varchar(1000),
@FKSelect varchar(1000) ,
@type char(1),
@data_type nVarchar(128)

/* Wenn keine Tabellenname existiert funktioniert der Trigger nicht */
if @TableName is null
begin
raiserror('kein Tabellenname feststellbar', 16, -1)
return
end

if exists (select * from #ins)
if exists (select * from #del)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'

-- Datum und Benutzer in Variablen speichern
select @UserName = dbo.username(),
@UpdateDate = convert(varchar(10), getdate(), 104) + ' ' + convert(varchar(12), getdate(), 114)

-- Get primary key columns for full outer join and logging
select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME,
@PKSelect = coalesce(@PKSelect+'+','') + '''' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+'''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Prüfen ob ein PK vorhanden ist
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end

-- Get foreign key columns
select @FKSelect = coalesce(@FKSelect+'+','') + '''[' + c.COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + c.COLUMN_NAME +',d.' + c.COLUMN_NAME + '))+'']'''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c,
INFORMATION_SCHEMA.COLUMNS col
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'FOREIGN KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
and col.TABLE_NAME = pk.TABLE_NAME
and col.COLUMN_NAME = c.COLUMN_NAME
and col.IS_NULLABLE = 'NO'

if @FKSelect is null select @FKSelect = ''''''

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

--print @UColumns

while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1

if ((substring(@UColumns,@char, 1) & @bit > 0) or (@Type in ('I','D')))
begin
select @fieldname = COLUMN_NAME, @data_type = DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
--print @fieldname
if (@data_type <> 'Timestamp') AND (@fieldname <> 'Geaendert') AND (@fieldname <> 'GDatum') AND (ISNULL(CHARINDEX(@fieldname,@FKSelect),0) = 0)
begin
select @sql = 'insert AuditLog (Typ, Tabellenname, PK, FK, Feldname, WertAlt, WertNeu, Datum, Benutzer)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
select @sql = @sql + ',' + @PKSelect
select @sql = @sql + ',' + @FKSelect
select @sql = @sql + ',''' + @fieldname + ''''
select @sql = @sql + ',convert(nvarchar(MAX),d.' + @fieldname + ')'
select @sql = @sql + ',convert(nvarchar(MAX),i.' + @fieldname + ')'
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',''' + @UserName + ''''
select @sql = @sql + ' from #ins i full outer join #del d'
select @sql = @sql + @PKCols
select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
--print @sql
exec (@sql)
end
end
end
END

/************************************************** ****/
/* in den Triggern dann wie folgt aufrufen */
/************************************************** ****/

/*

/** Mutationsprotokoll **/

DECLARE @TableName Varchar(128),
@flag VarBinary

-- Die Liste der Felder Zwischenspeichern sowie alles, was ausserhalb des Triggers keinen
-- Kontext besitzt
select * into #ins from inserted
select * into #del from deleted
select @flag= columns_updated()
SELECT @TableName = OBJECT_NAME(parent_id) FROM sys.triggers WHERE (object_id = @@procID)

-- Jetzt die einheitliche, allgemeine Logfunktion aufrufen
exec DoAuditLog @TableName, @flag

/** Ende Mutationsprotokoll **/

*/

[/highlight]

VIELEN DANK an Openshinok für den grundlegenden Ansatz!

Grüße
Tino

Announcement

Änderungsprotokoll (wieder mal) / XML im SQL-Server

Änderungsprotokoll (wieder mal) / XML im SQL-Server

Comment

Comment

Comment