Logging über Trigger - Alte Werte vor Überschreiben sichern

[Maggi]

Hallo zusammen,

ich arbeite momentan an einer Webanwendung welche auf eine SQL Datenbank zugreift. Sobald nun Daten in einer Tabelle geändert werden, soll bevor dem überschreiben eine "Sicherung" der alten Daten gemacht werden und in eine andere Tabelle gespeichert werden. Bei "jeder" Sicherung soll dann noch die "Log_ID" der Zieltabelle um eins steigen.

Hier meine 2 Tabellen um das zu verdeutlichen (genutzt wird MS SQL Server 2000):

Ursprungstabelle:

IVMMIdeen
_________
idee_ivmm_id (int)
idee_beschr_short (nvarchar)
idee_beschr_long (text)
idee_geber_acc (int)
idee_dat (datetime)
idee_pub_geber (bit)
idee_pub_ent (bit)
idee_stat (int)
idee_real (int)
idee_zeit (int)


Zieltabelle (praktisch die gleiche Tabelle, nur extra Spalte LogId):

IVMMIdeenLog
____________
IVMMIdeenLog_id (int)
idee_ivmm_id (int)
idee_beschr_short (nvarchar)
idee_beschr_long (text)
idee_geber_acc (int)
idee_dat (datetime)
idee_pub_geber (bit)
idee_pub_ent (bit)
idee_stat (int)
idee_real (int)
idee_zeit (int)

Jetzt habe ich mit dem Trigger "FOR UPDATE" rumprobiert, aber ich bekomme es einfach nicht hin. Ich blick da einfach nicht durch und nun hab ich auch noch den Problemfaktor Zeit, die komplette Anwendung muss diese Woche noch fertig werden da ich am Samstag für ne Weile auf Betriebsreise bin (und das ist leider bei weitem nicht die einzige Baustelle).

Daher wollte ich bei Euch nach Rat bitten da ich selbst das erste mal richtig mit einer SQL Datenbank arbeite und somit einfach überfragt bin.
Hat vielleicht jemand das ein oder andere Beispielskript für mich oder einfach nur ein paar Ideen die er mir gegen den Kopf schmeissen kann?

Ich habe sicherheitshalber mal ein Diagramm der Datenbank angehängt falls das für eine Hilfestellung von Nöten ist.

Ich möchte Euch schonmal im voraus für eure Hilfe bedanken!

[O. Helper]

Hallo Maggi,

