INSERTS abfangen und umleiten

9. Oktober 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Vorab: Das Ganze ist hier mehr exemplarisch und war eher als „proof of concept“ gedacht. Das Beispiel ist extra einfach gehalten und soll das Verfahren nur verdeutlichen.

Problemstellung:

Wir haben eine externe Software, die mit einer proprietärer Datenbank arbeitet, auf die man von Außen nicht zugreifen kann, wir aber nun die Daten benötigen. Es steht nur eine Exportfunktion zur Verfügung, um den Bestand komplett zu exportieren. Neben einem Flatfile-Export kann die Software auch in eine SQL Server Datenbank schreiben, aber

- festes, denormalisiertes Tabellendesign

- nur Varchar-Felder / Daten

- nur INSERT Statements

Also ein ganz „toller“ SQL Export, wurde wohl nur zu Marketing-Zwecken eingebaut, „Tooles Feature, wir können in eine SQL DB schreiben!“ (boah, ey).

Nun könnte man sagen: „Lass machen; wenn es fertig ist, bereite ich mir die Daten anderweitig mit SSIS und/oder SQL Scripten auf“; also ein Staging-Bereich im Dataware House zu Verfügung stellen und damit weiter arbeiten. Bei einem umfangreichen Szenario, sprich viel Tabelle und/oder Daten, wäre der Aufwand zur Sicherstellung der Daten-Qualität und Performanz gerechtfertig.

Und wie könnte man es ohne großen Aufwand als kleine, prozessunabhängige Lösung umsetzen?

Lösungsansatz:

Des Softwares Wille soll sein Himmelreich (oder was immer) sein. Aber eine Tabelle gibt es trotzdem nicht, da bin ich Stur.

Die eigentlichen Tabellen lege ich nach meinen Vorstellungen und natürlich normalisiert an. Hinzu kommt dann ein View, das von den Feldern her dem geforderten Tabellendesign entspricht und die Daten aus meinen Tabellen selektiert.

Dieses View ist aber definitiv nicht aktualisierbar, INSERTs können darauf nicht ausgeführt werden; wie kann jetzt trotzdem die externe Software hier rein schreiben?

Ganz einfach, mittels eines INSTEADOF Trigger, der, wieder Name schon sagt, statt des eigentlichen INSERTs ausgeführt wird und in der virtuellen Tabelle „inserted“ die Daten mit Tabellendesigns der View hat. Im Trigger kann man dann die Business Logic umsetzten, mit der man die Daten in die eigentlichen Tabellen weg schreibt.

Hier mein Demo Script dazu:

 SET NOCOUNT ON; -- Kein Feedback über Anzahl

 -- Basistabellen anlegen

 CREATE TABLE dbo.FirstNames

   (Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,

   firstname nvarchar(50) NOT NULL UNIQUE);

 CREATE TABLE dbo.LastNames

   (Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

   lastname nvarchar(50) NOT NULL UNIQUE);

 CREATE TABLE dbo.Names

   (Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

   firstname_id int NOT NULL,

   lastname_id int NOT NULL);

GO

 -- Kombination Vor/Nachname Unique

 CREATE UNIQUE NONCLUSTERED INDEX [UQ_FirstLastName]

 ON [dbo].[Names]

   ([firstname_id] ASC,

   [lastname_id] ASC  )

GO

 -- Sicht mit den Namen auf die Basistabellen

 CREATE VIEW dbo.vwNames

AS

   SELECT N.Id, F.firstname, L.lastname

   FROM dbo.Names AS N

   INNER JOIN dbo.FirstNames AS F

   ON N.firstname_id = F.Id

   INNER JOIN dbo.LastNames AS L

   ON N.lastname_id = L.Id;

GO

 -- Geht natürlich (noch) nicht

 INSERT INTO dbo.vwNames (firstname, lastname)

 VALUES ('Olaf', 'Helper');

GO

 -- InsteadOf Trigger zum Abfangen der INSERTS

 CREATE TRIGGER dbo.TRI_vwNames ON dbo.vwNames

 INSTEAD OF INSERT

AS

 BEGIN

   -- Fehlende Vornamen eintragen

   INSERT INTO dbo.FirstNames (firstname)

   SELECT DISTINCT I.firstname  -- Distinct wegen Unique!!

   FROM inserted as I

   LEFT JOIN dbo.FirstNames AS F

   ON I.firstname = F.firstname

   WHERE F.firstname IS NULL

   AND NOT I.firstname IS NULL

   -- Dito Nachnamen

   INSERT INTO dbo.LastNames (lastname)

   SELECT DISTINCT I.lastname  -- Distinct wegen Unique!!

   FROM inserted as I

   LEFT JOIN dbo.LastNames AS L

   ON I.lastname = L.lastname

   WHERE L.lastname IS NULL

   AND NOT I.lastname IS NULL

   -- Nun fehlende Namenseinträge

   INSERT INTO dbo.Names (firstname_id, lastname_id)

   SELECT DISTINCT F.Id, L.Id  -- Distinct wegen Unique!!

   FROM inserted AS I

   INNER JOIN dbo.FirstNames AS F

   ON I.firstname = F.firstname

   INNER JOIN dbo.LastNames AS L

   ON I.lastname = L.lastname

   LEFT JOIN dbo.Names AS N

   ON N.firstname_id = F.Id

   AND N.lastname_id = L.Id

   WHERE N.Id IS NULL

 END;

GO

 -- Nun mehrere Datensätze in einem Rutsch

 -- direkt ins View schreiben

 INSERT INTO dbo.vwNames

   (firstname, lastname)

 SELECT 'Olaf' AS firstname, 'Helper' AS lastname

 UNION ALL SELECT 'Peter',  'Mustermann'

 UNION ALL SELECT 'Anonymus', 'Unbekannt'

 UNION ALL SELECT 'Peter',  'Unbekannt' -- Vorhandener Vor+Nachname

 UNION ALL SELECT 'Olaf',  'Helper'  -- Doppelter Name

 UNION ALL SELECT NULL,  NULL  -- Schrott

GO

 -- Angekommen? Jawoll!

 SELECT * FROM dbo.vwNames

GO

 Ergebnis:

 Id  firstname  lastname

 --- ---------- ------------

 1  Anonymus  Unbekannt

 2  Olaf  Helper

 3  Peter  Mustermann

 4  Peter  Unbekannt

Fazit:

Mit der Methode kann man zum einen Fremdappliaktionen einen Schreibzugriff einrichten und zugleich die eigentliche Datenbankstruktur gegenüber Außen völlig transparent halten. Zudem lässt sich so eine kleiner datengetriebener ETL Prozeß realisieren.

Zugegeben, nichts neues, nichts wildes, aber wollte ich halt mal ausprobieren.

Diesen Post teilen

Repost0