Instead-of-Trigger und Check-Constraints

[ukulele]

Ich habe im Moment die Situation das ich viel mit After-Triggern arbeite. Allerdings erweißt sich das in vielen Situationen als problematisch und unperformant., ich würde daher gerne Instead-of-Trigger verwenden. Hierzu habe ich aber mal noch Fragen die mir noch nicht ganz klar sind.

Erstmal ein Beispielszenario:

Tabelle T hat eine Spalte für eine E-Mail Adresse, eine für die Domain und eine für die TLD. Wird nun eine E-Mail Adresse eingetragen wurden bisher im Nachgang zwei Trigger abgefeuert. Trigger A hat geprüft, ob das Format der E-Mail Adresse gültig ist. War z.B. kein @ enthalten wurde die Operation mit ROLLBACK anuliert und mit RAISERROR eine Entsprechende Meldung ausgeben. Trigger B hat aus der E-Mail Adresse nachträglich die Domain und die TLD ermittelt, sofern diese nicht bereits vorhanden waren, und in die vorgesehenen Spalten geschrieben.

Das Szenario ist natürlich stark vereinfacht, kommt aber so in unterschiedlichsten Ausprägungen in vielen meiner Tabellen vor. Wenn ich diese beiden Prozesse jetzt per Instead-of und / Check-Constraint lösen will ergeben sich folgende Fragen:

1) Kann ich z.B. zwei verschiedene Instead-of-Insert Trigger anwenden? Wenn ja, in welcher Reihenfolge werden sie ausgeführt?
2) Wer MSSQL kennt weiß, das Trigger immer nur einmal pro Update-Statement feuern. Ist das bei Instead-of-Triggern genauso?
3) Ich könnte mir überlegen, jede einzelne Prüfroutine und jeden automatisch ermittelten Wert per Funktion an Default- und Check-Constraints zu hängen. Kann ich bei einem Check-Constraint eine explizite Fehlermeldung an die Anwendung zurück geben?

 

[ukulele]

Und hier mal gleich noch ein Problem: Ich habe eine Funktion die mir den Default Wert einer Spalte bestimmt. Dieser Funktion muss ich aber zwei Paramter übergeben, also die Werte zweier anderer Spalten. Geht das überhaupt?

ALTER TABLE [dbo].[url] ADD CONSTRAINT url_scheme_default DEFAULT [dbo].[function_url_get_scheme]([adresse],[typ]) FOR [scheme];

Der Name "adresse" ist in diesem Kontext nicht zulässig. Gültige Ausdrücke sind Konstanten, konstante Ausdrücke und (in bestimmten Kontexten) Variablen. Spaltennamen sind nicht zulässig.

 

[akretschmer]

Trigger B hat aus der E-Mail Adresse nachträglich die Domain und die TLD ermittelt, sofern diese nicht bereits vorhanden waren, und in die vorgesehenen Spalten geschrieben.

Dies erscheint mir redundant.

 

[ukulele]

Es ist redundant aber sinnvoll weil meine Anwendung nur in Inhalten der Tabelle, nicht in Inhalten einer View oder ähnliches vernünftig suchen kann. Außderm muss ich diese Informationen nicht jedesmal neu erzeugen.

Frage 2) muss ich ürigens mit Ja beantworten :/

 

[akretschmer]

Hrm. ich würde da eher zu Indexen greifen:

test=*# select * from mail;
  adr
----------------
 foo@domain.tld
 bar@batz.de
(2 rows)

Time: 0,179 ms
test=*# create index tld_index on mail(regexp_replace(adr,'^.*\.',''));
CREATE INDEX
Time: 14,268 ms
test=*# set enable_seqscan to off;
SET
Time: 0,052 ms
test=*# explain select * from mail where regexp_replace(adr,'^.*\.','') = 'tld';
  QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using tld_index on mail  (cost=0.13..8.14 rows=1 width=32)
  Index Cond: (regexp_replace(adr, '^.*\.'::text, ''::text) = 'tld'::text)
(2 rows)
test=*# select * from mail where regexp_replace(adr,'^.*\.','') = 'tld';
  adr
----------------
 foo@domain.tld
(1 row)

Time: 0,354 ms

Erscheint mir besser als Redundanz mit all seinen Problemen.

 

[Distrilec]

Wie wäre es denn mit einem Before-Insert Trigger?
Man kann:
a) Prüfen ob das EMail Format in Ordnung ist
b) Ggbfs. benötigte Werte berechnen lassen
c) Eigene Exceptions auslösen...

Scheint mir die eleganteste Lösung für dein "Problem" ?

