"mehrzeiliger" Trigger unter MSSQL

[DrColossos]

Hi,

Ich habe folgendes Problem unter MSSQL (PGSQL & ORACLE kommen auch noch, hehe)

Hab eine Tabelle, zu der möchte ich über einen Trigger eine History-Tabelle füllen, die der aktuellen Tabelle identisch ist, allerdings noch eine ID-Spalte (muss sein wegen Primary Key), und noch eine Spalte, die angibt, ob der Datensatz eingefügt (aktion = 2), geändert (aktion = 3), oder gelöscht (aktion = 4) wurde.

Funktioniert bereits, bei INSERT, auch bei UPDATE und DELETE, bei beiden letzteren aber nur wenn HÖCHSTENS EIN Datensatz geändert gelöscht wird.

Problem ist, dass der Primärschlüsel, den ich für die History-Tabelle ermittle, mehrmals benutzt wird, das geht dann natürlich schief.

Ich müsste da eine Schleife einbauen, oder was auch immer ... wer kann mir helfen.

Kompletter Code ist angehängt,

Danke & Servus


GO

CREATE TABLE person
(
person_id int NOT NULL PRIMARY KEY,
person_name varchar(20),
person_alter int,
)

CREATE TABLE person_history
(
person_history_id int NOT NULL PRIMARY KEY,
aktion int NOT NULL,
person_id int NOT NULL,
person_name varchar(20),
person_alter int
)

GO
CREATE TRIGGER person_to_history
ON person
FOR UPDATE, DELETE
AS
DECLARE @action int

-- Aktion ermitteln
IF (SELECT MAX(person_id) FROM person WHERE person_id IN (SELECT person_id FROM deleted)) IS NULL
SELECT @action = 4
IF (SELECT MAX(person_id) FROM person_history WHERE person_id IN (SELECT person_id FROM deleted)) IS NULL
SELECT @action = 2
ELSE
SELECT @action = 3

-- Debugging ...
PRINT 'action: "' + CAST(@action AS varchar(20)) + '"'

DECLARE @cnt_npid int
SELECT @cnt_npid = (SELECT COUNT(person_id) FROM inserted)
PRINT 'COUNT neu PID: "' + CAST(@cnt_npid AS varchar(20)) + '"'

DECLARE @cnt_opid int
SELECT @cnt_opid = (SELECT COUNT(person_id) FROM deleted)
PRINT 'COUNT alte PID: "' + CAST(@cnt_opid AS varchar(20)) + '"'

DECLARE @npid int
SELECT @npid = (SELECT person_id FROM inserted)
PRINT 'neu PID: "' + CAST(@npid AS varchar(20)) + '"'

DECLARE @opid int
SELECT @opid = (SELECT person_id FROM deleted)
PRINT 'alte PID: "' + CAST(@opid AS varchar(20)) + '"'

DECLARE @max_person_history_id int

-- PK für historie-Tabelle ermitteln
SELECT @max_person_history_id = (SELECT MAX(person_history_id) FROM person_history)
IF @max_person_history_id IS NULL
SELECT @max_person_history_id = 1
ELSE
SELECT @max_person_history_id = @max_person_history_id + 1
PRINT '@max_person_history_id: "' + CAST(@max_person_history_id AS varchar(20)) + '"'
-- Datensatz einfügen
INSERT INTO person_history
SELECT @max_person_history_id, @action, * from DELETED

-- Testdaten
GO
INSERT INTO person values (1, 'A', 10)
INSERT INTO person values (2, 'B', 20)
INSERT INTO person values (3, 'C', 30)

-- Trigger-Test, funktioniert (genau ein Sat wird geändert)
UPDATE person SET person_name = 'Z' WHERE person_id = 1

-- Trigger-Test, scheitert, möchte mehrmal mit gleichem Primärschlüssel einfügen (mehrere Zeilen werden upgedated, hier drei)
UPDATE person SET person_name = 'Z'

 

[bernhard2211]

Ich hab den Code jetzt nicht genau durchgeschaut, aber funktioniert der Updatebefehl wenn du ihn im Query Analayzer ausführst? Kann es sein, das bei Update/DELETE einfach ein anderer Trigger angesprochen wird, der da deine Daten verändert?

 

[DrColossos]

Ehhhm, nein

Das passt schon so, er macht das richtige.

Zur Erklärung nochmal. Die ersten beiden Statements legen die Daten-Tabelle und die zugehörige History-Tabelle an.

