m:n Beziehung - "Zwischentabelle" automatisch mit Datensätzen füllen

[PerryAtlan]

Moin,
hat mysql selbst eine Funktion, um Datensätze in der "Zwischentabelle" (gibt es da eigentlich einen Fachbgriff?) automatisch zu generieren oder geht das nur via zusätzlicher externer Programmierung (z.B. "Entity Framework")

Ich benötige zu jedem Datensatz in tabelle "m" -also zu jedem m - in der "Zwischentabelle" n Datensätze.

Also zu jedem neu angelegtem m sollen n Datensätze angelegt werden bzw. zu jedem neu angelegtem n müssen m Datensätze neu angelegt werden. - es müsste also sowas wie ON INSERT CASCADE geben - ON UPDATE CASCADE behandelt soweit ich das sehe nur bereits vorhandene Datensätze.

Wird ein Datensatz in m oder n gelöscht, müssen die dazugehörigen Datensätze in der Zwischentabelle ebenfalls gelöscht werden - das wäre "ON DELETE CASCADE"

ciao
Lothar

P.S. ich bin Autodidakt was Datenbanken angeht.

[Ralf Jansen]

Woher sollte ein potentieller INSERT CASCADE wissen zu welchem Datensatz in der jeweils anderen beteiligten Tabelle er ein Mapping in der Mapping Tabelle herstellen soll? Die Datenbank kann nur mit den Daten arbeiten die sie schon weiß und diese Info hat sie nicht.
Man könnte vielleicht irgendwas mit einem View über die 3 Tabellen anstellen und einem InsteadOf Trigger. Der wird aber voraussichtlich so komplex (die Anwendung die den View benutzt auch) das du das lieber sauber vor der DB in der Anwendung machen solltest.

[PerryAtlan]

Das war kurz und knapp, danke für die Info.
Ich hatte gehofft, daß die DB-Engine selbst dafür eine Funktion bereitstellt.
Dann muss ich das halt auf Anwendungesebene realisieren - die Mapping Datensätze sind dann halt für einen kleinen Moment nicht aktuell (weil unvollständig).

[Ralf Jansen]

Dann muss ich das halt auf Anwendungesebene realisieren - die Mapping Datensätze sind dann halt für einen kleinen Moment nicht aktuell (weil unvollständig).

Das ganze läuft doch wohl transaktional oder? Das sollte also niemanden stören außer du läßt Dirty Reads zu.

[akretschmer]

Das war kurz und knapp, danke für die Info.
Ich hatte gehofft, daß die DB-Engine selbst dafür eine Funktion bereitstellt.
Dann muss ich das halt auf Anwendungesebene realisieren - die Mapping Datensätze sind dann halt für einen kleinen Moment nicht aktuell (weil unvollständig).

Transaktionen wurden schon erfunden. Zum Beispiel dafür.

[defo]

eins noch:
Für solche Zwecke werden gern StoredProcedures verwendet (Pseudo Code):

procedure InsertMandN(pmValue, pnValue);
begin
Insert into mTable (mValue) values(pmValue) [returning mID];
Insert into nTable (nValue) values(pnValue) [returning nID];
Insert into nmTable (IDM, IDN) values (mID,nID);
[exception handling]
end;

Das wird serverseitig garantiert in >einer< Transaktion verarbeitet (oder gar nicht, bei Fehler), die (Transaktion) muss man nicht mal im Client steuern.
Es erlaubt allein schon über entsprechende Rechtevergabe, dass keine n oder m Leichen entstehen.
Es entspricht häufig sogar der Darstellung im Client, der oftmals die Daten als Join über n, n:m, m darstellt.

Mit 2 zusätzlichen Prozeduren für reine n oder m Inserts hat man alles fürs Insert.
Zusätzlich kann man über ähnliche oder schlauere Prozeduren auch Updates und Deletes regeln.

[PerryAtlan]

Was heisst das jetzt?
Es ist ja nicht möglich in Views die über 2 oder mehr Tabellen gehen neue Datensätze anzulegen.