Edit: Man spart sich auch ein After-Trigger Rollback ... Wo sich mir die Sinnlichkeit noch nicht so ganz erschließt, weil es z.B. logging im Trigger unmöglich macht...

 

[ukulele]

Stimmt die Exceptions sind bisher auch von einem Before-Trigger ausgelöst worden, macht ja auch Sinn. Wenn ich beides habe, wird dann erst der Before- und im Anschluss der Instead-of-Trigger auslösen oder ist das Zufall?

Mir ist beim Testen ein weiteres Problem aufgefallen: Ich schreibe noch eine Log. In der Log werden Änderungen des Triggers auch als solche ausgewiesen. Ich glaube das kriege ich nur mit einem After-Trigger vernünftig hin.

 

[Distrilec]

Ich glaube das kriege ich nur mit einem After-Trigger vernünftig hin.

Warum? In eine zweite Tabelle kannst du auch in einem Before-Trigger schreiben...

 

[ukulele]

Richtig. Dann hätte ich Prüffunktionen, Ergänzung der Daten und Logging in einem Before-Trigger, was durchaus viel wird. Ich gehe jetzt einen etwas anderen Weg:

Ein Default-Constraint setzt eine Hilfsspalte (verklagt mich!) auf -1, wobei -1 die negierte Anzahl der (After-)Trigger repräsentiert die auf der Tabelle Daten verändern. Null stellt den Endzustand dar und positive Werte Trigger, die Daten in andere Tabellen schreiben.
Ein Before Trigger mit Prüffunktionen, wie gehabt, feuert als Erster
After Trigger #1: Vervollständigt die Daten soweit möglich und setzt die Hilfsspalte einen hoch, zunächst mal auf Null
After Trigger #2: Loggt den Vorgang und setzt die Hilfsspalte + 1

In diesem Fall ist dann erstmal Ende und damit kann ich alles abbilden was ich brauche.

 

[ukulele]

Einzig die Reihenfolge Before-/Instead-of-Trigger würde mich noch interessieren. Und natürlich eine Möglichkeit meinen Check Constraint wie in Post #2 umzusetzen.

 

[akretschmer]

Einzig die Reihenfolge Before-/Instead-of-Trigger würde mich noch interessieren.

In PG feuern TRIGGER alphabetisch sortiert. Vielleicht in M$SQL auch?

 

[ukulele]

Ich habe mal versucht das raus zu finden. Damals kam ich zu dem Schluss das ein später angelegter Trigger später feuert. Das kann aber Mumpitz sein.

 

[Distrilec]

sp_settriggerorder (Transact-SQL)
Erster und letzter Trigger kann man scheinbar festlegen, der rest ist unsortiert...

 

[ukulele]

Also nach viel rumgehampel habe ich feststellen müssen, das FOR und AFTER Trigger in MSSQL das Selbe sind (Ich nahm an FOR wäre BEFORE, soetwas hat MS aber nicht). Meine Lösung mit dem Zähler hat sich auch als nervtötend rausgestellt da ein Trigger auf der eigenen Tabelle im Nachgang Updates ausführt und sich somit immer wieder ausführt. Jetzt habe ich einen neuen Plan:

1) INSTEAD OF-Trigger prüft die Daten auf Plausibilität (Alles was sich nicht mit CHECK Constraint abbilden läßt oder Fehlermeldungen zurück geben soll.)

2) AFTER-Trigger @order=FIRST ergänzt die Daten sofern das automatisch möglich ist (Redundanz ist hier nicht entscheidend). Beide Trigger nutzen die selben Funktionen um z.B. den Typ 'E-Mail' zu erkennen und darauf baisierend entweder das Format zu prüfen oder Domain und TLD zu ermitteln. Obowhl die Funktion zur Bestimmung des Typs also unter Umständen öfter aufgerufen wird als es nötig wäre wenn ich beide Trigger konsolidieren würde, möchte ich sie dennoch trennen, weil
a) ich klarer zwischen Daten prüfen und Daten erzeugen unterscheide
b) und mein nächster Trigger (Logging) erkennen kann, welche Daten vom User und welche vom Trigger erstellt / verändert wurden.

3) AFTER-Trigger @order =NONE Loggt die Eingaben. Meine Logging-Trigger sind sehr umfangreich und werden per Script automatisch erzeugt, daher würde ich diese auch sehr gerne seperat belassen.

4) AFTER-Trigger @order=LAST gibt es derzeit keinen, würde aber in einigen Fällen abhängige Operationen in andern Tabellen auslösen.

Mal sehen ob das Konstrukt so standhält.