Das 3. Statement legt einen Update/Delete-Trigger an, der auch ein paar debugging-Ausgaben macht.

Dann werden noch 3 Testzeilen in die Datentabelle eingefügt und schließlich ein Update ausgeführt, der GENAU EINE Zeile ändert. Diese Zeile wird geändert, und auch in der History-Tabelle steht danach der korrekte Datensatz.

Probleme gibt's nur wenn ich mehrere Datensätze ändere/lösche (z.B. wenn keine WHERE-Klausel angegeben ist).

Ich schaffs nicht, dass der Trigger PRO geänderter/fgelöschter Zeile aufgerufen wird. Statt dessen scheint MSSQL zuversuchen folgende INSERTs in die Histrory-Tabelle zu machen (siehe Beispiel 2, das nicht funktionier).

-> UPDATE person SET person_name = 'Z'
-> 3 Datensätze sollten in Datentabelle geändert werden
-> auch sollten via Trigger die drei alten Datensätze in die History-Tabelle wandern, via ...
"INSERT INTO person_history
SELECT @max_person_history_id, @action, * from DELETED"

Mit dem "* from DELETED" kommen natürlich alle DREI geänderten Datensätze zurück, d.h. er versucht dann folgendes:
- 1. geänderte Zeile: INSERT INTO person_history VALUES (1, 2, ...)
- 2. geänderte Zeile: INSERT INTO person_history VALUES (1, 2, ...)
- 3. geänderte Zeile: INSERT INTO person_history VALUES (1, 2, ...)

Der 1. Wert des VALUES Tupel ist aber der Primärschlüssel. Wir alle wissen was passiert wenn ein PK mehrmals vergeben wird.

Fazit: Ich müsste das irgendwie zeilenweise machen, also INSERT "firstEditedRow", INSERT "nextEditedRow", ...

Aber wie?

In ORACLE geht das wie ich glaube mit "FOR EACH ROW" oder so ... gibts da auch was ähnliches in MSSQL?

Auch CURSOR wäre möglich, aber ist glaub ich VIEL umständlicher als das schicke "INSERT INTO table SELECT ..."

Hilfe!
Danke

 

[lmarkus31]

Hallo,

mittels @@ROWCOUNT kannst du feststellen, wieviele Rows von dem triggerendem Statemenrt betroffen sind (INSERT/UPDATE/DELETE).

Was du bei Multi-Rows-Triggern beachten solltest, schau mal hier:
http://msdn2.microsoft.com/en-us/library/ms190752.aspx

Markus

 

[DrColossos]

Danke,
das hab ich auch schon gefunden ...

Ich weiss aber nicht was mit "rowset-based logic instead of cursors" gemeint ist ... auch google weiss es nicht, hehe.

Und mit ROWCOUNT hab ich auch schon rumprobiert, das würde sicher auch gehen, dann halt selber eine Schleife basteln und die Zeilen der Reihe nach einfügen.

Mit dem Code unten funktionierts jetzt auch, allerdings mit folgender Einschränkung.

Der Primaärschlüssel der Historien-Tabelle muss hier sein:

person_history_id UND person_id

Eigentlich würde vom Konzept her "person_history_id" reichen, da es ein Counter ist.

Allerdings ist mir dann SQL immer abgestürzt (wie oben beschrieben), weil versucht wurde, mehere Datensätze mit der selben ID anzulegen ... SELECT MAX(person_history_id) + 1 lieferte immer den selben Wert, d.h. mehrere Zeilen auf einmal ging nicht.

-> INSERT INTO person_history
SELECT (SELECT MAX(person_history_id) + 1 FROM person_history), @action, * from DELETED

Da aber person_history_id UND person_id IMMER eindeutig ist, geht's nun.

Zusätzlich muss auch noch die History-Tabelle nach Erzeugung mit einem DAtensatz initialisiert werden, z.B. ...

INSERT INTO person_history values (0, 0, 0, '0', 0)

... sonst liefert SELECT MAX(person_history_id) + 1 einen Fehler.

Vielleicht hilft's jemanden, geht sicher besser, aber für'n Anfang reicht mir das ...

Btw, dieses Thread muss noch nicht sterben, wer noch was weiss, immer her damit, hehe.

Danke

################################## Der Trigger ##################################

GO
CREATE TRIGGER person_to_history
ON person
FOR UPDATE, DELETE
AS
DECLARE @action int

