Trigger soll mehrere Datensätze einfügen, doch inserted er nur einen

[script42]

Hallo!

Ich habe zwei Tabellen: tbl_Familienfotozeiten (id, auftragsnr, datum, von, bis) und tbl_Familienfotos (id, ffz_id, zeit, familienname).
Wenn in die erste ein Datensatz eingefügt wird (AFTER INSERT), soll in der zweiten für jede 5-Minuten-Einheit zwischen von und bis ein Datensatz eingefügt werden. (Also z.B. von: 10:00, bis: 12:00, dann müssten in der Tabelle tbl_Familienfotos 24 Zeilen(Timeslots) erstellt werden mit den Werten: 10:00, 10:05, 10:10, ...11:55 in der Spalte Zeit.)
Dafür habe ich den folgenden Trigger erstellt:

DROP TRIGGER IF EXISTS slots_familienfotos;

DELIMITER $$

CREATE TRIGGER slots_familienfotos
AFTER INSERT
ON tbl_Familienfotozeiten FOR EACH ROW
BEGIN
SET @von = new.von;
WHILE @von < new.bis DO
INSERT INTO tbl_Familienfotos (ffz_id, zeit, familienname)
VALUES(new.id, @von, NULL);
SET @von = DATE_ADD(@von, INTERVAL 5 minute);
END WHILE;
END$$

DELIMITER ;

Doch inserted er mir leider nur jeweils eine Zeile (in diesem Fall diejenige mit dem Wert 10:00) statt mehrere.
Kann mir jemand sagen, wo mein Fehler liegt?
(Die Spalten von, bis und zeit sind übrigens TIME-Spalten, falls das etwas weiterhilft.)

Herzliche Grüße
Kerstin

 

[akretschmer]

Um Deine Frage zu beantworten: Erstelle alle Rows, die da zutrefend sind. Das ist dann massiv redundant. Also, das ist berechenbar, z.B. via Lateral Join.

 

[script42]

Nun ja, da sollen ja am Ende in jeder Zeile unterschiedliche Namen drinstehen. Dann ist es nicht mehr redundant.
Meine Frage ist, warum der Trigger mir nur eine Zeile einträgt, statt multipler.

 

[dabadepdu]

Ich habe keine Ahnung von mySQL Programmierung. Die Whileschleife hat ja ein Abbruchkriterium und daran könnte es liegen.
Was steht in bis?

Ansonsten, auch wenn der Ton von akretschmer peinlich und unsachlich ist, sowas macht man nicht, nicht so, nicht ohne Not und überhaupt: Warum?
Man will keine While Schleifen die eigene SQL Aktivitäten entfalten. Noch weniger will man das ein insert 24 weitere inserts produziert, immer, gnadenlos.
Und auch nicht auf so undurchsichtigem Weg (Trigger).

Man schreibt 24 Datensätze, die berechnete Werte enthalten am besten gar nicht. Wenn doch, dann mit einem(!) Statement, das 24 Datensätze "am Stück" erzeugt.
Das Datenmodel, was bei dem Insert durchschimmert ist auch sagen wir mal ungewöhnlich, nur mit FK (new.id) und dann scheinbar die Zeitangaben zur Identifikation eines Datensatzes..

Du kannst ja mal erklären, warum es so sein soll.

 

[akretschmer]

Nun ja, da sollen ja am Ende in jeder Zeile unterschiedliche Namen drinstehen. Dann ist es nicht mehr redundant

Nach Deiner Beschreibung nur unerschiedliche Zeiten, Das ist immer noch massiv redundant - und dumm.

Tut mir leid, und kein Mitleid.

Ähm. Dein TRIGGER feuert vermutlich beim Eintragen des ersten Datensatzes. Er ist doof genug nicht zu wissen, wann das Spiel ein Ende hat. Daher auch nur ein Eintrag. Und noch einmal, weil es grad so viel Spaß macht: doofe Idee.


Andreas

 

[script42]

Man schreibt 24 Datensätze, die berechnete Werte enthalten am besten gar nicht. Wenn doch, dann mit einem(!) Statement, das 24 Datensätze "am Stück" erzeugt.

Super! Das wäre auch okay! Wie macht man das?

 

[akretschmer]

Wie macht man das?

Gar nicht, weil berechenbar aus der Ursprungstabelle. Hab ich heute in einem ähnlichen Thread gezeigt.

 

[akretschmer]

Auswertung mit Abgrenzung über Datum

 

[script42]

Okay, sagen wir mal, es macht keinen Sinn, das so zu machen.
Trotzdem möchte ich nach wie vor wissen, wo der Programmierfehler im Trigger ist, um daraus für zukünftige While-Schleifen oder Trigger-Programmierung lernen zu können.
Daher stelle ich meine ursprüngliche Frage noch einmal, da ich ja leider "doof genug bin, um nicht zu wissen, wann das Spiel zu Ende ist".
Warum läuft der Trigger nicht so, wie er soll?
Vielleicht findet sich ja jemand, der das beantworten kann und möchte, ohne zwanghaft Beleidigungen raushauen zu müssen...
(Meine Dich nicht, dabadepdu, danke für Deine Antwort)
Eure Kerstin

 

[akretschmer]

Kurzdemo:

