Trigger für Generalisierungsbeziehung

[skruffes]

Hallo,

wie im Titel geschrieben benötige ich einen (denke vielleicht sogar zwei) der mit bei einer Generalisierungsbeziehung helfen soll.

Mein create Statement für die 3 betreffenden Tabellen sieht so aus:

CREATE TABLE APosition (
    apnr  INTEGER DEFAULT nextval('seq_apnr'),
    anr INTEGER REFERENCES Auftrag(anr),
    liefertermin DATE DEFAULT current_date,
    rabatt INTEGER DEFAULT 0,
   
    gehoert_zu_rnr INTEGER,
    gehoert_zu_rpnr INTEGER,
    FOREIGN KEY(gehoert_zu_rnr, gehoert_zu_rpnr) REFERENCES RPosition(rnr, rpnr),
   
    PRIMARY KEY (anr, apnr)
);

CREATE TABLE Sonstige_Position (

    anr INTEGER,
    apnr INTEGER,
    wert  INTEGER  NOT NULL CHECK (wert > 0),
    beschreibung VARCHAR(40) NOT NULL,
   
    PRIMARY KEY (anr, apnr),
    FOREIGN KEY(anr, apnr) REFERENCES APosition(anr,apnr)
);

CREATE TABLE Leistungs_Position (
    anr INTEGER,
    apnr INTEGER,
    menge  INTEGER  NOT NULL CHECK (menge > 0),
    betrifft INTEGER REFERENCES Leistung(lnr),
   
    PRIMARY KEY (anr, apnr),
    FOREIGN KEY(anr, apnr) REFERENCES APosition(anr,apnr)
);

Man sollte hier schon erkennen was ich möchte: Eine Auftragsposition kann entweder eine Sonstige Position oder eine Leistungsposition sein.

Dies möchte ich mit einem Trigger kontrollieren der vor dem update oder insert in der anderen Tabelle nachsieht, ob es schon einen Eintrag mit der Auftragsnummer (anr) und Auftragspositionsnummer (apnr) gibt.

ABER: Wie geht das? Ich kenne jetzt nur Trigger die über new and old Werte der eigenen Tabelle ansehen..

Würde mich über einen Tipp sehr freuen
LG

 

[akretschmer]

Du willst sicherstellen, daß entweder anr oder apnr in Leistungs_Position gesetzt ist, nie aber beide, aber immer ein Feld, und daß ein Wert über beide Spalten nie doppelt vorkommt?

Falls ja, geht einfacher, ohne Trigger:

test=# create table bla(a int, b int, check((case when a is null then 1 else 0 end + case when b is null then 1 else 0 end) = 1));
CREATE TABLE
test=*# create unique index idx_ab on bla((coalesce(a,b)));
CREATE INDEX

vereinfach, ist aber nach Deinen Vorgaben.

 

[skruffes]

Hallo akretschmer,

vielen Dank für deine Antwort!

Du willst sicherstellen, daß entweder anr oder apnr in Leistungs_Position gesetzt ist, nie aber beide, aber immer ein Feld, und daß ein Wert über beide Spalten nie doppelt vorkommt?

fast: anr und apnr werden immer zusammen gesetzt, da sie die AuftragsPosition (APosition) identifizieren. Diese beiden Werte sollen entweder in Leistungs_Position oder in Sonstige_Position stehen. Ein Wert darf dann nie über diese beiden Spalten doppelt vorkommen.

Falls ja, geht einfacher, ohne Trigger:

Ich denke auch dass dies mit coalesce als Bedingung einfacher geht, würde dennoch gern den Weg über einen oder zwei Trigger probieren. Einfach um diesen besser zu verstehen, denn dies muss ja theoretisch funktionieren. Laufzeit ect. ist jetzt mal egal.

Danke!

 

[akretschmer]

Du müßtest dann im Trigger abfragen, ob der Wert schon mal da ist. Das wird mit zunehmender Größe der Tabelle immer zeitaufwändiger. Und potentiell auch riskant bei vielen nebenläufigen Prozessen. Ich würde ohne Not keine Trigger verwenden, wenn es nicht bessere Wege gibt.

 

[skruffes]

Ok danke.

Wenn ich den Trigger dennoch schreiben würde, wie könnte ich auf den Wert einer anderen Tabelle zugreifen? Würde dies ja sicherlich auch bei anderen Aufgaben benötigen und meine Aufgabe dient jetzt nur als "Beispiel".

Gibt es so etwas wie ein select in einer Triggeranweisung? Wüsste sonst nicht wie ich mit den Befehlen new bzw. old an eine andere Tabelle kommen könnte.

 

[akretschmer]

Ja, du kannst alles mögliche in der Triggerfunktion machen, kein Problem. NEW.* und OLD.* beziehen sich auf den Zustand des aktuellen Datensatzes.

 

[skruffes]

Ok danke, dann sollte dies funktionieren oder?

Kenne mich leider noch nicht so mit den Triggern aus...

CREATE OR REPLACE TRIGGER trigger_generalisation_check
BEFORE UPDATE OR INSERTION Sonstige_Position
FOR EACH ROW WHEN(old.anr <> new.anr
)
DECLARE
l_anr Sonstige_Position.anr%TYPE;
BEGIN
SELECT anr INTO l_anr
FROM Leistungs_Position
WHERE anr =:new.anr;

IF l_anr IS NOT NULL THEN
INSERT INTO Sonstige_Position(anr, apnr)
VALUES(null,null);
ENDIF;
END;

 

[akretschmer]

Command:  CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
  ON table_name
  [ FROM referenced_table_name ]
  [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
  [ FOR [ EACH ] { ROW | STATEMENT } ]
  [ WHEN ( condition ) ]
  EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

  INSERT
  UPDATE [ OF column_name [, ... ] ]
  DELETE
  TRUNCATE

Du hast also erst einmal eine Funktion zu definieren. Und mache dann in einem Insert-Trigger bitte kein Insert, weil dieser wieder einen Trigger auslöst, die einen Insert macht, der einen Trigger auslöst, der ...

 

[skruffes]

Ok vielen Dank für deine Hilfe!

Und mache dann in einem Insert-Trigger bitte kein Insert, weil dieser wieder einen Trigger auslöst, die einen Insert macht, der einen Trigger auslöst, der .

Wie kann ich einen Insert Vorgang eleganter abbrechen?

 

[akretschmer]

wenn Du ein Feld im aktuellen Datensatz noch ändern willst dann ein before-trigger und NEW.<feld> ändern, wenn der Insert scheitern soll, dann die Funktion mit return NULL beenden.

Bei Gelegenheit mal das lesen: PostgreSQL: Documentation: 9.4: Trigger Procedures