Also lege ich manuell den neuen Datensatz in einer Parent-Tabelle an, mit einem Steuerfeld "Datensatz_neu =1.
Unsere externe Software (zur Zeit Envox) selektiert dann diesen Datensatz, holt sich die entsprechenden PrimärIndexe aus der andern Parent-Table und legt dann die notwendigen Datensätze ind der Mapping Table an.
Erst dann ist die Transaktion vollständig (Datensatz_Neu wird ebenfalls von Envox auf 0 gesetzt)
zwisen der Datensatzeingabe und der automatischen Vervollständigung leigt dann halt eine kleine Zeitspanne, weil ein kompletter Durchlauf des gesamten komplexen Durchlaufs halt auch mal 'ne Minute dauern kann

Schön wäre es natürlich, wenn diese Transaktion direkt auf dem MySQL-Server definiert werden könnte (Procedures, Functions, UDFs?)

Zur Zeit läuft MySQL als Backend, Access 2003 als Frontend (ein paar wenige Macros sind auch in VBA vorhanden) und Envox übernimmt die Automatisierung des ganzen :-)

Ich bin zur Zeit dabei eine vorhandene Datenbank neu zu strukturieren und zu vereinfachen. Dabei setze ich auf grösstmögliche "Atomisierung" der Daten, um die Datenpflege selbst zu vereinfachen. Daraus folgt die angesprochene Frage zu den Mapping Tables (trifft natürlich zum Teil auch auf einfache 1:n Beziehungen zu)
Wie schon gesagt, bislang arbeiten wir nur mit den Tabellen direkt (bei kleineren überschaubaren Tabellen), Abfragen oder auch Formularen in Access. Die gesamte Transaktionssteuerung und auch Datenverarbeitung als solches übernimmt bei uns ein Envox Script (bzw. einige VBA Macros).
Envox und Acess soll wahrscheinlich durch Visaual Basic ersetzt werden, das ist aber noch nicht spruchreif - auf der eine Seite wären die hohen Lizenzkosten für Envox, auf der anderen Seite die Entwicklungskosten für Visaual Basic, wobei letzteres dann möglicherweise Zukunftssicherer wäre.

Edith:
@defo
Danke für den "Fingerzeig" in welche Richtung ich schauen muss!

[PerryAtlan]

Ganz nach dem Motto "Es gibt keine Doofen Fragen" .... :-)

würde so ein Trigger Funktionieren?

CREATE TRIGGER Test AFTER INSERT ON Tabelle_A
    -> FOR EACH ROW
    -> BEGIN
    ->     INSERT INTO tabelle_AB SET tabelle_AB.primarykey_A = NEW.primarykey_A , tabelle_AB.primarykey_B = SELECT primarykey_B FROM tabelle_B
    -> END;//

After INSERT, da ich in Tabelle_AB auf primarykey_A & primarykey_B jeweils einen Fremdschlüssel definiert habe (mit ON UPDATE RESTRICT).


... tabelle_AB.primarykey_A = NEW.primarykey_A.... >> der Wert aus der Triggerauslkösenden Zeile in Tabelle A (wenn ich das Richtig verstanden habe)
... tabelle_AB.primarykey_B = SELECT primarykey_B FROM tabelle_B >> für jeden Datensatz aus Tabelle_B wird ein Datensatz in Tabelle_AB eingefügt

ist das so kombinierbar?

Nach ein wenig weiterlesen im Netz, dürfte obige Kombi nicht gehen, aber diese hier sollte theoretisch funzen,

CREATE TRIGGER Test AFTER UPDATE ON Tabelle_A
    -> FOR EACH ROW
    -> BEGIN
    -> SELECT  INTO tabelle_AB (primarykey_A, primarykey_B)  SELECT primarykey_B, NEW.primarykey_A FROM tabelle_B
    -> END;

Realer Code:

CREATE TRIGGER `tbl_konditionsart_after_ins_tr` AFTER INSERT ON `tbl_konditionsart`
  FOR EACH ROW
