UPDATE und DELETE Triggerfunktion

Hallo, ich habe mal wieder eine Frage. Es geht darum das ich für eine bestimmte View einen UPDATE und DELETE Trigger benötige. Ich habe zur Zeit ein bisschen rumprobiert, kam aber noch zu keinem Ergebnis.

Es handelt sich um folgende Tabellen:

/* Tabelle Fachbereich anlegen */CREATE TABLE Fachbereich(
FBID serial PRIMARY KEY,
Fachbereich varchar(50)
);


/* Tabelle Fach anlegen */
CREATE TABLE Fach(
FachID serial PRIMARY KEY,
Fach varchar(100),
Studienrichtung varchar(20),
FBID integer REFERENCES Fachbereich(FBID)
);


/* Tabelle Versuche anlegen */
CREATE TABLE Versuche(
VersuchID serial PRIMARY KEY,
Versuch varchar(100),
sichtbarVersuch boolean,
FachID integer REFERENCES Fach(FachID)
);

Daraus habe ich dann folgende View gemacht:

CREATE VIEW prak_fach_fba AS select f.FachID, f.Fach, f.Studienrichtung, fb.Fachbereich From fach f INNER JOIN fachbereich fb ON f.FBId = fb.FBId WHERE fb.Fachbereich = 'Architektur';

Meine Triggerfunktion und der Trigger für UPDATE und DELETE sieht folgendermaßen aus:

CREATE OR REPLACE FUNCTION function_fach_fba() 
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $function$
   BEGIN
	IF TG_OP = 'UPDATE' THEN
		UPDATE fach SET fachID = NEW.fachID, fach = NEW.fach, studienrichtung = NEW.studienrichtung, fbid = NEW.fbid WHERE fachID = old.fachID; 
		UPDATE fachbereich SET fachbereich = NEW.fachbereich WHERE fbid = OLD.fbid;
		RETURN new; 
	ELSIF TG_OP = 'DELETE' THEN
		DELETE FROM fach WHERE fachid = OLD.fachID;
		RETURN null;
	END IF;
	RETURN new; 
   END;
$function$;




create trigger trg_insert 
instead of update or delete on prak_fach_fba 
for each row execute procedure 
function_fach_fba();

Die Fehlermeldung beim DELETE ist mir noch klar, das ist nämlich folgende:

FEHLER: Aktualisieren oder Löschen in Tabelle »fach« verletzt Fremdschlüssel-Constraint »versuche_fachid_fkey« von Tabelle »versuche«
DETAIL: Auf Schlüssel (fachid)=(21) wird noch aus Tabelle »versuche« verwiesen.
CONTEXT: SQL-Anweisung »DELETE FROM fach WHERE fachid = OLD.fachID«

Da mein Primary Key FachID noch als Fremdschlüssel in der Tabelle Versuch auftaucht, kann man diesen nicht löschen. Muss man dann erst die Datensätze löschen die noch Bezug auf die FachID haben, oder gibt es da eine einfachere und bessere Lösung?

Beim UPDATE kommt folgende Fehlermeldung:

FEHLER: Record »new« hat kein Feld »fbid«
CONTEXT: SQL statement “UPDATE fach SET fachID = NEW.fachID, fach = NEW.fach, studienrichtung = NEW.studienrichtung, fbid = NEW.fbid WHERE fachID = old.fachID”

Da habe ich jedoch keine Ahnung was ich da verändern muss damit es klappt.

Würde mich über Tipps freuen, wie ich die beiden Probleme am besten angehen könnte.

Gruß Ulti

Siehst Du im VIEW ein Feld “fbid”?


Zum löschen: ON DELETE CASCADE als Stichwort.

Andreas

Siehst Du im VIEW ein Feld “fbid”?

Nein, aber ich dachte ich muss es auch ansprechen da es ein “Bestandteil” der Tabelle Fach ist. Oder muss ich die fbid auch mit in den Fachbereich aufnehmen?

Zum löschen: ON DELETE CASCADE als Stichwort.

