Trigger erstellen - Hilfe

[FelixDatenbank]

Hallo liebe Mitglieder,

ich bin im Moment dabei, ein Datenmodell mit PostgreSQL zu erstellen.
Dieses wird in Verbindung mit Python und einer Benutzeroberfläche erstellt.
Mein Anliegen:
Als Bsp.: der Benutzer gibt einen Lehrernamen an, der in der Tabelle Lehrer mit einer ID, etc. integiert wird.
Außerdem gibt es eine Tabelle mit bestimmten Unterrichtsfächern.
Zwischen diesen Tabellen gibt es die Tabelle Präferenzen. Dort wird hinterlegt, welche Fächer ein Lehrer unterrichten möchte.
Meine Idee: ich möchte mit einem Trigger die lehrer_id in der praeferenz_id hinterlegen, und gleichzeitig aus der dazugehörigen unterrichtsfächer_id die Fächer, die der Lehrer präferiert.
Die Präferenzen gibt der Benutzer auch an.
Das Problem ist, dass wenn ich ein Trigger für den Lehrer anwende, kommt der Error, dass in der Praeferenztabelle die unterrichtsfächer_id not null sein darf.

Wie kann ich dieses Problem beheben?

 

[akretschmer]

Zwischen diesen Tabellen gibt es die Tabelle Präferenzen. Dort wird hinterlegt, welche Fächer ein Lehrer unterrichten möchte.
Meine Idee: ich möchte mit einem Trigger die lehrer_id in der praeferenz_id hinterlegen, und gleichzeitig aus der dazugehörigen unterrichtsfächer_id die Fächer, die der Lehrer präferiert.

Möglicherweise verstehe ich nicht vollständig,was Du exakt erreichen willst, aber ich denke, ein TRIGGER ist dazu der falsche Ansatz. Wenn bekannt ist, was wo genau wie einzutragen ist (dies scheint hier der Fall zu sein), dann sind TRIGGER eher nicht nötig.

Eine TRIGGER-Anwendumng, als Beispiel, wäre eher z.B. in einer Lagerhaltung nötig, wenn droht, daß ein Artikel unter Sollmenge geht und dann nicht mehr lieferbar ist - der TRIGGER könnte dann eine Nachbestellung auslösen. Diese Notwendigkeit ist aber anhand der konkreten Buchung nicht erkennbar.

Das, was Du willst (vermutlich) ist die Vereinfachung der einzelnen Statements, ist die Reduzierung der einzelnen Gänge zur Datenbank. Das geht mittels wCTE, mal als Beispiel einer Master->Detail - Tabelle:

create table master (id serial primary key, name text);
create table detail (master_id int references master, tel_nummer text);

with new_master as (insert into master (name) values ('Andreas') returning id),
     new_tel    as (select('123') union all select ('345') union all select ('789'))
insert into detail select * from new_master cross join new_tel;

select * from master;
select * from detail;

probiere das mal nach, vielleicht verstest Du, was ich damit meine und vielleicht löst dies auch schon das Problem, was Du hast.

viel Erfolg!

 

[FelixDatenbank]

Leider konnte ich dieses nicht auf mein Programm anwenden.
Ich habe mal ein wenig weiter probiert, stoße jetzt aber darauf, dass ich eigentlich eine INSERT INTO WHERE brauche. gibt es soetwas?
Bsp.: ich legen einen Lehrer in Praeferenzen an und die fächer_id ist leer. sobald ich die INSERT INTO praeferenz VALUES ( ,1) bräuchte ich dann eine Anweisung, dass die 1 (fächer_id) dem Lehrer zugeordnet wird.
Bsp.: WHERE lehrer_id = 2;

 

[akretschmer]

Insert mit Where-Condition? Nein, gibt es nicht. Auf das soll denn das Where sich beziehen?

Ich denke aber Dein Problem zu erkennen, und denke auch, mein erstes Beispiel trifft das schon ganz gut. Hier noch mal etwas näher an Dein Problem angepaßt.

test=*# create table lehrer (id serial primary key, name text);
CREATE TABLE
test=*# create table faecher (id serial primary key, fach text);
CREATE TABLE
test=*# create table lehrer_fach (id_lehrer int references lehrer, id_fach int references faecher, primary key(id_lehrer, id_fach));
CREATE TABLE
test=*# insert into faecher (fach) values ('mathe');
INSERT 0 1
test=*# insert into faecher (fach) values ('sport');
INSERT 0 1
test=*# insert into faecher (fach) values ('geo');
INSERT 0 1
test=*# insert into faecher (fach) values ('geschichte');
INSERT 0 1
test=*# insert into faecher (fach) values ('computer');
INSERT 0 1
test=*# insert into faecher (fach) values ('deutsch');
INSERT 0 1
test=*# insert into faecher (fach) values ('bio');
INSERT 0 1
test=*# select * from faecher;
 id |    fach   