edb=> create table t1(id int primary key, von int, bis int);
CREATE TABLE
edb=*> create table t2 (id int, val int);
CREATE TABLE
edb=*> create or replace function f1() returns trigger as $$begin insert into t2 select new.id, s from generate_series(new.von, new.bis) s(s); return new; end; $$language plpgsql;
CREATE FUNCTION
edb=*> create trigger trg1 after insert on t1 for each row execute procedure f1();
CREATE TRIGGER
edb=*> select * from t1;
 id | von | bis
----+-----+-----
(0 rows)

edb=*> select * from t2;
 id | val
----+-----
(0 rows)

edb=*> insert into t1 values (1,5,8);
INSERT 0 1
edb=*> select * from t1;
 id | von | bis
----+-----+-----
  1 |   5 |   8
(1 row)

edb=*> select * from t2;
 id | val
----+-----
  1 |   5
  1 |   6
  1 |   7
  1 |   8
(4 rows)

edb=*>

Mit generate_series() kannst Du auch deine 5-Minuten-Intervalle generieren - wenn MySQL diese Funktion hätte.

Den Trigger-Aufwand und den Platz in der zweiten Tabelle kann man sich natürlich sparen:

edb=*> select * from t1 left join lateral (select * from generate_series(t1.von, t1.bis)) x(s) on (true);
 id | von | bis | s
----+-----+-----+---
  1 |   5 |   8 | 5
  1 |   5 |   8 | 6
  1 |   5 |   8 | 7
  1 |   5 |   8 | 8
(4 rows)

edb=*>

MySQL hat weder generate_series() noch LATERAL JOIN.

 

[script42]

MySQL hat weder generate_series() noch LATERAL JOIN.

Ja, das ist schade, deshalb hätte ich es gerne mit dem Trigger gemacht.

Der Sinn des Ganzen liegt hauptsächlich in einem deutlich einfacheren Code in PHP und Javascript, wenn ich die Tabelle vorerstelle, bevor die Familiennamen dann über's Frontend eingetragen werden.
Das Verfahren ist so:
Der Fotograf legt ein oder mehrere Tage und Zeiten für Familienfotos in der Tabelle tbl_Familienfotozeiten fest.
Also z.B.
id | auftragsnr | datum | von | bis
123 | 365 | 2021-12-12 | 8:00 | 9:00
124 | 365 | 2021-12-13 | 9:00 | 9:30
Dann sollen in einer separaten Tabelle für jede der IDs (also hier 123 und 124) eine Liste von Terminslots erstellt werden, in die sich die Familien dann eintragen.
Die Tabelle soll nach Einsatz des Triggers für die erste ID (123) so aussehen:
id | ffz_id | zeit | familienname
1 | 123 | 8:00| NULL
2 | 123 | 8:05| NULL
3 | 123 | 8:10| NULL
...
12 | 123 | 8:55| NULL
So habe ich die Möglichkeit, über eine sehr einfache Abfrage die Tabelle im Frontend anzeigen zu lassen und auch nicht komplizierter zu werden, wenn sich Stück für Stück die Leute eintragen.
Denn ich muss einfach nur jeweils die Slots zur Anzeige bringen, wo der Familienname noch NULL ist, sich also noch keiner eingetragen hat.
Habe ich keine vorerstellte Tabelle, bekomme ich natürlich nur Datensätze für die Zeiten, wo sich bereits Leute eingetragen haben.
Dadurch natürlich keine Redundanz, da stimme ich zu, aber dafür muss ich jetzt jedesmal über PHP oder über's Frontend die Liste der Slots berechnen lassen, diese dann mit den schon belegten in der Datenbank vergleichen, entsprechend ausfiltern und erst dann das Ergebnis anzeigen lassen.
Diese Neuberechnung bei jedem Seitenaufruf - bzw noch öfter, da gleichzeitige Veränderungen ausgeschlossen werden müssen - kann ich mir sparen, wenn ich es wie oben beschrieben, anlege.
In der Datenbank wird es ein einziges Mal berechnet und das war's.
Die Redundanzen machen sich auch kaum bemerkbar, da der Eintragungsprozess der Familien nur über ein paar Tage läuft und die Datensätze dann ohnehin wieder gelöscht werden, da nicht langfristig benötigt.
Diese kurze Belastung der Datenbank mit ein paar Kilobyte mehr können wir also verkraften.

Hat jemand bitte eine Antwort für mich, wie ich den Trigger zum Laufen bekommen kann?
Herzliche Grüße
Kerstin

 

[akretschmer]

ich kann an deinem Code nix prinzipiell falsches finden, habe aber auch von MySQL-Programmierung keine Ahnung. Stimmen die Datentypen? Tabellenaufbau? Vielleicht ist ja die ffz_id ein PK ...

 

[script42]

Hallo akretschmer,
ja, ich finde auch nichts. Tabellenaufbau und Datentypen passen, wenn ich auch die Time-Spalten im Verdacht habe, da sie sich ja anders als der übliche Timestamp verhalten, dass da der Hase im Pfeffer liegen könnte.
Werde jetzt aber auch nicht weiter einsteigen, wenn das selbst für Euch Profis nicht direkt ersichtlich ist, auch wenn Deine Hauptdomäne woanders liegt.
Habe es stattdessen mit PHP gelöst, erzeuge mir diese Slots einmalig dort und kann dann wie gewünscht abfragen. Passt in den Workflow genausogut wie der Trigger und ist nicht so verdeckt wie dieser, was Du ja auch zu Recht beanstandet hast.
Danke auf jeden Fall für's Durchsehen und die Tipps!
Herzliche Grüße
Kerstin