Schreibbare Views mit INSTEAD OF Triggern in SQL Server
Manchmal hat ein Eingabetool eine einfache Einschränkung: Es kann nur gegen eine tabellenartige Struktur schreiben. Fachlich liegen die Daten aber in mehreren Tabellen. Genau hier können Views mit INSTEAD OF Triggern helfen.
In diesem Beispiel gibt es eine Produkttabelle mit Produktnummer und Preis. Zusätzlich gibt es eine Sprachentabelle mit Produktnummer, Sprachencode und Produktname. Der Produktname ist also sprachabhängig, der Preis dagegen ein globales Produktattribut.
Warum das für Apparo Fast Edit relevant ist
In Apparo Fast Edit basiert ein Business Case typischerweise auf einer zentralen schreibbaren Datenstruktur. Über Lookups können zwar zusätzliche Informationen aus anderen Tabellen angezeigt oder ausgewählt werden, das eigentliche Schreiben erfolgt aber auf die definierte Zielstruktur des Business Case.
Wenn die fachlichen Daten jedoch auf mehrere physische Tabellen verteilt sind, entsteht eine typische Herausforderung. Im Beispiel liegen Produktnummer und Preis in einer Produkttabelle, während die sprachabhängigen Produktnamen in einer separaten Sprachentabelle gespeichert werden.
Auch in Apparo Fast Edit ist diese Anforderung einfach zu lösen indem nach der Eingabe der Daten ein UPDATE- oder INSERT-Skript aufgerufen wird. Diese Lösung stellen wir hier vor: Daten in mehrere Tabellen einfügen.
Für den Anwender soll das trotzdem wie eine einzige bearbeitbare Tabelle aussehen. Genau dafür kann eine SQL-View mit INSTEAD OF Triggern eingesetzt werden.
Apparo Fast Edit schreibt dann gegen die View. Die Datenbank übernimmt über die Trigger die technische Verteilung der Änderungen auf die eigentlichen Basistabellen. Für den Business Case bleibt das Modell einfach, während die Datenbankstruktur sauber normalisiert bleiben kann.
Der Vorteil: Der Anwender pflegt Produktnummer, Preis und Produktname in einer einzigen Oberfläche. Im Hintergrund wird der Preis in der Produkttabelle gespeichert, während der sprachabhängige Produktname in der Sprachentabelle gepflegt wird.
Das Datenmodell
Die Haupttabelle enthält die produktbezogenen Stammdaten:
CREATE TABLE dbo.Produkt (
Produktnummer INT NOT NULL PRIMARY KEY,
Preis DECIMAL(10,2) NOT NULL
);Die Sprachentabelle enthält die sprachabhängigen Produktnamen:
CREATE TABLE dbo.ProduktSprache (
Produktnummer INT NOT NULL,
Sprachencode CHAR(2) NOT NULL,
Produktname NVARCHAR(200) NOT NULL,
CONSTRAINT PK_ProduktSprache
PRIMARY KEY (Produktnummer, Sprachencode),
CONSTRAINT FK_ProduktSprache_Produkt
FOREIGN KEY (Produktnummer)
REFERENCES dbo.Produkt (Produktnummer)
ON DELETE CASCADE
);Die Herausforderung
Ein deutscher Benutzer sieht deutsche Produktnamen. Ein englischer Benutzer sieht englische Produktnamen. Das klingt zunächst nach einer einfachen gefilterten View.
Problematisch wird es aber, wenn ein Produkt bereits existiert, jedoch noch keine englische Übersetzung vorhanden ist. Bei einer normalen inneren Verknüpfung würde der englische Benutzer das Produkt gar nicht sehen. Er könnte also annehmen, dass das Produkt noch nicht existiert.
Die bessere Lösung ist daher eine View mit LEFT JOIN. Dadurch werden Produktnummer und Preis immer angezeigt. Wenn der englische Produktname noch fehlt, bleibt dieser einfach leer.
View für englische Benutzer
CREATE VIEW dbo.v_Produkt_EN
AS
SELECT
p.Produktnummer,
p.Preis,
CAST('EN' AS CHAR(2)) AS Sprachencode,
ps.Produktname
FROM dbo.Produkt p
LEFT JOIN dbo.ProduktSprache ps
ON ps.Produktnummer = p.Produktnummer
AND ps.Sprachencode = 'EN';
GOWichtig ist hier: Der Filter auf EN steht im ON-Teil des Joins, nicht im WHERE-Teil. Würde man den Sprachfilter in die WHERE-Bedingung schreiben, würde der LEFT JOIN faktisch wieder wie ein INNER JOIN wirken.
Beispieldaten
INSERT INTO dbo.Produkt
VALUES (123, 10.00);
INSERT INTO dbo.ProduktSprache
VALUES (123, 'DE', N'Stuhl');Das Produkt existiert also bereits. Es hat die Produktnummer 123, kostet 10,00 Euro und hat bisher nur einen deutschen Namen: Stuhl.
Ein englischer Benutzer sieht über die View trotzdem bereits das Produkt:
SELECT *
FROM dbo.v_Produkt_EN;Das Ergebnis wäre sinngemäß:
Produktnummer | Preis | Sprachencode | Produktname
123 | 10.00 | EN | NULLDer Benutzer sieht also: Das Produkt existiert bereits. Nur der englische Produktname fehlt noch.
Schreiben auf die View
Damit ein Eingabetool gegen diese View schreiben kann, werden INSTEAD OF Trigger verwendet. Diese Trigger fangen INSERT, UPDATE und DELETE auf der View ab und führen stattdessen die passenden Änderungen auf den Basistabellen aus.
INSERT-Trigger
Beim Insert wird zuerst geprüft, ob das Produkt bereits existiert. Falls ja, wird der Preis aktualisiert. Falls nein, wird das Produkt neu angelegt. Anschließend wird der englische Produktname eingefügt oder aktualisiert.
CREATE TRIGGER dbo.trg_v_Produkt_EN_insert
ON dbo.v_Produkt_EN
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
MERGE dbo.Produkt AS target
USING (
SELECT DISTINCT
Produktnummer,
Preis
FROM inserted
) AS source
ON target.Produktnummer = source.Produktnummer
WHEN MATCHED THEN
UPDATE SET Preis = source.Preis
WHEN NOT MATCHED THEN
INSERT (Produktnummer, Preis)
VALUES (source.Produktnummer, source.Preis);
MERGE dbo.ProduktSprache AS target
USING (
SELECT
Produktnummer,
Produktname
FROM inserted
WHERE Produktname IS NOT NULL
) AS source
ON target.Produktnummer = source.Produktnummer
AND target.Sprachencode = 'EN'
WHEN MATCHED THEN
UPDATE SET Produktname = source.Produktname
WHEN NOT MATCHED THEN
INSERT (Produktnummer, Sprachencode, Produktname)
VALUES (source.Produktnummer, 'EN', source.Produktname);
END;
GOUPDATE-Trigger
Beim Update wird der Preis in der Produkttabelle aktualisiert. Der Produktname wird in der Sprachentabelle für den Sprachencode EN aktualisiert oder neu eingefügt, falls er noch nicht existiert.
CREATE TRIGGER dbo.trg_v_Produkt_EN_update
ON dbo.v_Produkt_EN
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(Produktnummer)
BEGIN
RAISERROR('Produktnummer darf über diese View nicht geändert werden.', 16, 1);
RETURN;
END;
UPDATE p
SET p.Preis = i.Preis
FROM dbo.Produkt p
JOIN inserted i
ON i.Produktnummer = p.Produktnummer;
MERGE dbo.ProduktSprache AS target
USING (
SELECT
Produktnummer,
Produktname
FROM inserted
WHERE Produktname IS NOT NULL
) AS source
ON target.Produktnummer = source.Produktnummer
AND target.Sprachencode = 'EN'
WHEN MATCHED THEN
UPDATE SET Produktname = source.Produktname
WHEN NOT MATCHED THEN
INSERT (Produktnummer, Sprachencode, Produktname)
VALUES (source.Produktnummer, 'EN', source.Produktname);
END;
GODELETE-Trigger
Beim Löschen wird zunächst der englische Spracheintrag entfernt. Danach wird geprüft, ob zu diesem Produkt noch irgendein Spracheintrag existiert. Falls keine Sprache mehr vorhanden ist, wird auch das Produkt selbst gelöscht.
CREATE TRIGGER dbo.trg_v_Produkt_EN_delete
ON dbo.v_Produkt_EN
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE ps
FROM dbo.ProduktSprache ps
JOIN deleted d
ON d.Produktnummer = ps.Produktnummer
WHERE ps.Sprachencode = 'EN';
DELETE p
FROM dbo.Produkt p
JOIN deleted d
ON d.Produktnummer = p.Produktnummer
WHERE NOT EXISTS (
SELECT 1
FROM dbo.ProduktSprache ps
WHERE ps.Produktnummer = p.Produktnummer
);
END;
GOBeispiel: Englischen Produktnamen ergänzen
Der englische Benutzer sieht das Produkt bereits mit Preis, aber ohne Produktnamen. Er kann nun den englischen Namen ergänzen und bei Bedarf auch den Preis ändern.
UPDATE dbo.v_Produkt_EN
SET Produktname = N'Chair',
Preis = 15.00
WHERE Produktnummer = 123;Danach ist der Preis global auf 15.00 geändert und der englische Produktname Chair wurde ergänzt.
Wichtig: Der Preis liegt nicht sprachabhängig in der Sprachentabelle, sondern global in der Produkttabelle. Deshalb sehen anschließend auch andere Sprach-Views den neuen Preis.
Warum der LEFT JOIN wichtig ist
Ohne LEFT JOIN würde ein Benutzer nur Produkte sehen, für die bereits ein Spracheintrag in seiner Sprache existiert. Das kann fachlich irreführend sein.
Mit dem LEFT JOIN sieht der Benutzer dagegen auch Produkte, bei denen die Übersetzung noch fehlt. Dadurch erkennt er vorhandene Produktnummern und vorhandene Preise und ergänzt nur den fehlenden sprachabhängigen Anteil.
Vollständiges Skript
CREATE TABLE dbo.Produkt (
Produktnummer INT NOT NULL PRIMARY KEY,
Preis DECIMAL(10,2) NOT NULL
);
GO
CREATE TABLE dbo.ProduktSprache (
Produktnummer INT NOT NULL,
Sprachencode CHAR(2) NOT NULL,
Produktname NVARCHAR(200) NOT NULL,
CONSTRAINT PK_ProduktSprache
PRIMARY KEY (Produktnummer, Sprachencode),
CONSTRAINT FK_ProduktSprache_Produkt
FOREIGN KEY (Produktnummer)
REFERENCES dbo.Produkt (Produktnummer)
ON DELETE CASCADE
);
GO
CREATE VIEW dbo.v_Produkt_EN
AS
SELECT
p.Produktnummer,
p.Preis,
CAST('EN' AS CHAR(2)) AS Sprachencode,
ps.Produktname
FROM dbo.Produkt p
LEFT JOIN dbo.ProduktSprache ps
ON ps.Produktnummer = p.Produktnummer
AND ps.Sprachencode = 'EN';
GO
CREATE TRIGGER dbo.trg_v_Produkt_EN_insert
ON dbo.v_Produkt_EN
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
MERGE dbo.Produkt AS target
USING (
SELECT DISTINCT
Produktnummer,
Preis
FROM inserted
) AS source
ON target.Produktnummer = source.Produktnummer
WHEN MATCHED THEN
UPDATE SET Preis = source.Preis
WHEN NOT MATCHED THEN
INSERT (Produktnummer, Preis)
VALUES (source.Produktnummer, source.Preis);
MERGE dbo.ProduktSprache AS target
USING (
SELECT
Produktnummer,
Produktname
FROM inserted
WHERE Produktname IS NOT NULL
) AS source
ON target.Produktnummer = source.Produktnummer
AND target.Sprachencode = 'EN'
WHEN MATCHED THEN
UPDATE SET Produktname = source.Produktname
WHEN NOT MATCHED THEN
INSERT (Produktnummer, Sprachencode, Produktname)
VALUES (source.Produktnummer, 'EN', source.Produktname);
END;
GO
CREATE TRIGGER dbo.trg_v_Produkt_EN_update
ON dbo.v_Produkt_EN
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(Produktnummer)
BEGIN
RAISERROR('Produktnummer darf über diese View nicht geändert werden.', 16, 1);
RETURN;
END;
UPDATE p
SET p.Preis = i.Preis
FROM dbo.Produkt p
JOIN inserted i
ON i.Produktnummer = p.Produktnummer;
MERGE dbo.ProduktSprache AS target
USING (
SELECT
Produktnummer,
Produktname
FROM inserted
WHERE Produktname IS NOT NULL
) AS source
ON target.Produktnummer = source.Produktnummer
AND target.Sprachencode = 'EN'
WHEN MATCHED THEN
UPDATE SET Produktname = source.Produktname
WHEN NOT MATCHED THEN
INSERT (Produktnummer, Sprachencode, Produktname)
VALUES (source.Produktnummer, 'EN', source.Produktname);
END;
GO
CREATE TRIGGER dbo.trg_v_Produkt_EN_delete
ON dbo.v_Produkt_EN
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE ps
FROM dbo.ProduktSprache ps
JOIN deleted d
ON d.Produktnummer = ps.Produktnummer
WHERE ps.Sprachencode = 'EN';
DELETE p
FROM dbo.Produkt p
JOIN deleted d
ON d.Produktnummer = p.Produktnummer
WHERE NOT EXISTS (
SELECT 1
FROM dbo.ProduktSprache ps
WHERE ps.Produktnummer = p.Produktnummer
);
END;
GOFazit
Eine View mit LEFT JOIN und INSTEAD OF Triggern kann ein mehrtabelliges Datenmodell für ein einfaches Eingabetool wie eine normale Tabelle wirken lassen.
Der entscheidende Punkt ist die saubere fachliche Modellierung: Der Preis gehört zum Produkt und wird global geändert. Der Produktname gehört zur Sprache und wird je Sprachcode gepflegt. Die View verbindet beides zu einer einfach nutzbaren Eingabestruktur.
