Sie sind nicht angemeldet.

  • Anmelden

Oraclefrage: Integritätsprüfung

Lieber Besucher, herzlich willkommen bei: MastersForum. Falls dies Ihr erster Besuch auf dieser Seite ist, lesen Sie sich bitte die Hilfe durch. Dort wird Ihnen die Bedienung dieser Seite näher erläutert. Darüber hinaus sollten Sie sich registrieren, um alle Funktionen dieser Seite nutzen zu können. Benutzen Sie das Registrierungsformular, um sich zu registrieren oder informieren Sie sich ausführlich über den Registrierungsvorgang. Falls Sie sich bereits zu einem früheren Zeitpunkt registriert haben, können Sie sich hier anmelden.

Hendor

Profi

  • »Hendor« ist der Autor dieses Themas

Beiträge: 1 097

Beruf: GER

  • Nachricht senden

1

14.02.2006, 15:22

Oraclefrage: Integritätsprüfung

Hi Experts,

kurze Frage zum Thema Integritätsprüfung unter Oracle 9i.

Szenario:
Tabelle User, Spalte Account ist via Costraint auf "unique" eingestellt. Besagte Spalte enthält bereits einen Wert "Hendor".
Nun kommt jemand daher und legt einen neuen Account "hendor" an bzw. ändert nen Bestehenden auf diesen Wert.
Da gemäß unique-Regel "Hendor" verschieden von "hendor" ist, läuft die Transaktion problemlos durch.

Meine Aufgabe:
Dieses zu verhindern, sprich: ein Konstrukt zu schaffen, was bei reiner Zeichenfolgegleichheit (also unabhängig von Groß-/Kleinschreibung) anspringt und die Transaktion blockt.

Meine Ansatz:
CREATE OR REPLACE TRIGGER "eineindeutige_accountnamen" BEFORE
INSERT OR UPDATE OF "name" ON "account" FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM acount WHERE UPPER(name)=UPPER(:NEW.name)) > 0
THEN
RAISE_APPLICATION_ERROR(-20100,'Die Zeichenfolge für Account gibts schon');
END IF;
END;

Problem:
Innerhalb eines "befor update for each row" Triggers, ist ein Select auf die selber Tabelle (auf die die Transaktion abzielt) nicht zulässig, da ein Teil der Datensätze durch die Transaktion gesperrt sind und daher vom Trigger nicht gelesen werden können.

