Trigger entwerfen

[PaulWenzel]

Hallo.

Ich versuche mich grad an einer Übungsaufgabe (Klausurvorbereitung) und will einen Trigger entwerfen.

Habe jetzt aber keine herangehensweise und weiß gar nicht wie ich anfangen soll.

http://img127.imageshack.us/img127/3951/tabdy1.jpg

Der Trigger soll eine Statistik führen über welche Spediteure welche Artikel in welchen Mengen versendet wurden.

Das heißt, sobald eine neue Versandposition auf dem Weg gebracht wird, soll die Spediteursstatiktik aktualisiert werden.

Kann mir jemand weiterhelfen ?

danke schonmal im vorraus

[Jan Jansen]

Jedes mal wenn eine neue Versandposition eingefügt wird, erhöhe in der Versandstatistik für den entsprechenden Lieferanten den entsprechenden Artikel um die Menge aus der Versandpostion

[PaulWenzel]

Ja gut aber wie sieht das in Code umgesetzt aus ?

[dr.dimitri]

Nun zum einen wissen wir nicht, welche Db Du verwendest - das wäre nicht ganz unerheblich. Ein Trigger in Oracle sieht anders aus als einer in MSSQL, DB2 oder PostgreSQL.

Des weiteren wird sich hier wohl keiner finden, der dir einfach so die Hausaufgabe runterschreibt.

Und last but not least, kannst Deinem Lehrer von mir ausrichten, dass fachliche Logik in einem Trigger nichts, aber auch gar nichts verloren hat.

Kannst ihn ja mal fragen, wie er denkt, dass sich das ganze verhält, wenn sagen wir mal 20,30 oder mehr Personen gleichzeitig hier Daten ändern - mal davon abgesehen, dass man solche Daten in einem OLTP System nicht on the fly berechnen, sondern, falls benötigt, zur Laufzeit per SQL berechnen lässt. Aber das nur am Rande.

Dim

[PaulWenzel]

Oracle wäre die DB.

Hausaufagbe ist das nicht. Es ist eine alte Klausuraufagbe die ich lösen will um mich auf die Klausur vorzubereiten.

Ich kenne leider keinen der mir da weiterhelfen kann, drum versuche ich es hier und hoffe auf eure Hilfe.

Das mit der fachlichen Logik wird ihn wohl nicht weiter interessieren aber ich kann ihn ja mal drauf ansprechen und schauen was er dazu sagt ...

[dr.dimitri]

Ok. Bei Oracle kann ich dir weiterhelfen. Was hast denn bisher schon geschafft? Hast Du zumindest grundlegende Grundlagen in PL/SQL (das ist die Programmiersprache in der der Trigger geschrieben wird).

Hier mal die Grundlagen zu Triggern.

Dim

[PaulWenzel]

Die Grundlagen hab ich durchgearbeitet. D.h. ich habe die Folien von uns, viele Google Seiten

(u.a. auch dein Link

PL/SQL-Tutorial: Eine kurze Übersicht zu PL/SQL

Datenbank-Tutorien : Main - Home Page browse

Oracle PL/SQL Tutorial - Die Programmiersprache von Oracle

Contents

) gelesen.

Wir haben vorher immer nur simple SQL Abfragen gemacht und das konnt ich.

Das Problem ist halt lesen ist die eine Sache aber eine Aufgabe lösen und da dann abstahieren können was gewollt ist, ist was anderes

[dr.dimitri]

Also ich würde so anfangen, dass Du erstmal die rohen SQLs baust

Innerhalb eines Triggers hast Du ja alle Daten die in die Tabelle eingefügt werden sollen über die :new.spaltenname Struktur.

Jetzt geh das ganze einfach mal Schritt für Schritt durch. Mach also einen Insert in die Positionstabelle und nimm dann die entsprechenden Werte aus diesem insert um manuell die Spediteurstabelle entsprechend zu ändern.

Sobald Du das nämlich hast, muss nur noch ein Standardtriggergerüst drüber und fertig.

Dim

[PaulWenzel]

Gut, dann werde ich das mal ausprobieren aber obs heute noch ein Ergebnis gibt bezweifle ich.

Danke

[PaulWenzel]

Innerhalb eines Triggers hast Du ja alle Daten die in die Tabelle eingefügt werden sollen über die :new.spaltenname Struktur.