Okay danke, da werde ich nun mal nachlesen. Habe ich bislang aber noch nie von gehört.

Gruß Ulti

Naja, die Fehlermeldung “FEHLER: Record »new« hat kein Feld »fbid«” ist ja eindeutig, oder?

Andreas

Könnte das auch an der Groß- und Kleinschreibung liegen?

Gruß Thomas

Soweit ich sehe hast Du zwar z.T. auch Großbuchstaben genommen, aber nicht in “” gequotet. Damit macht PG da überall klein draus. Sollte also kein Problem sein, prüf aber nochmal, ob nicht vielleicht doch expliziet Großbuchstaben sind (PGAdmin macht gern Späße mit sowas…)


Andreas

Ne an der Groß- und Kleinschreibung liegt es nicht dran, habe es aber direkt mal überarbeitet damit ich dort eine klare Linie habe. Ich habe die fbid nun noch mit in die View geschrieben und die Zeilen mit dem UPDATE folgendermaßen abgeändert:

ELSIF TG_OP = ‘UPDATE’ THEN
UPDATE fachbereich SET fbid = NEW.fbid, fachbereich = NEW.fachbereich WHERE fachbereich = OLD.fachbereich;
UPDATE fach SET fachid= NEW.fachid, fach = NEW.fach, studienrichtung = NEW.studienrichtung, fbid = NEW.fbid WHERE fachid = old.fachid;
RETURN new;

Nun klappt es auch. Ist aber natürlich nicht so schön, da ich die fbid nicht unbedingt mit in meiner View haben will. Kann ich das Update auch machen, ohne das die fbid mit in der View ist, oder ist das nicht möglich?

Vielen Dank übrigens für den Tipp mit CASCADE. Ich hatte den Begriff bislang noch nie gehört und wäre da wohl im Leben nicht draufgekommen.

Ok doch noch ein Problem. Wenn ich nun bei meinem UPDATE den fachbereich ändern will, dann ändert er mir diesen für die ID. Das ist mir gestern noch aufgefallen. Also geht es so doch nicht…

Hatte mir dazu nun auch eine zählvariable gemacht, aber irgendwie kommt da nichts vernünftiges bei raus bislang.

Ups, ich dachte ich hätte meinen Code hier geposted. Sorry für den Dreifachbeitrag, aber editieren kann ich es nicht mehr.

ELSIF TG_OP = ‘UPDATE’ THEN SELECT INTO zaehl count(*) FROM fachbereich WHERE fachbereich = new.fachbereich;
IF zaehl=0 THEN
INSERT INTO fachbereich VALUES (DEFAULT,NEW.fachbereich);
END IF;
UPDATE fach SET fachID = NEW.fachID, fach = NEW.fach, studienrichtung = NEW.studienrichtung, fbid = NEW.fbid WHERE fachID = old.fachID;
RETURN new;

Falls es einen neuen Fachbereich geben sollte, schreibt er diesen nun rein, aber es ändert sich nicht in der Tabelle Fach und daher auch nicht in der View :x Wieder ein Denkfehler?

Entschuldigung das ich mich schon wieder melde, aber nun sollte es meiner Meinung nach passen. Hätte aber gerne nochmal eine zweite Meinung ob das so alles ok ist, oder ob man das vielleicht besser schreiben kann, da ich mich wie gesagt noch nicht so gut mit Triggern auskenne!

ELSIF TG_OP = ‘UPDATE’ THEN
SELECT INTO zaehl count(*) FROM fachbereich WHERE fachbereich = new.fachbereich;
IF zaehl=0 THEN
INSERT INTO fachbereich VALUES (DEFAULT,NEW.fachbereich);
SELECT INTO te fbid FROM fachbereich WHERE fachbereich = new.fachbereich;
ELSIF zaehl=1 THEN
SELECT INTO te fbid FROM fachbereich WHERE fachbereich = NEW.fachbereich;
END IF;
UPDATE fach SET fachID = NEW.fachID, fach = NEW.fach, studienrichtung = NEW.studienrichtung, fbid = te WHERE fachID = old.fachID;
RETURN new;