mysql trigger

[der henry]

Hallo,

ich möchte das erste mal einen trigger in einer mysql Datenbank bzw. Tabelle anwenden.

Hierzu habe ich nachfolgendes recherchiert, was aber noch nicht funktioniert, obwohl ich auch die "gehobene Aufgabenstellung" noch gar nicht erfüllt habe. 😟

Ich möchte einen Trigger in der Tabelle "datenpunktliste" bei Eingabe bzw. update (Änderung) auslösen. Dann soll der Wert aus dem Feld "datenpunktliste.spsname" in das Feld "datenpunktliste.spsvarname" geschrieben werden.

Anbei mein Code:

(Edit Rolf B: Zeilenumbrüche)

CREATE DEFINER=`root`@`%`
   TRIGGER `trigg_create_spsvarname`
   AFTER INSERT ON `datenpunktliste`
   FOR EACH ROW 
      INSERT INTO datenpunktliste(spsvarname)
      SELECT datenpunktliste(spsname)

Die endgültige Aufgabenstellung

• Ich möchte einen Trigger auslösen, wenn sich die Felder "datenpunktliste.spsname" oder "datenpunktliste.varname" ändern.

Dann soll der Wert aus dem Feld "datenpunktliste.spsname" und der Wert aus dem Feld "datenpunktliste.varname" inkl. date in das Feld "datenpunktliste.spsvarname"

Ich hoffe, das geht überhaupt, einem Feld einen Trigger zuweisen. Wenn ich der ganzen Tabelle einen Trigger zuweise, löst der Trigger auch wieder aus wenn die das Feld "datenpunktliste.spsvarname" beschreibe. Auch hier habe ich keine Erfahrung, ob so etwas überhaupt geht.

Hier nochmal die Tabelle "datenpunktliste" grafisch.

spsname	  varname	       spsvarname	           istwert	  datentyp
=================================================================
  T1	  drehzahl	   T12024.04.21drehzahl          0	        D	
  T1	  xy_temp	            -	                 0	        D	

Gruß

der henry

[Felix Riesterer]

Lieber Henry,

Dann soll der Wert aus dem Feld "datenpunktliste.spsname" und der Wert aus dem Feld "datenpunktliste.varname" inkl. date in das Feld "datenpunktliste.spsvarname"

mal ungeachtet der neuen Herausforderung einen Trigger zu nutzen: Warum willst Du in einer Tabellenspalte eine Zusammenfügung aus Werten anderer Spalten aus vielleicht anderen Tabellen haben? Ist es Dir zu lästig, die Zusammenfügung in Deiner Query zu formulieren?

Liebe Grüße

Felix Riesterer

[Rolf B]

Hallo Felix,

mir schien, dass der Hauptgrund für den Trigger das Einsteuern des Update-Datums war. Dementsprechend ist meine Antwort formuliert. Aber da kann ich natürlich völlig schief liegen.

Rolf

[Rolf B]

Hallo der henry,

ich möchte das erste mal einen tripper in einer mysql Datenbank bzw. Tabelle anwenden.

🤣

Ich möchte einen Trigger in der Tabelle "datenpunktliste" bei Eingabe bzw. update (Änderung) auslösen. Dann soll der Wert aus dem Feld "datenpunktliste.spsname" in das Feld "datenpunktliste.spsvarname" geschrieben werden.

D.h. spsvarname ist ein synthetischer Wert, der sich aus spsname, varname und dem Datum der letzten Änderung zusammensetzt?

Aus DB-Modellierungssicht ist dazu zu sagen: Pfui. Mach ein drittes Feld spsvardate oder so, schreibe das Änderungsdatum hinein und setze den Wert von spsvarname bei Bedarf in der SQL Query oder in den Anwendung zusammen.

Um das Änderungsdatum automatisch hinzubekommen, kannst Du für spsvardate einen Defaultvalue festlegen.

create table datenpunktliste
   ...
   spsvardate DATE NOT NULL DEFAULT CURDATE()
   ...