Habe die Programmierung jetzt auf die Clientseite verschoben und frage erst die Gültigkeit ab, bevor ich das Update auf die DB loslasse.
Leider muß ich es mit der Methode noch an weiteren 2.465.411.... Stellen ändern und dazu hab ich keine Böcke ;(

Also bitte, bitte helft mir!!! Muß auch nicht zwingend mit nem Trigger gelöst werden. Mit völlig Latte. Hauptsache ich muß nur an EINER Stelle was ändern. Hoffe jemand hat ne Lösung

Gruß Hendor <<-- :stupid:

SIM_BamBam_GP

Meister

Beiträge: 2 031

Beruf: GER

  • Nachricht senden

2

14.02.2006, 15:33

das einfachste waere indem du einfach alles in Grossbuchstaben speicherst egal wie jemand seinen Account schreibt

Hendor

Profi

  • »Hendor« ist der Autor dieses Themas

Beiträge: 1 097

Beruf: GER

  • Nachricht senden

3

14.02.2006, 15:42

Dies ist auch nicht erlaubt, da die Tabelle alle Arten von Accounts - z.B. auch Mailaccounts usw - enthält. Kundenwunsch heißt demnach:
"So wie ichs schreibe solls gespeichert werden, ausser wenns die Zeichenfolge eben schon gibt. Dann Fehlermeldung."

Ich liebe solche Anforderungen, aber was solls. Irgend eine Lösung gibts bestimmt oder?!

SIM_BamBam_GP

Meister

Beiträge: 2 031

Beruf: GER

  • Nachricht senden

4

14.02.2006, 15:44

dann nimm einen function based index

create unique index name_upper_idx on account(upper(name));

SIM_BamBam_GP

Meister

Beiträge: 2 031

Beruf: GER

  • Nachricht senden

5

14.02.2006, 15:55

achja, ansonsten kannst du natuerlich eine extra spalte einfuehren mit dem namen name_upper und da per trigger immer den namen in grossbuchstaben reinschreiben. hier legst du dann einfach einen unique index drauf. ich denke mal das duerfte die verbreiteste loesung sein

-=)GWC(RaMsEs

Erleuchteter

Beiträge: 5 098

Wohnort: Bamberg

Beruf: IT-ler

  • Nachricht senden

6

14.02.2006, 17:47

was macht ihr den? aua!

ahm, wie wärs den wenn du dir ne view baust die eine spalte account und eine spalte uaccount enthält? so ist das sauber.

und da prüfst du nach.

auch bei datenbanken gilt KISS

fast_tam

Profi

Beiträge: 1 013

Wohnort: Erding

Beruf: GER

  • Nachricht senden

7

14.02.2006, 18:07

du solltest das schon auf applikationsebene abfangen. eigentlich sollte die db ja den fehler garnicht werfen müssen, weil deine applikation im vorfeld schon auf gültigkeit prüft. fehlerbehandlung in die db zu verschieben ist .. mhm .. nicht so schön finde ich.

spätestens bei größeren sachen verfluchst du dich dafür ..

wenn dus wirklich in der db machen willst, was ich deinen aussagen entnehme mach es wie bambam sagt ^^
das mit den views versteh ich nicht. das bringt ihm imho nicht wirklich was ..

Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von »fast_tam« (14.02.2006, 18:10)

-=)GWC(RaMsEs

Erleuchteter

Beiträge: 5 098

Wohnort: Bamberg

Beruf: IT-ler

  • Nachricht senden

8

14.02.2006, 19:19

naja, du kannst eine view machen die zu jeder suchspalte eine zusätzliche spalte

upper(a.suchspalte) usuchspalte

enthält.

und mit der vergleichst du den uppercase wert den du eingegegeb hast. falls das fehlschlägt dann wird insertet.

lesend direkt auf tabellen zugreifen, das ist nicht gut. über jede tabelle kommt ein view und auf den wird zugegriffen.

ZwerG_DarkMan

Profi

Beiträge: 1 308

Wohnort: Würzburg

Beruf: GER

  • Nachricht senden

9

14.02.2006, 19:23

100% agree mit tam. Sowas muss zwingend in die Programmlogik und wird auf keinen Fall erst in der DB realisiert. Völlig unsauberer Stil sonst.

SIM_BamBam_GP

Meister

Beiträge: 2 031

Beruf: GER

  • Nachricht senden

10

14.02.2006, 19:27

@ramses, das schon klar, aber trotzdem wuerde das sein Problem an der Stelle nicht loesen, denn er muesste immer noch auf sich selbst schauen ;)

MaxPower

Erleuchteter

Beiträge: 7 452

  • Nachricht senden

11

14.02.2006, 19:39

edit: ok Problem falsch verstanden :)

Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von »MaxPower« (14.02.2006, 19:40)

-=)GWC(RaMsEs

Erleuchteter

Beiträge: 5 098

Wohnort: Bamberg

Beruf: IT-ler

  • Nachricht senden

12

15.02.2006, 12:00

hm warum?

ich würde eben vor dem insert schauen
SELECT COUNT ....
WHERE ( V_ACCOUNT.UACCOUNTNAME = 'accountname' )
.

und wenn das >0 ist dann gibts den namen schon,


aber das muss in der programmlogik sein, denn als anwender erwarte ich im fehlerfall eine anständige fehlermeldung. insofern muss ich ja ohnehin im code unterscheiden da ich hier die fehlermeldung benennen.

wie ich schon gesagt habe, komplizierte lösungen sind meistens falsch/unschön oder im besten fall schlecht zu debuggen.

KISS=Keep it simple and stupid. dann übersieht man auch keine möglichen fehlerquellen.

MfG_iLGenio

Profi

Beiträge: 894

Beruf: AUT

  • Nachricht senden

13

15.02.2006, 12:39

Kann man das nicht über eine Prozedur lösen die der Trigger auslöst?
z.B.:


CREATE OR REPLACE PROCEDURE checkID (UserLogin IN VARCHAR2) IS

temp_UserID VARCHAR2;
e1 EXCEPTION;

CURSOR c1 IS
SELECT name
FROM account acc
WHERE UPPER(acc.name) = UPPER(UserLogin);

BEGIN
OPEN c1;
IF (c1%NOTFOUND) THEN INSERT INTO account VALUES (UserLogin)
ELSE RAISE e1;
END IF;

EXCEPTION
WHEN e1 THEN raise_application_error(-20001,'Account schon vorhanden');
END;
/


CREATE OR REPLACE TRIGGER "eineindeutige_accountnamen" BEFORE
INSERT OR UPDATE OF "name" ON "account" FOR EACH ROW
BEGIN
checkID(:NEW.name);
END;


So hätte ich es gelöst wenn das vor 4 Wochen bei unserer Prüfung auf der Uni gekommen wäre :)