Jetzt geh das ganze einfach mal Schritt für Schritt durch. Mach also einen Insert in die Positionstabelle und nimm dann die entsprechenden Werte aus diesem insert um manuell die Spediteurstabelle entsprechend zu ändern.

Dim

Wie ist das jetzt gemeint speziell mit : new.spaltenname Struktur?

Ich sitze jetzt schon bestimmt ne Stunde dran aber komm nicht vorwärts.

create trigger trg_insert_Versandposition

after insert

on Versandposition

for each row       richtig???

In der Tabelle Versandposition wird eine neue Zeile eingefügt mit VNR, LFNDNR, ANR und Menge.

Von diesen Werten brauche ich ANR & Menge für Spediteursstatistik.

Aber ich muss noch ANR & Menge über Spediteur SNR über Versand VNR SNR mit Versandposition VNR miteinander verbinden?

[dr.dimitri]

Richtig. Das komplette Gerüst würde aber so aussehen:

create or replace trigger trg_insert_Versandposition

after insert

on Versandposition

for each row

declare

 --Hier Variablendeklarationen rein falls notwendig

begin

 --mach was

NULL; --Wird nur benötigt, solange kein weiterer Code hier drin steht.

end;

An die Werte kommst Du per : new dran:

:new.VNR

Aber ich würd, wie gesagt, erstmal die eigentlichen Selects/Updates ohne Trigger machen dann siehst beim Ausfüren gleich welches Ergebnis sie bringen.

Dim

[PaulWenzel]

select VNR.Versandposition, ANR.Versandposition, Menge.Versandposition, SNR.Versand

Ich versteh jetzt nicht welche ich auswählen und wie die miteinander verknüpft werden sollen.

Ich muss doch jetzt Versandposition mit Spediteursstatistik verknüpfen und das über Versand anhand der SNR ??

[PaulWenzel]

So ich hab hier weiter probiert.

create or replace trigger trg_insert_Versandposition

after insert

on Versandposition

for each row

declare

 WertSNR int


begin

 select vp.SNR

FROM Versandposition VP

         JOIN Versand VS ON VP.VNR = VS.VNR

and FROM Versand VS

         JOIN Spediteurstatistik S ON VS.SNR = S.SNR

INTO WertSNR

UPDATE Spediteursstatistik

SET SNR = :WertSNR

end;

Ist das so vom Prinzip her jetzt richtig ?

Das selbe dann für ANR machen ?

Bearbeitet 23. September 2008 von PaulWenzel

[dr.dimitri]

Na langsam wirds ja was, aber ein paar Sachen sind da schon noch.

1. Ein SELECT ... INTO funktioniert nur, wenn man genau einen Rückgabewert bekommt.

2. Du möchtest eine Anzahl wissen, änderst aber (von der Logik her) die SNR.

Leider fehlen in dem Datenmodell die Angabe der PK Felder, ich nehm daher mal an, dass das erste Feld der PK ist.

Ich würde folgendermaßen vorgehen:

1. Herausfinden für welchen Spediteur (SNR) die Versandposition bestimmt ist.

2. Ermitteln, wieviele dieser Artikel der Spediteur bekommen hat. Dazu joinen wir wiederum versand und versandposition, diesmal allerdings zählen wir die Einträge (im übrigen ist das ein potentieller Bug, bei dem ein Mutating Table Fehler auftreten kann, sobald man einen INSERT INTO ... SELECT verwenden und keinen INSERT INTO ... VALUES(). daher ist das Beispiel wie gesagt absolut nicht praxistauglich. Kannst dem Aufgabensteller ausrichten).

3. Jetzt haben wir alles was wir brauchen und speichern das in der Tabelle Spediteurstatistik ab.

Da wir nicht wissen, ob es für diesen Spediteur schon einen Eintrag gibt (könnt ja auch ein ganz neuer sein), verwenden wir dazu den MERGE Befehl.

CREATE OR REPLACE TRIGGER TRG_INSERT_VERSANDPOSITION

AFTER INSERT ON VERSANDPOSITION

FOR EACH ROW

DECLARE

 l_snr NUMBER;

 l_posCount NUMBER;

BEGIN

--1. Den Spediteur herausfinden.

 SELECT 

       vers.snr INTO l_snr

  FROM        

       versand vers

  WHERE 

       vers.vnr=:new.versand_vnr; --Hier verwenden wir die :new.Spaltenname Sytax um auf den neu eingefügten Wert zugreifen zu können