----+------------
  1 | mathe
  2 | sport
  3 | geo
  4 | geschichte
  5 | computer
  6 | deutsch
  7 | bio
(7 Zeilen)

Nun soll Herr mueller eingetragen werden, mit seinen Präferenzen zu deutsch, bio und sport:

test=*# with l_id as (insert into lehrer (name) values ('mueller') returning id), f_id as (select id from faecher where fach in ('deutsch','bio','sport')) insert into lehrer_fach select * from l_id cross join (select * from f_id) f;
INSERT 0 3
test=*# select * from lehrer_fach ;
 id_lehrer | id_fach
-----------+---------
         1 |       2
         1 |       6
         1 |       7
(3 Zeilen)

test=*#

Mit dieser Technik (writeable Common Table Expression) kannst Du gleichzeitig in mehrere Tabellen (hier 2) Werte eintragen, dabei bei der zweiten Tabelle unter Verwendung einer beim Eintragen in die erste Tabelle generierten SERIAL. Spart ein oder mehrere Gänge zur Datenbank (praktisch wenn diese z.B. remote weit entfernt ist und daher Latenzen ein Problem sind UND man sich kein BDR Version 3 leisten kann ...)


So, und nun: select Kaffee!

 

[FelixDatenbank]

Das hat mir sehr geholfen vielen Dank.
Eine Frage hätte ich noch dazu. Wenn ich bspw. den Lehrer wieder entfernen möchte, sollen auch die Praeferenzen entfernt werden. Wie kann ich das machen? Da habe ich auch schon unterschiedliche Dinge ausprobiert

 

[FelixDatenbank]

Eine weitere Frage die sich mir stellt ist die folgende:
Wie kann ich in einem Trigger eine Abfrage (if?) machen, ob das hinzugefügte (INSERT) bereits vorhanden ist?

 

[akretschmer]

Eine Frage hätte ich noch dazu. Wenn ich bspw. den Lehrer wieder entfernen möchte, sollen auch die Praeferenzen entfernt werden. Wie kann ich das machen? Da habe ich auch schon unterschiedliche Dinge ausprobiert

Du erstellst den Foreign Key mit ON DELETE CASCADE:

test=*# create table lehrer_fach (id_lehrer int references lehrer on delete cascade, id_fach int references faecher on delete cascade, primary key(id_lehrer, id_fach));
CREATE TABLE
test=*# \d lehrer_fach;
                   Tabelle »public.lehrer_fach«
  Spalte   |   Typ   | Sortierfolge | NULL erlaubt? | Vorgabewert
-----------+---------+--------------+---------------+-------------
 id_lehrer | integer |              | not null      |
 id_fach   | integer |              | not null      |
Indexe:
    "lehrer_fach_pkey" PRIMARY KEY, btree (id_lehrer, id_fach)
Fremdschlüssel-Constraints:
    "lehrer_fach_id_fach_fkey" FOREIGN KEY (id_fach) REFERENCES faecher(id) ON DELETE CASCADE
    "lehrer_fach_id_lehrer_fkey" FOREIGN KEY (id_lehrer) REFERENCES lehrer(id) ON DELETE CASCADE

Wenn Du jetzt einen Lehrer löscht zieht sich das runter in die lehrer_fach - Tabelle.

 

[akretschmer]

Wie kann ich in einem Trigger eine Abfrage (if?) machen, ob das hinzugefügte (INSERT) bereits vorhanden ist?

Das klingt eher nach ON CONFLICT ...

test=*# create table demo(id int primary key, val text);
CREATE TABLE
test=*# insert into demo values (1, 'test 1') on conflict (id) do update set val = excluded.val;
INSERT 0 1
test=*# select * from demo;
 id |  val   
----+--------
  1 | test 1
(1 Zeile)

test=*# insert into demo values (1, 'test 2') on conflict (id) do update set val = excluded.val;
INSERT 0 1
test=*# select * from demo;
 id |  val   
----+--------
  1 | test 2
(1 Zeile)

test=*#

 

[FelixDatenbank]

Wofür steht das btree,
soweit funktioniert das alles. Aber ich habe einen Trigger auf die Lehrertabelle (v_lehrer). Dort steht drin, dass sobald ein lehrer geschlöscht, hinzugefügt oder geändert wird, dies dort hinterlegt wird.
Da ist der einzige Fehler, der aufkommt.

 

[akretschmer]

BTREE ist eine Indexart, so wie auch HASH, GIN, GiST, BRIN, BLOOM, RUM, VODKA und mehr.

Deine anderen Fragen versteh ich nicht.

 

[akretschmer]

wenn du deine Texte im Nachgang komplett änderst wird es unnötig schwer, dir weiter zu helfen. Und mit

"ich habe einen Trigger auf die Lehrertabelle (v_lehrer). Dort steht drin, dass sobald ein lehrer geschlöscht, hinzugefügt oder geändert wird, dies dort hinterlegt wird.
Da ist der einzige Fehler, der aufkommt."

kann ich leider nicht viel anfangen.