Damit ist die Vorbelegung beim INSERT erledigt. Nun stellt sich die Frage, was bei Updates von istwert oder datentyp ist. Soll sich das Datum dann auch ändern? Oder nur bei Updates auf spsname und varname? Wenn jeglicher Update das Datum ändern darf, kannst Du das der Default-Definition hinzufügen - musst dann aber auf DATETIME wechseln:

   spsvardate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()

D.h. du musst dann bei jedem Zugriff auf spsvardate mit der DATE()-Funktion den Zeitanteil aus spsvardate ausmaskieren.

Wenn nur Updates auf spsname und varname das Datum ändern sollen, kannst Du beim Date-Typ bleiben, brauchst aber einen Update-Trigger. Darin verwendest Du die Pseudo-Aliase OLD und NEW, um den Wert der Felder vor und nach dem Update zu vergleichen:

DELIMITER $$

CREATE TRIGGER `trigg_create_spsvarname`
   AFTER UPDATE ON `datenpunktliste`
   FOR EACH ROW
   BEGIN
      IF (OLD.spsname <> NEW.spsname) OR (OLD.varname <> NEW.varname) THEN
         SET NEW.spsvardate = CURDATE();
      END IF;
   END$$

DELIMITER ;

Die Akrobatik mit dem Delimiter ist nötig, weil andernfalls die Semikolons in der Trigger-Definition den Create-Befehl zu früh beenden.

Beachte: Wenn spsname oder varname eine Collation verwenden, die auf _ci endet (case insensitive) enthält, dann löst das Ändern des Inhaltes von - sagen wir mal - "FOO" zu "Foo" den Trigger NICHT aus. Dafür benötigt die Column eine _bin Collation (utf8_general_cs gibt's nicht?!?!).

Wenn Du das Zusammensetzen von spsname, spsvardate und varname in den Queries nicht hinschreiben willst, dann füge der Table eine GENERATED VIRTUAL COLUMN hinzu.

create table datenpunktliste
   ...
   spsvarname VARCHAR(99) AS CONCAT(spsname,spsvardate ,varname)
   ...

spsvardate konvertiert sich automatisch in JJJJ-MM-TT. Es sei denn, es ist ein Datetime-Datentyp, dann musst Du DATE(spsvardate) verwenden. Als Länge im varchar gib die Summe der Längen von spsname und varname plus 10 (für das Datum) an.

Den synthetisierten Wert per Trigger in die Table schreiben: Nein, nicht tun. Nur unter Androhung von sadistischer Folter. Wenn es um Performance beim Zugriff geht: mach eine materialisierte Spalte draus. Die belegt dann zwar Speicher, aber dafür kann man auch einen Index drauflegen.

create table datenpunktliste
   ...
   spsvarname VARCHAR(...) AS CONCAT(spsname,spsvardate,varname) STORED
   ...

Soweit ich weiß, funktioniert das alles in MYSQL und MariaDB. Nur das Einbringen von ALTER auf die Tabellen mit HeidiSQL war ein Drama, das generierte mir bei generierten Spalten einen SQL Syntaxfehler nach dem anderen...

Rolf

[MudGuard]

Hi,

Anbei mein Code:

CREATE DEFINER=`root`@`%`
   TRIGGER `trigg_create_spsvarname`
   AFTER INSERT ON `datenpunktliste`
   FOR EACH ROW 
      INSERT INTO datenpunktliste(spsvarname)
      SELECT datenpunktliste(spsname)

Das würde, wenn ich das richtig sehe, bei jedem Insert in die Tabelle datenbankliste einen weiteren insert in die Tabelle datenbankliste erzeugen.

Letzterer könnte schiefgehen, wenn es nicht-null-Spalten ohne Default-Wert gibt …

Ich bin mir grad nicht sicher, ob dieser neue Insert, wenn er nicht scheitert, nicht auch wieder den Trigger auslösen würde,
womit ein weiterer Insert geschieht, der wieder den Trigger auslöst,
womit ein weiterer Insert geschieht, der wieder den Trigger auslöst,
womit ein weiterer Insert geschieht, der wieder den Trigger auslöst,
womit ein weiterer Insert geschieht, der wieder den Trigger auslöst,
womit ein weiterer Insert geschieht, der wieder den Trigger auslöst,
…

cu,
Andreas a/k/a MudGuard