Hallo erstmal!
Da hätt ich auch schon ne Frage:
Bin ein ziemlicher Neuling in PL/SQL und muss nun einen Trigger schreiben, der ein RAISE_APPLICATION_ERROR ausgibt, wenn in eine vorhandene Datenbank mit Spalten „von“ und „bis“ neue Zeilen eingefügt werden, bei denen sich die Zeiträume mit den gegebenen Daten überschneiden.
Kann mir da jemand auf die Sprünge helfen?
mfg & Danke
hallo
Bin ein ziemlicher Neuling in PL/SQL und muss nun einen
Trigger schreiben, der ein RAISE_APPLICATION_ERROR ausgibt,
wenn in eine vorhandene Datenbank mit Spalten „von“ und „bis“
neue Zeilen eingefügt werden, bei denen sich die Zeiträume mit
den gegebenen Daten überschneiden.
ich fürchte, so einfach wird das nicht. das problem ist, dass oracle es einem trigger nicht zulässt, auf die tabelle, auf die gerade zugegriffen wird, lesend zuzugreifen.
angenommen, du hast folgende tabelle:
create table zeitraum
( id number(5) not null,
von date not null,
bis date not null );
nett wäre nun ein trigger in der art:
create or replace trigger ti\_zeitraum
before insert on zeitraum
for each row
is
ln\_anzahl number ;
begin
select count(\*)
into ln\_anzahl
from zeitraum
where :new.von between von and bis
or :new.bis between von and bis ;
if ln\_anzahl \> 0 then
raise\_application\_error (-20001, 'Zeitraum überschneidet sich');
end if ;
end ;
(keine ahnung, ob das oben prinzipiell kompilieren würde - habe gerade kein oracle zur verfügung…)
sobald aber alle syntaxfehler und sonstigen tippfehler ausgebessert sind, wir oracle immer noch sagen, dass kein zugriff auf die eigene tabelle möglich ist (du bekommst „ORA-04091: table name is mutating, trigger/function may not see it.“)
google mal zu dem thema:
http://www.google.at/search?client=firefox-a&rls=org…
findest jede menge mehr oder weniger hilfreiche seiten zum thema.
wirklich sauber lässt sich das problem nicht lösen. ich gehe meist so vor, dass ich mir eine prozedur bzw. eine package schreibe, die den zugriff auf die tabelle macht. diese prozedur kann dann ohne probleme alle checks machen, die notwendig sind. dem jeweiligen anwender nimmt man dann einfach das insert-recht auf die tabelle und gibt ihm nur das execute auf die prozedur. schon kann er nur mehr auf kontrolliertem weg auf die tabelle zugreifen.
oder du verlagerst diesen check gleich in die anwendung - im vertrauen darauf, dass der anwender nur mit diesem programm auf die datenbank zugreifen wird.
lg
erwin
Naja, soll ja ein statement-trigger sein, kein row-trigger.
Da tritt dann ja auch kein mutating table auf.
sobald aber alle syntaxfehler und sonstigen tippfehler
ausgebessert sind, wir oracle immer noch sagen, dass kein
zugriff auf die eigene tabelle möglich ist (du bekommst
„ORA-04091: table name is mutating, trigger/function may not
see it.“)
Hallo,
du brauchst beides:
Gruß
Peter
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Erstmal Danke für die Hilfe 
Leider steht in meiner Aufgabenbeschreibung explizit, das ich einen (oder mehrere) Statement-Trigger verwenden soll.
Werd mich morgen früh mal dransetzen, dann seh ich weiter…
Hallo,
du brauchst beides:
- Du musst eine Temporäre Tabelle anlegen, um die Werte zu
sammeln.- Die Temporäre Tabelle wird über einen Zeilentrigger
gefüllt.- Jetzt kann man die Werte wieder auslesen und die
Überprüfungen machen.Gruß
Peter
Hi!
ich fürchte, so einfach wird das nicht. das problem ist, dass
oracle es einem trigger nicht zulässt, auf die tabelle, auf
die gerade zugegriffen wird, lesend zuzugreifen.
Doch, doch, das Zauberwort nennt sich „Autonomous Transaction“
Und mit Deinem Beispiel:
create or replace trigger ti\_zeitraum
before insert on zeitraum
for each row
DECLARE
PRAGMA AUTONOMOUS\_TRANSACTION;
ln\_anzahl number ;
begin
select count(\*)
into ln\_anzahl
from zeitraum
where :new.von between von and bis
or :new.bis between von and bis ;
if ln\_anzahl \> 0 then
raise\_application\_error (-20001, 'Zeitraum überschneidet sich');
end if ;
end ;
(keine ahnung, ob das oben prinzipiell kompilieren würde -
habe gerade kein oracle zur verfügung…)
geht mir genauso
Allerdings: Diese autonomen Transaktionen in Tabellen-Triggern habe ich persönlich noch nie angewandt - ein bisserl heikel ist es halt schon, den sobald mehrere Records eingefügt werden, ist der Zustand der Tabelle gar nicht mehr mal so eindeutig definierbar …
Grüße,
Tomh
Auch hier nochmal danke für die Hilfe, leider muss ich definitiv einen Statement Trigger verwenden 
Doch, doch, das Zauberwort nennt sich „Autonomous Transaction“
Und mit Deinem Beispiel:
create or replace trigger
ti_zeitraum
before insert on zeitraum
for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
ln_anzahl number ;
begin
select count(*)
into ln_anzahl
from zeitraum
where :new.von between von and bis
or :new.bis between von and bis ;
if ln_anzahl > 0 then
raise_application_error (-20001, ‚Zeitraum überschneidet
sich‘);
end if ;
end ;(keine ahnung, ob das oben prinzipiell kompilieren würde -
habe gerade kein oracle zur verfügung…)geht mir genauso
Allerdings: Diese autonomen Transaktionen in Tabellen-Triggern
habe ich persönlich noch nie angewandt - ein bisserl heikel
ist es halt schon, den sobald mehrere Records eingefügt
werden, ist der Zustand der Tabelle gar nicht mehr mal so
eindeutig definierbar …Grüße,
Tomh
Hallo,
wenn dir alle intelligenten Wege verbaut sind - komische Aufgabe - schreib doch einfach eine Prozedur, die die gesamte Tabelle überprüft. Die stößt du einfach in deinem geliebten Statement Trigger an und wirfst gegebenenfalls eine Exception.
Gruß
Peter
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Man soll halt lernen, wie`s geht… 
Alles klar, damit versuch ichs jetzt mal…
Hallo,
wenn dir alle intelligenten Wege verbaut sind - komische
Aufgabe - schreib doch einfach eine Prozedur, die die gesamte
Tabelle überprüft. Die stößt du einfach in deinem geliebten
Statement Trigger an und wirfst gegebenenfalls eine Exception.Gruß
Peter
Hallo Tom,
ich fürchte, so einfach wird das nicht. das problem ist, dass
oracle es einem trigger nicht zulässt, auf die tabelle, auf
die gerade zugegriffen wird, lesend zuzugreifen.Doch, doch, das Zauberwort nennt sich „Autonomous Transaction“
Jetzt bin ich aber enttäuscht: Das funktioniert doch gar nicht!
SQL\> create table mar\_tab (von date, bis date);
Table created.
SQL\> create or replace trigger mar\_trg before insert on mar\_tab for each row
2 DECLARE
3 PRAGMA AUTONOMOUS\_TRANSACTION;
4 ln\_anzahl number ;
5 begin
6 select count(\*)
7 into ln\_anzahl
8 from mar\_tab
9 where :new.von between von and bis
10 or :new.bis between von and bis ;
11 if ln\_anzahl \> 0 then
12 raise\_application\_error (-20001, 'Zeitraum überschneidet sich');
13 end if ;
14 end ;
15 /
Trigger created.
SQL\> insert into mar\_tab values (sysdate-10, sysdate-5);
1 row created.
Jetzt machen wir ein zweites SQL*Plus auf und schreiben dort:
SQL\> insert into mar\_tab values (sysdate-7, sysdate-2);
1 row created.
SQL\> commit;
Commit complete.
Wieder zurück im ersten Fenster:
SQL\> commit;
Commit complete.
SQL\> select \* from mar\_tab;
VON BIS
---------- ----------
11.05.2007 16.05.2007
14.05.2007 19.05.2007
Und jetzt?
(keine ahnung, ob das oben prinzipiell kompilieren würde -
habe gerade kein oracle zur verfügung…)geht mir genauso
Tut es, löst aber leider das Problem nicht. Dir fehlt der „Lock table“ (der einem übrigens auch ohne autonomous_transaction nicht erspart bleibt).
autonomous_transaction kann hier aber noch mehr:
SQL\> delete from mar\_tab;
2 rows deleted.
SQL\> commit;
Commit complete.
SQL\> insert into mar\_tab values (sysdate - 5, sysdate);
1 row created.
SQL\> insert into mar\_tab values (sysdate - 3, sysdate - 1);
1 row created.
SQL\> insert into mar\_tab values (sysdate - 2, sysdate - 2);
1 row created.
SQL\> commit;
Commit complete.
SQL\> select \* from mar\_tab;
VON BIS
--------- ---------
16-MAY-07 21-MAY-07
19-MAY-07 19-MAY-07
18-MAY-07 20-MAY-07
Sieht gar nicht gut aus…
Allerdings: Diese autonomen Transaktionen in Tabellen-Triggern
habe ich persönlich noch nie angewandt - ein bisserl heikel
ist es halt schon, den sobald mehrere Records eingefügt
werden, ist der Zustand der Tabelle gar nicht mehr mal so
eindeutig definierbar …
Doch, eindeutig definierbar schon. Das Problem ist, dass mit Triggern referentielle zwischen bzw. logische Integrität innerhalb von Tabellen nur abbildbar ist, wenn da mindestens ein „Lock Table“ drinsteht - zumindest solange es keine ON COMMIT Trigger gibt. Dafür waren Trigger natürlich auch nie gedacht.
So, jetzt habe ich zwar deine Lösung niedergemacht, aber mir selbst fällt natürlich ausser dem Lock auch nichts vernünftiges ein. Allerdings müsste sich da bei http://asktom.oracle.com was finden, ist ja kein Problem, das völlig aus der Welt ist. Mir schwebt da natürlich wieder was mit Function based indices im Kopf herum, ich kann es nur nicht dingfest machen *g*
Liebe Grüße,
Martin
Hi Martin!
Jetzt bin ich aber enttäuscht: Das funktioniert doch gar
nicht!
'Tschuldigung - hatte in Anbetracht meines Angina-erkrankten Sohnes keine Zeit, das Ganze auszuprobieren - „Bob auf der Ritterburg“ hatte ganz einfach Vorrang (Beginne nie eine Diskussion mit einem kranken, 5jährigem Kind! Man kann nur verlieren …)
So, jetzt habe ich zwar deine Lösung niedergemacht
Dazu bist Du ja da
aber mir selbst fällt natürlich ausser dem Lock auch nichts
vernünftiges ein. Allerdings müsste sich da bei
http://asktom.oracle.com was finden, ist ja kein Problem, das
völlig aus der Welt ist.
Eigentlich ein triviales Problem, mit dem sich wohl schon jeder rumgeschlagen und natürlich jeder eine andere Lösung implementiert hat …
Mir schwebt da natürlich wieder was
mit Function based indices im Kopf herum
Perversling! Pfui! (Meine ganz persönliche Meinung zu den Indizes mit den bösen Wörtern davor)
ich kann es nur nicht dingfest machen *g*
Ich würde mir sogar die Zeit nehmen, das „dingfest“ auseinanderzunehmen
Grüße,
Tomh
PS: Trotzdem glaube ich mich zu erinnern, daß ich vor kurzem (also ein paar Jahren) ein ähnliches Problem hatte und eine autonome Prozedur (die aus dem Trigger aufgerufen wurde) die Lösung war (mit PRAGMA und so’n Zeugs halt) …
Hi Tom,
aber mir selbst fällt natürlich ausser dem Lock auch nichts
vernünftiges ein. Allerdings müsste sich da bei
http://asktom.oracle.com was finden, ist ja kein Problem, das
völlig aus der Welt ist.
Tja, allzu viel hat Mr. Kyte leider auch nicht zu bieten (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::…):
_If you have two number columns in a table say c1 and c2, where c2 is always
higher than c1 and the rows contain ranges eg
c1 c2
1 5
6 20
24 28
36 134
145 148
etc
and you want to add a constraint such that no ranges are contained within
others or overlap in anyway, how would you recommend doing it? Given the
above data the following inserts would fail:
c1 c2
1 2
18 23
28 30
39 58
but these would be allowed:
c1 c2
21 22
29 34
149 999
etc
**Followup** October 16, 2003 - 11am US/Eastern:
its really hard and relies on triggers that basically force serialization on
modifications to the table and query the results in AFTER triggers to see if
everything is OK._
Eigentlich ein triviales Problem, mit dem sich wohl schon
jeder rumgeschlagen und natürlich jeder eine andere Lösung
implementiert hat …
…wovon die meisten vermutlich falsch sind.
Mir schwebt da natürlich wieder was
mit Function based indices im Kopf herumPerversling! Pfui! (Meine ganz persönliche Meinung zu den
Indizes mit den bösen Wörtern davor)
Abgesehen davon, dass FBI’s wohl hier doch nicht der „way to go“ sind (sonst hätte TK sie wohl erwähnt, weil er die gerne und oft verwendet): Was hast du gegen FBIs?
ich kann es nur nicht dingfest machen *g*
Ich würde mir sogar die Zeit nehmen, das „dingfest“
auseinanderzunehmen
Naja, mir fällt schon noch was ein: Eine eigene Tabelle, die die freien Tage speichert. Aus der wird gelöscht (und dabei geprüft, ob die Anzahl der Tage und die der Rows übereinstimmt) und dann in die Intervalltabelle eingefügt. Sollte für abgegrenzte Zeiträume ganz gut funktionieren und vor allem immer nur den benötigten Zeitraum für Inserts/Updates sperren, nicht gleich die gesamte Tabelle. Wenn das nicht praktikabel ist, dann hilft wirklich nur noch der Lock Table im Trigger.
PS: Trotzdem glaube ich mich zu erinnern, daß ich vor kurzem
(also ein paar Jahren) ein ähnliches Problem hatte und eine
autonome Prozedur (die aus dem Trigger aufgerufen wurde) die
Lösung war (mit PRAGMA und so’n Zeugs halt) …
Den Code würde ich suchen und ausbessern
Gruß
Martin
Hi Martin!
…wovon die meisten vermutlich falsch sind.
Ich würde eher sagen, daß die meisten Probleme nicht alle Fälle abbilden (und die Fehler nach der Reihe eintrudeln …)
Was hast du gegen FBIs?
Ich mag sie nicht - und sie mögen mich nicht; sie funktionieren NIE so, wie ich will; und wenn sie dann doch mal mein gewünschtes Ergebnis liefern, kommt das geflügelte Wort „Performance“ ins Spiel …
Naja, mir fällt schon noch was ein: Eine eigene Tabelle, die
die freien Tage speichert. Aus der wird gelöscht (und dabei
geprüft, ob die Anzahl der Tage und die der Rows
übereinstimmt) und dann in die Intervalltabelle eingefügt.
Sollte für abgegrenzte Zeiträume ganz gut funktionieren und
vor allem immer nur den benötigten Zeitraum für
Inserts/Updates sperren, nicht gleich die gesamte Tabelle.
Wenn das nicht praktikabel ist, dann hilft wirklich nur noch
der Lock Table im Trigger.
Oder - wie es meistens gemacht wird - die Prüfung erfolgt vollständig in der Applikation und man hofft halt, das es zu keinen Worst-Case-Szenarien kommt - also das wirklich mal 2 User gleichzeitig an zwei sich ausschließenden Sätzen „arbeiten“
Den Code würde ich suchen und ausbessern
Gott behüte - der Code ist seit Jahren in einer Produktionsdatenbank und bisher fiel er noch nicht auf *klopf* *klopf* *klopf* - dürfte also noch ein Worst-Case eingetreten sein.
Grüße,
Tomh