BEGIN
INSERT INTO tbl_katgueltiginkoart (KondiArt_ID, Kategorie_ID) SELECT Kategorie_ID, NEW.KondiArt_ID FROM tbl_fahrzeugkategorien
END;

Aber sowohl "EMS SQL Manager 2005 Lite" als auch "DB Manager" von DBTootls melden:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

Line 5 ist "END;"
ich kann da weder einen Fehler, noch Hochkommata oder Anführungszeichen entdecken

Auch direkt auf der MySQL Kommandozeile gibt es den Fehler (mit und ohne Delimiter).
Der MySQL_Server ist 5.1.42 auf Windows 2003 - gibt es vielleicht mit dieser Version Probleme bei der Triggererstellung? (Never change a running system, deswegen läuft da noch 'ne relativ alte Version)

mysql> CREATE TRIGGER `tbl_konditionsart_after_ins_tr` AFTER INSERT ON `tbl_kond
itionsart`
-> FOR EACH ROW
-> Begin
-> INSERT INTO tbl_katgueltiginkoart (KondiArt_ID, Kategorie_ID) SELECT Kate
gorie_ID, NEW.KondiArt_ID FROM tbl_fahrzeugkategorien
-> end;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 5
mysql>

mysql> Delimiter |
mysql> CREATE TRIGGER `tbl_konditionsart_after_ins_tr` AFTER INSERT ON `tbl_kon
itionsart`
-> FOR EACH ROW
-> Begin
-> INSERT INTO tbl_katgueltiginkoart (KondiArt_ID, Kategorie_ID) SELECT Kat
gorie_ID, NEW.KondiArt_ID FROM tbl_fahrzeugkategorien
-> end;
-> |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 5
mysql>

P.S. für heute ist bei mir Feierabend

[Falk Prüfer]

Hallo,

so auf die Schnelle würde ich sagen hinter "...FROM tbl_fahrzeugkategorien" fehlt ein Semikolon.

Gruß Falk

[defo]

Ich hab's mir im Detail nicht angesehen, aber:
- die Anführungszeichen (keine normalen?) bei Trigger und Tablename scheinen mir verdächtig
- die Funktion (Trigger für n:m) finde ich fragwürdig. Trigger dürfen bei mir erstmal nur Primary Key ID produzieren. Ausnahmen gibt's nur in Sonderfällen und dann gehört dazu ein Warnschild am Firmeneingang. Wenn ein Trigger dann noch mit anderen Tabellen "kommuniziert", gehört das Warnschild schon an den Ortseingang.
Das ist alles nicht verboten oder böse, aber Trigger sind ungefähr das intransparenteste, was man auf der Datenbank machen kann.
- irgendwie fehlen mir bei den Each Row Triggern Where Bedingungen im Select

[PerryAtlan]

Moin,
@ Falk Prüfer
das scheint es gewesen zu sein - allerdings wohl auch nicht alleine.
Ich habe "Pro Forma" auch das Script "mysql_fix_privilege_tables" ausgeführt, da die Datenbank(en) Anfang 2010 auf neue Hardware umgezogen ist, und dabei auch gleich ein aktuellerer MySQL-Server verwendet wurde (vorher hatte wir einen 4er laufen).

mysql> Delimiter |
mysql> CREATE TRIGGER `tbl_konditionsart_after_ins_tr` AFTER INSERT ON `tbl_konditionsart`
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT INTO tbl_katgueltiginkoart (KondiArt_ID, Kategorie_ID) SELECT Kategorie_ID, NEW.KondiArt_ID FROM tbl_fahrzeugkategorien;
    -> END;
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql>

wobei

mysql> show triggers
    -> ;
    ->

wieder mal nichts ausgibt.
Wie komme ich bei sowas eigentlich wieder zurück auf den MySQL-Prompt? (ohne cmd mit STRG-C zu beenden)

Weder EMS noch DBManager zeigen zur Tabelle "tbl_konditionsart" einen Trigger an!

EMS zeigt in der Tabelle "information_schema.TRIGGERS" nichts an ( Auch ein "SELECT * FROM `information_schema`.`TRIGGERS`" im SQL Editor von EMS zeigt keine Datensatz)!

DBManager zeigt in der Tabelle "information_schema.TRIGGERS" nichts an!

"MySQL Query Browser 1.2.17" wiederum zeigt in der Tabelle "information_schema.TRIGGERS" einen Datensatz an (eigentlich genau meinen erstellten Trigger)!

CSV:

"TRIGGER_CATALOG","TRIGGER_SCHEMA","TRIGGER_NAME","EVENT_MANIPULATION","EVENT_OBJECT_CATALOG","EVENT_OBJECT_SCHEMA","EVENT_OBJECT_TABLE","ACTION_ORDER","ACTION_CONDITION","ACTION_STATEMENT","ACTION_ORIENTATION","ACTION_TIMING","ACTION_REFERENCE_OLD_TABLE","ACTION_REFERENCE_NEW_TABLE","ACTION_REFERENCE_OLD_ROW","ACTION_REFERENCE_NEW_ROW","CREATED","SQL_MODE","DEFINER","CHARACTER_SET_CLIENT","COLLATION_CONNECTION","DATABASE_COLLATION"
"","apl24-hh","tbl_konditionsart_after_ins_tr","INSERT","","apl24-hh","tbl_konditionsart",0,"","BEGIN
INSERT INTO tbl_katgueltiginkoart (KondiArt_ID, Kategorie_ID) SELECT Kategorie_ID, NEW.KondiArt_ID FROM tbl_fahrzeugkategorien;
END","ROW","AFTER","","","OLD","NEW","","STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION","root@localhost","latin1","latin1_swedish_ci","latin1_swedish_ci"

MySQL Query Browser 1.2.17" unterstützt aber scheinbar (noch) keine Trigger!

Worauf kann ich mich denn "verflixt und zugenäht nochmal" verlassen?

@Defo

- die Funktion (Trigger für n:m) finde ich fragwürdig. Trigger dürfen bei mir erstmal nur Primary Key ID produzieren. Ausnahmen gibt's nur in Sonderfällen und dann gehört dazu ein Warnschild am Firmeneingang. Wenn ein Trigger dann noch mit anderen Tabellen "kommuniziert", gehört das Warnschild schon an den Ortseingang.

- irgendwie fehlen mir bei den Each Row Triggern Where Bedingungen im Select

Im Insert fehlt noch das IGNORE Statement, da ich auf beide Keys einen gemeinsamen Unique Index gelegt habe.
Ansonsten führe ich nur Alle (!!) Kombinationsmöglichkeiten von 2 PKs aus 2 Tabellen zusammen, daß ist in diesem Fall genau so gewollt und erforderlich

- die Anführungszeichen (keine normalen?) bei Trigger und Tablename scheinen mir verdächtig

Das ist der DDL Code von EMS

Das ist alles nicht verboten oder böse, aber Trigger sind ungefähr das intransparenteste, was man auf der Datenbank machen kann.

Warum?
Die muss ich wenigstens nicht in einer Programmierung aufrufen sondern werden aktiv, wenn ich einen Datensatz anlege, und das ist doch genau das was ich hier benötige.
Eine Procedure oder Function muss ich aktiv aus einer Programmierung heraus aufrufen, zumindest nachdem was ich in den letzten Stunden gelesen habe, Das wäre eine Überlegung, wenn wir denn das Frontend und die automatische Datenverarbeitung in VB neu Programmieren, das mit einzubauen.

P.S.: Ich kann ja viel lernen, aber alles auf einmal schaffe ich nicht mehr.

P.P.S. in wie weit empfiehlt sich ein Udate auf die aktuelle Version von MySQL (5.6.12) ?

[PerryAtlan]

Da wird doch der Hund in der Pfanne verrückt!
ich weiss jetzt nicht, was zum Erfolg führte;
1. Von EMS SQL Manager Lite 3.6.5.8 auf EMS SQL Manager 4.5.0.9 gewechselt
2. Dem User Root nochmals explizit alle Rechte auf die Tabelle gegeben

Im neueren EMS wurde plötzlich der Trigger auf der Tabelle angezeigt (den ich ja vorhin über die MySQL Commandozeile angelegt hatte)
Paralell wird der Trigger im EMS SQL Manager Lite 3.6.5.8 immer noch nicht angzeigt.
Im neuen EMS konnt ich sogar den Trigger bearbeiten und erfolgreich speichern.

Ein erster Test ist aber in die Hose gegangen .-) ich muss mir die beteiligten Constraints und Fremdschlüssel nochmal anschauen. entweder ich habe irgendwo einen Denk- oder Vertändnisfehler, oder aber schiesse grob über's Ziel hinaus.