-- Aktion ermitteln
IF (SELECT MAX(person_id) FROM person WHERE person_id IN (SELECT person_id FROM deleted)) IS NULL
SELECT @action = 4
IF (SELECT MAX(person_id) FROM person_history WHERE person_id IN (SELECT person_id FROM deleted)) IS NULL
SELECT @action = 2
ELSE
SELECT @action = 3

-- Datensatz einfügen
INSERT INTO person_history
SELECT (SELECT MAX(person_history_id) + 1 FROM person_history), @action, * from DELETED

######################################################################################################

 

[DrColossos]

Hier nun meine bisher beste Lösung, mit einem CURSOR.

Eine WHILE-Schleife ohne Cursor funktionierte leider nicht.

Die Idee wäre gewesen, einfach mittels @@ROWCOUNT zu ermitteln, wieviele Datensätze geändert wurden.
Dann hätte ich pro Schleife (von 0 bis @@ROWCOUNT) den jeweiligen Datensatz in die History geschrieben. Leider weiss ich nicht, wie ich bei einem Statement wie SELECT person_id FROM DELETED (können beliebig viele Person-IDs sein) in einer Schleife zuerst die 1., dann die 2., dann ... die i-te Person-ID bekomme.

Also am Ende doch per Cursor ... vielleicht hilfts wem ...

/* Test-Tabellen definieren */
IF EXISTS (SELECT * FROM sysobjects WHERE id=object_id(N'[dbo].[person]') AND OBJECTPROPERTY(id, N'IsTable')=1)
DROP TABLE person
CREATE TABLE person
(
person_id int NOT NULL PRIMARY KEY,
person_name varchar(20),
person_alter int
)
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id=object_id(N'[dbo].[person_history]') AND OBJECTPROPERTY(id, N'IsTable')=1)
DROP TABLE person_history
CREATE TABLE person_history
(
person_history_id int NOT NULL,
aktion int NOT NULL,
person_id int NOT NULL,
person_name varchar(20),
person_alter int
PRIMARY KEY (person_history_id)
)
GO

/* Trigger definieren */
IF EXISTS (SELECT * FROM sysobjects WHERE id=object_id(N'[dbo].[person_to_history]') AND OBJECTPROPERTY(id, N'IsTrigger')=1)
DROP TRIGGER person_to_history
GO
CREATE TRIGGER person_to_history
ON person
AFTER UPDATE, DELETE
AS

/* Hilfsvariablen */
DECLARE @max_id int
DECLARE @del_id int
DECLARE @action int


/* Aktion ermitteln */
IF (SELECT MAX(person_id) FROM person WHERE person_id IN (SELECT person_id FROM deleted)) IS NULL
SELECT @action = 4
ELSE IF (SELECT MAX(person_id) FROM person_history WHERE person_id IN (SELECT person_id FROM deleted)) IS NULL
SELECT @action = 2
ELSE
SELECT @action = 3

/* mittels Cursor bearbeitete Zeilen iterieren */
DECLARE history_cursor CURSOR FOR
(SELECT person_id FROM DELETED)
OPEN history_cursor
FETCH NEXT FROM history_cursor INTO @del_id

WHILE @@FETCH_STATUS = 0
BEGIN
/* aktuell höchste ID ermitteln -> neue ID = aktuelles Maximum + 1 */
SELECT @max_id = (SELECT (MAX(person_history_id) + 1) FROM person_history)
IF @max_id IS NULL
SELECT @max_id = 1
/* Datensatz in History schreiben */
INSERT INTO person_history SELECT @max_id, @action, * FROM DELETED WHERE person_id = @del_id
/* nächste Zeile holen */
FETCH NEXT FROM history_cursor INTO @del_id
END
CLOSE history_cursor
DEALLOCATE history_cursor
GO

/* Testdaten erzeugen */
INSERT INTO person values (1, 'A', 10)
INSERT INTO person values (2, 'B', 20)
INSERT INTO person values (3, 'C', 30)
SELECT * from person
SELECT * from person_history

/* Test 1: Namen einer Person ändern */
UPDATE person SET person_name = 'Z' WHERE person_id = 1
SELECT * from person
SELECT * from person_history

/* Test 2: Namen mehrerer Personen ändern */
UPDATE person SET person_name = 'AB'
SELECT * from person
SELECT * from person_history

/* Test 2: mehrere Personen löschen */
DELETE FROM person WHERE person_alter >= 20
SELECT * from person
SELECT * from person_history