Hallo zusammen,<br><br>
sorry für das nun folgende längere Posting, aber irgendwie stehe ich am Schlauch. Ich habe eine Tabelle Accommodation mit einem Primärschlüssel auf einem Identity Feld ID. Ich möchte nun DELETE Operationen auf dieser Tabelle in zwei unterschiedliche Tabellen mitprotokollieren, wobei die erste Log-Tabelle allgemeine Informationen wie den Zeitstempel, Benutzername, Primärschlüsselwert, ... beinhaltet. Die zweite Log-Tabelle referenziert die erste und speichert konkrete protokollierte Spaltenwerte. Der AFTER DELETE Trigger für die Tabelle Accommodation sieht in etwa wie folgt aus:
<pre>
CREATE TRIGGER TRI_ACCOMMODATION_L_D ON ACCOMMODATION
AFTER DELETE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON
IF (EXISTS(SELECT * FROM DELETED))
BEGIN
DECLARE @VAR_ID INT
DECLARE @PKEY1_VALUE VARCHAR(20)
DECLARE @COLUMN_VALUE VARCHAR(255)
DECLARE DELETED_CURSOR CURSOR FOR SELECT ID FROM DELETED
OPEN DELETED_CURSOR
FETCH NEXT FROM DELETED_CURSOR INTO @PKEY1_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
-- An entry in the operation log table
EXEC P_INSERTINTOMSSQLLM$OPERATIONLOG 'ACCOMMODATION', 'D', 'ID', @PKEY1_VALUE, @VAR_ID = @VAR_ID OUTPUT
-- For each registered column an entry in the column log table
SELECT @COLUMN_VALUE = ID FROM DELETED WHERE ID = @PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'ID', @OLD_VALUE = @COLUMN_VALUE
SELECT @COLUMN_VALUE = NAME FROM DELETED WHERE ID = @PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'NAME', @OLD_VALUE =
@COLUMN_VALUE
SELECT @COLUMN_VALUE = NUMBEROFBEDS FROM DELETED WHERE ID =
@PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'NUMBEROFBEDS', @OLD_VALUE
= @COLUMN_VALUE
....
FETCH NEXT FROM DELETED_CURSOR INTO @PKEY1_VALUE
END
CLOSE DELETED_CURSOR
DEALLOCATE DELETED_CURSOR
END
END
</pre>
Die Stored Procedure P_INSERTINTOMSSQLLM$OPERATIONLOG macht einfach ein INSERT INTO in die erste Log-Tabelle, und gibt die Identity des eingefügten Log-Datensatzes zurück, um danach etwas in die zweite Log-Tabelle eintragen zu können. Der Grund warum ich einen CURSOR verwende ist, dass ich einen Row-Level-Trigger simulieren möchte, d.h. wenn z.B. mit einem DELETE FROM ... 1000 Datensätze gelöscht werden, dann möchte ich auch 1000 Log-Datensätze in der ersten Log-Tabelle haben, und bei jedem Durchlauf werden auch die entsprechenden protokollierten Spaltenwerte in der zweiten Log-Tabelle eingetragen. Das Problem an der Sache ist nun, dass ein Löschen von 1000 Datensätzen in Accommodation über eine Minute dauert. Ich benutze den selben Triggeraufbau auch in einem AFTER INSERT Trigger (die temp. Tabelle DELETED ist durch INSERTED ausgetauscht), und hierbei wird ein Einfügen von 1000 Datensätzen in ca. einer Sekunde, inkl. Protokollierung durchgeführt. Das Einfügen über P_INSERTINTOMSSQLLM$COLUMNLOG ist nicht das Problem. Es sieht eher danach aus, dass SELECT @COLUMN_VALUE = ID FROM DELETED WHERE ID = @PKEY1_VALUE
die Bremse ist. Nochmals, das selbe Konstrukt, jedoch INSERTED statt DELETED ist pfeilschnell in einem AFTER INSERT Trigger.
<br><br>
Hat jemand eine Ahnung, warum der AFTER DELETE Trigger sich so bremsend auswirkt?
<br><br>
Besten Dank,<br>
Thomas
sorry für das nun folgende längere Posting, aber irgendwie stehe ich am Schlauch. Ich habe eine Tabelle Accommodation mit einem Primärschlüssel auf einem Identity Feld ID. Ich möchte nun DELETE Operationen auf dieser Tabelle in zwei unterschiedliche Tabellen mitprotokollieren, wobei die erste Log-Tabelle allgemeine Informationen wie den Zeitstempel, Benutzername, Primärschlüsselwert, ... beinhaltet. Die zweite Log-Tabelle referenziert die erste und speichert konkrete protokollierte Spaltenwerte. Der AFTER DELETE Trigger für die Tabelle Accommodation sieht in etwa wie folgt aus:
<pre>
CREATE TRIGGER TRI_ACCOMMODATION_L_D ON ACCOMMODATION
AFTER DELETE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON
IF (EXISTS(SELECT * FROM DELETED))
BEGIN
DECLARE @VAR_ID INT
DECLARE @PKEY1_VALUE VARCHAR(20)
DECLARE @COLUMN_VALUE VARCHAR(255)
DECLARE DELETED_CURSOR CURSOR FOR SELECT ID FROM DELETED
OPEN DELETED_CURSOR
FETCH NEXT FROM DELETED_CURSOR INTO @PKEY1_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
-- An entry in the operation log table
EXEC P_INSERTINTOMSSQLLM$OPERATIONLOG 'ACCOMMODATION', 'D', 'ID', @PKEY1_VALUE, @VAR_ID = @VAR_ID OUTPUT
-- For each registered column an entry in the column log table
SELECT @COLUMN_VALUE = ID FROM DELETED WHERE ID = @PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'ID', @OLD_VALUE = @COLUMN_VALUE
SELECT @COLUMN_VALUE = NAME FROM DELETED WHERE ID = @PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'NAME', @OLD_VALUE =
@COLUMN_VALUE
SELECT @COLUMN_VALUE = NUMBEROFBEDS FROM DELETED WHERE ID =
@PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'NUMBEROFBEDS', @OLD_VALUE
= @COLUMN_VALUE
....
FETCH NEXT FROM DELETED_CURSOR INTO @PKEY1_VALUE
END
CLOSE DELETED_CURSOR
DEALLOCATE DELETED_CURSOR
END
END
</pre>
Die Stored Procedure P_INSERTINTOMSSQLLM$OPERATIONLOG macht einfach ein INSERT INTO in die erste Log-Tabelle, und gibt die Identity des eingefügten Log-Datensatzes zurück, um danach etwas in die zweite Log-Tabelle eintragen zu können. Der Grund warum ich einen CURSOR verwende ist, dass ich einen Row-Level-Trigger simulieren möchte, d.h. wenn z.B. mit einem DELETE FROM ... 1000 Datensätze gelöscht werden, dann möchte ich auch 1000 Log-Datensätze in der ersten Log-Tabelle haben, und bei jedem Durchlauf werden auch die entsprechenden protokollierten Spaltenwerte in der zweiten Log-Tabelle eingetragen. Das Problem an der Sache ist nun, dass ein Löschen von 1000 Datensätzen in Accommodation über eine Minute dauert. Ich benutze den selben Triggeraufbau auch in einem AFTER INSERT Trigger (die temp. Tabelle DELETED ist durch INSERTED ausgetauscht), und hierbei wird ein Einfügen von 1000 Datensätzen in ca. einer Sekunde, inkl. Protokollierung durchgeführt. Das Einfügen über P_INSERTINTOMSSQLLM$COLUMNLOG ist nicht das Problem. Es sieht eher danach aus, dass SELECT @COLUMN_VALUE = ID FROM DELETED WHERE ID = @PKEY1_VALUE
die Bremse ist. Nochmals, das selbe Konstrukt, jedoch INSERTED statt DELETED ist pfeilschnell in einem AFTER INSERT Trigger.
<br><br>
Hat jemand eine Ahnung, warum der AFTER DELETE Trigger sich so bremsend auswirkt?
<br><br>
Besten Dank,<br>
Thomas
Comment