[PerryAtlan]

Scheinbar schiesse ich über's Ziel hinaus....
Der (einfachere) Trigger :

CREATE DEFINER = 'root'@'%' TRIGGER `tbl_konditionsart_after_ins_tr` AFTER
INSERT ON `tbl_konditionsart`
FOR EACH ROW BEGIN
INSERT IGNORE 
INTO tbl_konditiartsteuern (KondiArt_ID, Modell_ID)
SELECT Modell_ID,
       NEW.KondiArt_ID
FROM tbl_modelldaten
WHERE Marke_ID=NEW.Marke_ID; END;

beisst sich offenbar mit dem "Rückwärtsgerichtetem" Constraint (Foreign Key in Tabelle "tbl_konditiartsteuern"):

  CONSTRAINT `tbl_konditiartsteuern_fk` 
FOREIGN KEY (`KondiArt_ID`) 
REFERENCES`tbl_konditionsart` (`KondiArt_ID`),

Ich vermute daß der neue Datensatz in Tabelle "tbl_konditionsart" noch nicht wirklich für die Datenbank-Engine und somit für den Foreign Key noch nicht sichtbar ist, auch wenn im Trigger "After INSERT" steht >> somit läuft die Überprüfung auf den Foreign Key gegen die Wand.
werde das als nächstes austesten, indem ich den Constraint lösche.
Muss jetzt aber erstmal fast alle Timestamp Felder ändern, bei fast allen fehlt "on update current timestamp"