fast_tam

Profi

Beiträge: 1 013

Wohnort: Erding

Beruf: GER

  • Nachricht senden

14

15.02.2006, 13:31

@ramses:
eben, ich sag ja das muss in die programmlogik ;)
aber eben das will er ja verhindern - gesucht ist anscheinend ein quick n dirty hack. da hat irgendein programmierer wohl sehr gepfuscht wenn die registrierung an so vielen stellen im code zu ändern wäre .. x_X

nochwas: datenbanken sind datenbehälter. auch wenn moderne datenbanken stored procedures und dergleichen schnickschnack unterstützen ist das aus architektursicht eigentlich ein absolutes nono. eine persistenzschicht unterstützt das meist sowieso nicht und wer bei einem größeren projekt manuell mit der datenbank rumfrickeln will hat mein vollstes mitgefühl ;-)

Hendor

Profi

  • »Hendor« ist der Autor dieses Themas

Beiträge: 1 097

Beruf: GER

  • Nachricht senden

15

15.02.2006, 15:51

Wow....hier gehts ja richtig ab :respekt:

Es geht bei den "Accounts" nicht um Anmeldenamen oder dergleichen. Es sind einfach nur verschiedenste Daten(Mailaccount, Kostenstelle, Windowsaccount, Telefonaccount....) in einer Tabelle "Account", die durch eine "Labeltyp"-Spalte kategorisiert sind.

Die Idee mit der zusätzlichen Spalte hatte ich auch schon, allerdings bin ich kein Freund von zusätzlicher Datenhaltung, nur um ein logisches Problem (hier die Eindeutigkeit) zu erschlagen.
In ein paar Monaten ändert sich das Eindeutigkeitskriterium und schwups braucht man wieder ne neue Spalte.

Fehlerbehandlung gehört auf die Applicationsseite - keine Frage. Nur bin ich der Meinung das die Absicherung von Datenkonsistenz (auch mit erweiterten Anforderungen wie hier) in die Datenbank bzw direkt auf die Tabelle gehören, für die sie gelten sollen.
Wozu gibts sonst überhaupt den "Constraint"-Register bei der Tabellenverwaltung zum Beispiel um ein "unique" zu setzen?!
Eben deshalb, damit ich diese Kriterien an einer zentralen Stelle definiere und somit überschaubar halte und nicht in der gesamten Anwendung verteile.
Ist meine Meinung - sicher aber ein Frage der Sichtweise.

Zum genannten Vorschlag "View" ist zu sagen, dass für den View das gleiche gilt, wie für die geblockte Tabelle während der Transaktion.

Was mir am besten gefällt ist der Vorschlag von BamBam bzgl "function based index". Das meinte ich mit der Frage, ob man das "unique" nicht irgendwo genauer spezifizieren kann. Defaultmäßig unterscheidet "unique" ja leider zwischen Groß- und Kleinschreibung.
Leider habe ich das "create unique index name_upper_idx on account(upper(name));" nicht verstanden BamBam :stupid:
Was macht das genau? Kannst Du ein paar erklärende Sätze abgeben evtl ein Beispiel der Wirkung? Bitte möglichst einfach - bin kein Expert ;(

Eine letzte Idee, die mir gestern Abend kam, nachdem mir mein Handy beim rauchen vom Balkon gefallen ist (5.Stock).
Wie wäre es mit nem "after update" Trigger (dann kann ich nämlich die Tabelle wieder abfragen) der ein Rollback durchführt. Hab mal was von "atomic" in dem Zusammenhang gelesen, um direkt auf den letzten Step zu verweisen?!

SIM_BamBam_GP

Meister

Beiträge: 2 031

Beruf: GER

  • Nachricht senden

16

15.02.2006, 16:15

den after update kannst du in dem zusammenhang vergessen ;)

aehm was verstehst du an dem function based index nicht ? ok ich weiss nicht wirklich ob du den auch unique machen kannst, denn wir haendlen dein problem eher mit der extra spalte ab, aber du legst einfach einen index auf die von dir angesprochene spalte zusammen mit der function upper.
dh du hast einen index auf der Spalte wo alle Namen noch einmal als Upper gespeichert sind !!!