--2. Wir kennen jetzt den Spediteur, jetzt finden heraus, wieviele Artikel dieser Versandposition er schon bekommen hat

--Da auf die Tabelle vertragposition zugegriffen wird, ist das ein potentieller Bug der einen ORA-04091 zur Folge hat.

 SELECT

        COUNT(vp.vnr) INTO l_posCount;

   FROM

        versand vers,

        vertragposition vp

  WHERE 

        vers.snr=l_snr --Der vorher ermittelte Spediteur

    AND

        vers.vnr=vp.versand_vnr --Seine Versandpositionen

    AND 

        vp.artikel_anr=:new.artikel_anr; --Für diesen speziellen Artikel


--3. Wir haben jetzt den Spediteur, den Artikel und die Anzahl der Positionen für diesen Artikel

--Jetzt aktualisieren wir die Tabelle Spediteurstatistik bzw. fügen eine neue Zeile ein, falls diese noch nicht existiert.

--Dazu existiert in Oracle der MERGE Befehl


MERGE INTO spediteurstatistik dest

USING (SELECT l_snr AS snr ,l_posCount AS menge, :new.artikel_anr AS anr FROM dual) src

ON (dest.spediteur_snr=src.snr AND dest.anr=src.anr)

WHEN MATCHED THEN UPDATE SET dest.menge=src.menge

WHEN NOT MATCHED THEN INSERT(snr,spediteur_snr,artikel_anr,anr,menge)

                      VALUES(seq_stat.nextval,dest.snr,dest.anr,dest.anr,dest.menge);


END;

/

Im Merge Befehl verwende ich eine Sequence um den PK für einen neuen Satz festzulegen. Die musst Du vorher anlegen damit sich der Trigger komplilieren lässt.

create sequence stat;

Was mir noch aufgefallen ist: warum wird in der Statistiktabelle Zweimal die ANR abgelegt? Naja der Aufgabensteller wird das schon wissen :upps

Dim

PS: ich hab das jetzt einfach mal so runtergeschrieben - könnte also sein, dass sich noch der ein oder andere Fehler eingeschlichen hat, aber das kannst Du dann ja erledigen.

[PaulWenzel]

Der sieht ja komplett anders aus... Da hab ich ja noch was zu tun damit ich es verstehe.

Ich schaue ihn mir schon an.

danke

PS:

Ich hab im DBDesigner4 die Statistiktabelle mit zweimal der ANR angelegt wohl.

:uli

Bearbeitet 24. September 2008 von PaulWenzel

[PaulWenzel]

WHEN NOT MATCHED THEN INSERT(snr,spediteur_snr,artikel_anr,anr,menge)

                      VALUES(seq_stat.nextval,dest.snr,dest.anr,dest.anr,dest.menge);

Ist das richtig soweit oder was verstehe ich da falsch?

Auf der Oracleseite sind da auch nicht doppelte Werte drinne.

Sonst ist der Rest mir klar.

[dr.dimitri]

Auf der Oracleseite sind da auch nicht doppelte Werte drinne.

Wie meinst das? Wenn die ON Klausel nicht erfüllt ist, dann wird NOT MATCHED ausgeführt. MERGE entscheidet anhand der ON Klausel ob ein insert oder ein Update stattfinden soll.

Dim

[PaulWenzel]

Merge speziell bsp

Das Beispiel von Merge und da steht (Colum,)

Bei uns steht snr,spediteur_snr,artikel_anr,anr,menge.

Wäre nicht snr, anr und menge ausreichend?

Bei VALUES steht dest.anr doppelt?

Diese Sachen verstehe ich nicht.

[dr.dimitri]

Das Beispiel von Merge und da steht (Colum,)

Ja und das bedeutet, dass man hier die Spalten angiebt.

Bei VALUES steht dest.anr doppelt?

Siehe meine Anmerkung:

Was mir noch aufgefallen ist: warum wird in der Statistiktabelle Zweimal die ANR abgelegt? Naja der Aufgabensteller wird das schon wissen

In der Tabelle die Du gepostet hast, ist das Feld ANR und ARTIKEL_ANR enthalten. Da ich nicht weißt was es sonst sein soll, gehe ich davon aus, dass es sich zweimal das gleiche Feld ist, warum auch immer, und befülle es auch zweimal mit dem gleichen Wert.

Dim

[PaulWenzel]

Nun gut. So gehen ergibt das dann Sinn.

Danke für die Arbeit.