Edit: Nö!
man sollte beim Insert auf die Reihenfolge der Feldnamen und Values achten *Aua, Das tut ja schon weh*

[PerryAtlan]

Mal ein kleines Statement zum Schluss!
Das mit den Triggern funktioniert genauso wie ich es brauche / angedacht habe.
Ich musste da zwar ein klein wenig "spielen" um das alles richtig hinzubekommen und das tut was ich als Ziel habe, aber das waren letztendlich Kleinigkeiten.
Auch wenn Trigger vielleicht nicht die letzte Weisheit ist, komme ich erstmal damit klar.
Mit den "Stored Procedures" werde ich mich aber auch noch beschäftigen - ich "kratze" gerade an den Möglichkeiten.
Fakt ist, daß ich jetzt schon einige Transaktionen direkt auf der Datenbank fahren kann, die bislang in Envox programmiert wurden (über "Steuerfelder" auf die ein Select ausgeführt wurde und dann die entsprechende Transaktion ausgelöst hat.)
Ich bin gespannt wie ein Flitzebogen, welche Möglichkeiten sich hier noch weiter auftun
Gegebenenfalls werde ich mich mit konkreter Fragestellung in einem neuen Thread melden.
Die neue Datenbankstruktur ist jetzt im ersten Entwurf bis auf eine Tabelle fertig.
Diese Tabelle haben wir bewusst noch ausgeklammert, weil hier noch einige Fragen zu klären sind!

Zur Zeit Dokumentiere ich das ganze.
Ich bedanke mich nochmal ganz herzlich für die Denkanstösse in die richtige Richtung.
Dieser Fred kann geschlossen werden.