und was ein index ist solltest du doch schon wissen, denn einen unique constraint konntest du doch auch anlegen, oder ?

fast_tam

Profi

Beiträge: 1 013

Wohnort: Erding

Beruf: GER

  • Nachricht senden

17

15.02.2006, 16:20

mhm nuja. da gibt es wie du richtig erkannt hast verschiedene sichtweisen ;-)

meine sicht ist das ich diese constraints in der db nur als failsafe definiere, also als "letzte schutzfunktion" um meine daten nicht zu ficken.
den fehler bekommt in der regel sowieso nur die persistenzschicht mit, meine anwendung kann damit nichts anfangen -> ob die db jetzt meckert weil der insert fehlgeschlagen aufgrund eines UNIQUE fehlgeschlagen ist, oder ob ein NOT NULL verletzt ist äußert sich gleich ...

ein fehler ist eben ein fehler, also eine AUSNAHME!
man sollte Ausnahmebehandlungen niemals nie nicht dazu benutzen um definierte use-cases oder öfter auftretende fälle abzufangen.

natürlich ist es im weitesten sinne ein fehler wenn es den account schon gibt. aber das ist keine wirkliche ausnahme, der fall wird öfter vorkommen - also ist es schon ein teil business-logik.

einen fehler, eine exception macht stets aus das er nicht vorhersehbar ist. und wenn eine exception geschmissen wird dann brauch ich eine fehlerbehandlung, muss das log füllen, muss vielleicht versuchen irgendwas zu reperieren ... kurz:

ein fall wie "gibts schon" sollte von vornherein als mögliche verzweigung bedacht werden.

so und nun genug der theorie ^^

[edit: wenn aber der constraint in der DB verletzt wird ist es ein wirklicher FEHLER. das heisst nämlich die anwendung hat versagt. housten we have a problem... ]

Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von »fast_tam« (15.02.2006, 16:22)

SIM_BamBam_GP

Meister

Beiträge: 2 031

Beruf: GER

  • Nachricht senden

18

15.02.2006, 16:47

gut erklaert tam und ja du hast recht :)

plexiq

Profi

Beiträge: 1 512

Wohnort: Wien

  • Nachricht senden

19

15.02.2006, 16:54

Lösungsvorschlag:

du speicherst den Benutzernamen doppelt, also 2 Spalten:
1x Upper oder Lowercase Name mit Unique Constraint
1x "Case-Sensitive" Namen

Dann hast dein Unique Problem gelöst, und gleichzeitig hast die Benutzerdaten auch case sensitive vorliegen.

PS: Stilhalber is es, wie mehrfach erwähnt, wahrscheinlich besser du lagerst das in die Programmlogik aus.

PPS: Sollt mir die Threads ab und zu auch mal komplett durchlesen. Vorschlag gabs ohnehin schon ;) Function based unique index wurde auch schon vorgeschlagen...hm. Gibt nicht mehr viel zu sagen dazu :D

Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von »plexiq« (15.02.2006, 17:00)

Hendor

Profi

  • »Hendor« ist der Autor dieses Themas

Beiträge: 1 097

Beruf: GER

  • Nachricht senden

20

15.02.2006, 17:06

Problem mit "create unique index name_upper_idx on account(upper(name));" gelöst. Macht genau was ich wollte. Danke an alle!

Immer wieder erhebend, wenn man bei solchen Anlässen merkt, wie wenig Ahnung man im Vergleich zu anderen hat ?(

-=)GWC(RaMsEs

Erleuchteter

Beiträge: 5 098

Wohnort: Bamberg

Beruf: IT-ler

  • Nachricht senden

21

15.02.2006, 17:36

Zitat

Original von plexiq
Lösungsvorschlag:

du speicherst den Benutzernamen doppelt, also 2 Spalten:
1x Upper oder Lowercase Name mit Unique Constraint
1x "Case-Sensitive" Namen



genau das mag ich nicht. ich versuche keine generierbaren sachen in die db zu legen ( und ein uppercase des namens ist generierbar). Dafür gibts ja views. aber ist eben geschmackssche^^

plexiq

Profi

Beiträge: 1 512

Wohnort: Wien

  • Nachricht senden

22

15.02.2006, 18:08

Ich würds auch net so machen. War nur das erste, was mir zu der Problemstellung eingefalln is. (Zusammen mit unique index) Aber mein Post war sowieso zu 100% useless, wie man an meinen PS's sehn kann ;)