das "um eins erhöhen" kannst Du über einen IDENTITY erledigen, also
CREATE TABLE
(IVMMIdeenLog_id int IDENTITY (1,1), ...

Im Trigger hast Du 2 virtuelle Tabellen: DELETED mit den gelöschten (alten) Werten und INSERTED mit den neuen Werten.
Wenn Du es nur wegschreiben willst, kannst Du die Datensätze aus DELETED einfach in Deine Log-Tabelle schreiben.
[highlight=SQL]
CREATE TRIGGER TRU_IVMMIdeenLog ON IVMMIdeenLog
FOR UPDATE, DELETE
AS
INSERT INTO IVMMIdeenLog
(idee_ivmm_id, idee_beschr_short, ....)
SELECT idee_ivmm_id, idee_beschr_short, ....
FROM deleted
[/highlight]

Olaf

[Maggi]

Hallo Olaf,

vielen Dank für die schnelle Hilfe.

Habe leider noch ein Problem, der akzeptiert in der deleted Tabelle keine "Text" Werte.

Ich hab mir gedacht ich bin ganz schlau und versuch das mal so:

CREATE TRIGGER SkiptUpd ON dbo.IVMMIdeen
FOR UPDATE, DELETE
AS

------------------------------------------------------------------------------------------------------------------------ Logging

           INSERT INTO IVMMIdeenLog
                (idee_ivmm_id, idee_beschr_short, idee_beschr_long, idee_geber_acc, idee_dat, idee_pub_geber, idee_pub_ent, idee_stat, idee_real, idee_zeit)
           SELECT idee_ivmm_id, idee_beschr_short, CAST(idee_beschr_long AS varchar(max), idee_geber_acc, idee_dat, idee_pub_geber, idee_pub_ent, idee_stat, idee_real, idee_zeit
           FROM deleted

Nun habe ich leider festgestellt das ich doch nicht so schlau bin und es so nicht klappt. Hast du vielleicht noch einen Rat für mich wie ich hier am besten verfahre?

Ich danke nochmal vielmals!


Viele Grüße,
Maggi

[hwoess]

TEXT und IMAGE sind im trigger in deleted/inserted nicht vorhanden, da führt kein Weg daran vorbei.
Zunächst mal die Frage: was speichert ihr in TEXT-Feldern? Wenn es nur Zeichenstrings sind, dann tut euch den Gefallen und verwendet stattdessen VARCHAR. TEXT ist eher etwas binäres und macht entsprechend Probleme. Falls es um die Grösse des Textes geht, ab SQL2005 gibt es den Typ varchar(MAX), der kann bis zu 2 Gb Text in einem Feld speichern, ohne die Problematik des TEXT-Datentyps.
Wenn es aber unbeding TEXT sein muss, dann bleibt nur die Möglichkeit, über einen INSTEAD-OF-Trigger das update/delete vorher abzufangen, über den Primary key die Daten aus der Tabelle zu lesen und in das Logfile zu schreiben und anschließend das update/delete auszuführen. Siehe dazu am besten die Beschreibungen/Beispiele im Manual zu "instead-of-trigger".

bye,
Helmut

[O. Helper]

Hallo Maggi,

wie Helmut schon ausgeführt hat, stehen in den virtuellen Tabellen eines Triggers keine Felder vom Typ Text, nText oder Image zur Verfügung; ebenso kann man keine Variablen von den Typen deklarieren.

Entweder gehst Du den Weg mit dem INSTEAD OF Trigger oder Du musst Du die virt. Tabellen mit der "echten" verknüpfen, um die Felder zu bekommen; sei es per SubSelect oder per JOIN.

[highlight=SQL]CREATE TRIGGER TRU_IVMMIdeenLog ON IVMMIdeenLog
FOR UPDATE, DELETE
AS

INSERT INTO IVMMIdeenLog
(idee_ivmm_id, idee_beschr_short, idee_beschr_long )
SELECT D.idee_ivmm_id, D.idee_beschr_short, I.idee_beschr_long
FROM deleted AS D
INNER JOIN IVMMIdeen AS I
ON I.idee_ivmm_id = D.idee_ivmm_id[/highlight]

VARCHAR(Max) gibt es erst ab SQL Server 2005, Du hast "nur" den 2000 im Einsatz.

Olaf

[hwoess]

Nachdem es beim SQL-Server ja keine Before-trigger gibt (bzw. sind das in weiterem Sinne die Instead-Of-trigger), ist doch das, was ich da im "for-update-delete-Trigger" per Verknüpfung aus der Tabelle lese, der bereits geänderte Wert - also zu spät für ein Log, wenn ich den alten Wert, der normalerweise im deleted steht, protokollieren will. Bleibt meines Erachtens also nur der Instead-Of-Trigger - oder habe ich da jetzt was übersehen

bye,
Helmut

[O. Helper]

Wo Du wahr hast, hast Du recht!
Das würde den neuen Wert mit loggen, nicht den alten.

Olaf

[Maggi]

Hallo nochmal,

vielen Dank für Eure Hilfe! Ich habe es nun (wie von euch vorgeschlagen) so gemacht das ich die virtuelle mit der echten Tabelle verknüpft habe und dafür immer alle Änderungen sichere, das geht auch so in Ordnung wie ich vom Auftraggeber erfahren habe.

Und es funktioniert einwandfrei!


Vielen Dank nochmal für Eure Hilfe!


Viele Grüße,
Patrik