MSSQL 2012: Papierkorb für SQL-Daten

In eines der nächsten Projekte ist es erforderlich, dass gelöschte Datensätze nicht gelöscht werden sondern aufgehoben werden und zu jedem Zeitpunkt wiederhergestellt werden können.
Da gelöschte Datensätze im SQL-Server entgültig gelöscht sind, muss man sich so eine „Papierkorb-Funktion“ selbst bauen.
Eine Möglichkeit dies zu tun ist die Datenbank-Trigger auf Tabellen-Ebene zu benutzen wofür ich hier ein Beispiel geben möchte.

Als erstes Brauchen wir eine Tabelle welche die Datensätze beinhaltet, welche wir löschen wollen.
Ich nenne diese Tabelle „testTabelle“ mit folgender Struktur:

CREATE TABLE [dbo].[testTab](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nchar](30) NULL,
	[email] [nchar](30) NULL,
	[geb] [date] NULL
) ON [PRIMARY]
 
GO

Jetzt brauchen wir noch einen Datensatz in dieser Tabelle:

1	INSERT INTO testTabelle (name,email,geb) VALUES('sven','sven@frickelblog.de','1983-05-08')

Ergebnis:

SELECT * FROM testTabelle
 
id          name                           email                          geb
----------- ------------------------------ ------------------------------ ----------
1           sven                           sven@frickelblog.de            1983-05-08

Das Konzept sieht vor das die gelöschten Datensätze als XML-Datentyp in eine „Papierkorb-Tabelle“ verschoben werden.
Hierfür müssen wir erst einmal den Datensatz als XML aus der Tabelle bekommen.
Für den eben eingefügten Datensatz funktioniert dies wie folgt:

1	SELECT * FROM testTabelle WHERE id=1 FOR XML AUTO, ELEMENTS

Als ergebnis bekommt man folgenden XML String, welcher den kompletten Datensatz aus der testTabelle spiegelt:

<testTabelle>
  <id>1</id>
  <name>sven                          </name>
  <email>sven@frickelblog.de           </email>
  <geb>1983-05-08</geb>
</testTabelle>

Jetzt brauchen wir noch eine Tabelle in der wir die gelöschten Datensätze speichern.
Diese Tabelle nenne ich „dele“ mit folgender Struktur:

CREATE TABLE [dbo].[dele](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[xmldata] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO

Um jetzt einen Datensatz aus der Tabelle „testTabelle“ zu nehmen und in die „dele“ tabelle als XMl String einzufügen müssen wir folgenden Query ausführen:

1
2
3

declare @xml xml
set @xml = (SELECT * FROM testTabelle WHERE id=1 FOR XML AUTO, ELEMENTS)
INSERT INTO dele (xmldata) VALUES (@xml)

In der „dele“ Tabelle sieht der Datensatz nun wie folgt aus:

SELECT * FROM dele
id  xmldata
--- -------------------------------------------------------------------------------------------------------------
1   <testTabelle><id>1</id><name>sven</name><email>sven@frickelblog.de</email><geb>1983-05-08</geb></testTabelle>

Das auslesen eines Datensatz und speichern in einer anderen Tabelle als XML funktioniert somit also.
Jetzt wollen wir das ganze automatisch machen lassen sobald in der Tabelle „testTabelle“ ein Datensatz gelöscht wird.
Hierfür erstellen wir folgenden DELETE-Trigger in dem wir den eben verwendeten Code einfügen:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Sven Schmalle
-- Create date: 24.01.2013
-- Description:	Papierkorb
-- =============================================
CREATE TRIGGER TrDelete 
   ON testTabelle 
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
	declare @xml xml
 
	set @xml = (SELECT * FROM deleted WHERE id=deleted.id FOR XML AUTO, ELEMENTS)
 
	INSERT INTO dele (xmldata) VALUES (@xml)
END
GO

Löscht man nun den Datensatz in der Tabelle „testTabelle“…

1	DELETE FROM testTabelle WHERE id=1

… ist der Datensatz in der testTabelle gelöscht worden:

SELECT * FROM testTabelle
 
id          name                           email                          geb
----------- ------------------------------ ------------------------------ ----------
 
(0 Zeile(n) betroffen)

Aber als XML String in der „dele“-tabelle eingefügt worden:

SELECT * FROM dele
 
id   xmldata
---- ------------------------------------------------------------------------------------------------------------------------------
1    <testTabelle><id>1</id><name>sven</name><email>sven@frickelblog.de</email><geb>1983-05-08</geb></testTabelle>
2    <deleted><id>1</id><name>sven</name><email>sven@frickelblog.de</email><geb>1983-05-08</geb></deleted>
 
(2 Zeile(n) betroffen)

Das war es auch schon 🙂

…ein mal im leben mit Profis zusammen arbeiten ;o)