tiko setup.adp da- ten - robertkrah.de 2000 teil 3.pdf · die beispieldatenbank tiko besteht aus...

M i c r o s o f t S Q L - S e r v e r 2 0 0 0 A n l e i t u n g

14 Beispieldatenbank Tiko installieren Bei der Datenbank Tiko handelt es sich um eine komplexe Datenbank 1, in der Artikel-, Kunden- und Lieferantenbeziehungen erfasst sind. Der Name der Datenbank, Tiko, ist aus den Anfangs-buchstaben der Buchautoren (siehe Fußnote) gebildet. Um diese Datenbank zu installieren, muss zunächst im SQL Server – Enterprise Manger die Datenbank als solche erstellt werden.

Aufgabe: Richten Sie im Enterprise Manager die leere Datenbank Tiko ein. Kopieren Sie vom Public-Ordner aus die Datei TIKO_Setup.adp und die Datei Da-ten.mdb Legen Sie jetzt nach folgender Anweisung die Datenbank an:

1. Öffnen Sie die Datei TIKO_Setup.adp. Wenn das erste mal der Anmelden-Dialog erscheint, klicken Sie auf Abbrechen.

2. Klicken Sie auf die Schaltfläche Ver-bindung und wählen Sie im Dialog Datenverknüpfungseigenschaften den Server und die zuvor erstellte Datenbank aus. Testen Sie die Ver-bindung und klicken Sie danach auf OK.

3. Tragen Sie den Pfad zur Datei DATEN.mdb ein oder wählen Sie ihn über die Schaltflä-

che [...] aus.

4. Klicken Sie auf die Schaltfläche Objekte anlegen, um die Beispielanwendung zu erstel-len. Unten in der Stauszeile sehen Sie, wie die Daten eingespielt werden. Dann wird gemeldet:

5. Beenden Sie das Setup über die Schaltfläche Beenden.

1 Tiemeyer, Konopasek, Unternehmensdatenbanken mit MS SQL Server und Access, München 2001 (Verlag Matrkt und Technik)

- 60 -


6. Öffnen Sie nun noch die Tabelle tblArtikel in der Entwurfsansicht. Markieren Sie die erste Zeile (ArtNr). aktivieren Sie das Kontrollkästchen in der Spalte Identität. Als ID-Ausgangswert tragen Sie 1001 und als ID-Schrittweite 1 ein. Speichern Sie die Änderung danach.

7. Wiederholen Sie den Vorgang mit der Tabelle tblBestellungen. Vergeben Sie für

die Spalte BestNr eine Identität mit dem Startwert 1000 und einer Schrittweite von 1. Speichern Sie auch diese Änderungen.

(Diese Änderung ist notwendig, da, wenn diese Einstellung von vorneherein getroffen wird, die Beispieldatensätze vom Setup-Programm nicht eingespielt werden können.

14.1 Zur Datenbank TIKO2 Die Beispieldatenbank TIKO besteht aus mehreren Tabellen, die alle miteinander in Beziehung stehen. Um Ihnen einen besseren Überblick über die Gesamtlösung zu verschaffen, haben wird einige Teildiagramme erstellt, die wir Ihnen in diesem Anhang erläutern. Artikel In der Tabelle tbIArtikel sind die Artikelstammdaten gespeichert. Da jeder Artikel von einem bestimmten Lieferanten geliefert wird, ist die Artikeltabelle mit der Tabelle tbILiefe-ranten verknüpft. Die Tabelle tbILieferanten enthält sämtliche Lieferantenstammdaten. Jeder Artikel ist einer Artikelgruppe zugeordnet. einzelne Artikel können zu Sets zusammengefasst werden, um als solche gemeinsam verkauft zu werden. Die Lagerstände der einzelnen Artikel sind in der Tabelle tbILagerstand ersichtlich. Diese ist mit der Tabelle tbILager verknüpft. Lieferantenbeziehungen Bestellungen, die bei Lieferanten aufgegeben werden, werden in der Tabelle tbIBestellungen gespeichert. Um zu dokumentieren, an welchen Lieferanten eine Bestellung geschickt wird, ist die Tabelle tbIBestellungen mit der Tabelle tbILieferanten verknüpft.

2 gekürzt aus: Tiemeyer, Konopasek, Unternehmensdatenbanken Mit MS SQL Server und Access, S. 530 ff

- 61 -


Außerdem wird in jeder Bestellung gespeichert, von welchem Mitarbeiter sie erstellt worden ist. Deshalb ist die Tabelle tbIBestellungen mit der Tabelle tbIPersonal verknüpft. Auf jede Bestellung folgt später ein Wareneingang. Dieser wird in der Tabelle tbIWareneingang erfasst. Die gelieferten Artikel werden in der mit dieser Tabelle verknüpften Tabelle tbIWare-neingangPositionen erfasst, die wiederum mit der Tabelle tbIArtikel verknüpft ist. Auch beim Wareneingang wird protokolliert, welcher Mitarbeiter die Waren übernommen hat. Deshalb ist die Tabelle tbIWareneingang mit der Tabelle tbIPersonal verknüpft. Um die Zuordnung zum Lieferanten abzubilden, ist die Tabelle tbIWareneingang auch mit der Tabelle tbILieferanten verknüpft. Aufträge, Lieferscheine und Rechnungen Kunden können Aufträge erteilen. Aus diesen Aufträgen können entweder zuerst Lieferscheine und danach Rechnungen resultieren oder direkt Rechnungen erstellt werden. Wenn eine Kunde direkt im Geschäft etwas kauft, kann das entweder sofort fakturiert werden oder es wird vorerst ein Lieferschein erstellt. Da es so viele verschiedene Abläufe gibt, die aufeinander folgen können - und später auch nachvollziehbar sein müssen -, sind die Tabellen tbIAuftrag und tbIAuftragPosition, die Tabellen tbILieferschein und tbILieferscheinPosition sowie die Tabellen tbIRechnungen und tbIRechnun-genPosition untereinander verknüpft. Zur Vereinheitlichung der Schreibweisen in der Korrespondenz werden Informationen wie Brief-kopf und Briefanrede in der Tabelle tbIAnreden gespeichert und dem Geschlecht des Kunden zugeordnet. Personal Alle Informationen über die Mitarbeiter werden in der Tabelle tbIPersonal gespeichert. Diese ist mit der Tabelle tbIAbteilungen verknüpft, um die Mitarbeiter den Abteilungen, in denen sie arbei-ten, zuordnen zu können. Um bei diversen Vorgängen nachvollziehen zu können, von welchem Mitarbeiter sie durchgeführt wurden, wird die Personalnummer in den entsprechenden Tabellen vermerkt. Daraus ergeben sich Beziehungen der Tabelle tbIPersonal zu den Tabellen tbIBestel-lungen, tbIWareneingang, tbIAuftrag, tbILieferscheine und tbIRechnungen. Auf den folgenden Seiten sehen Sie jeweils Ausschnitte aus dem noch zu erstellenden Dia-gramm.

- 62 -


- 63 -


- 64 -


- 65 -


Aufgabe: Erstellen Sie folgende Sichten für die TIKO-Datenbank:

1. In einer Sicht soll erfasst werden, welcher Lieferant welche Artikel liefert. (Speichern als: Sicht1)

2. Nur solche Lieferanten sollen durch die Sicht (Name: Sicht2) gespeichert werden, die Artikel aus der Gruppe Heimwerken liefern (wichtig: hier sollen die Lieferanten jeweils nur einmal aufgelistet werden.

3. Wie viele Artikel sind gespeichert? (Name: Sicht3)

4. Ermitteln Sie die Kunden, die einen akademischen Grad haben (Name: Sicht4).

5. Es sollen die Artikel erfasst werden, die nach dem 20.2.2000 in den Wareneingang ge-kommen sind (Name: Sicht5) .

6. Machen Sie in einer Auflistung deutlich, an welchen Orten Sie Kunden haben. (Name: Sicht6)

7. Welche Kunden haben Interesse an welchen Interessengebieten, z, B. Küche und Ko-chen? (Name: Sicht7)

8. Ermitteln Sie die Summe des Bestellwertes aus Bestellnr 1001 (Name: Sicht8) eine Lösungsmöglichkeit sehen Sie auf der nächsten Seite – mit etwas SQL geht es allerdings schneller! Lösung: 2448,54

9. Ermitteln Sie mit Sicht10, welche gesamte Menge im Wareneingang zu folgendem Arti-kel verbucht wurde: Arbeitsmantel Blau (Achtung: gibt es in verschiedenen Größen) Lösung: 660

10. Welche Bestellungen sind am 4.1.2000 herausgegangen? (Name: Sicht11)

11. Ermitteln Sie jetzt aus Sicht11, welchen Wert in Euro die Bestellungen vom 4.1.2000 hatten. (Name: Sicht12)

- 66 -


Mögliche Lösung zu Aufgabe 8: Erst Sicht8 so erstellen:

Dann eine Sicht9 erstellen, die die Summe der vorher ermittelten Werte liefert:

Aufgabe: Sehen Sie sich die Codierung von Sicht5 im Query Analyzer an.

- 67 -


15 Access als Frontend für die Datenbank Tiko Access soll für die Datenbank TIKO als Frontend verwendet werden, um von einem Client auf die Datenbank zuzugreifen. Dabei sind die Möglichkeiten von Access, z. B. Formulare und Be-richte zu erstellen, wichtig. Gehen Sie folgendermaßen vor:

1. Geben Sie in Access den Befehl Datei Neu

2. Verwenden Sie die Schaltfläche 3. Füllen Sie das Dialogfeld so aus, wie es Ihrem Server (Servernamen) entspricht:

Jetzt haben Sie von Access aus den Zugriff auf die Objekte der Datenbank:

Aufgabe: Speichern Sie das Projekt mit dem Namen Tiko.adp.

- 68 -


16 Prozeduren Im SQL Server werden sogenannte stored procedures (gespeicherte Prozeduren) verwendet. Darin werden kompilierte Transact-SQL-Anweisungen verwendet. Die Tatsache, dass Prozedu-ren kompiliert sind, hat den Vorteil, dass sie schneller ablaufen, da sie nicht bei jedem Aufruf neu übersetzt werden müssen. Allerdings hat die Tatsache, dass sie kompiliert werden, den Preis, dass der Code fehlerfrei sein muss, denn die Kompilierung bedeutet ja, dass die gesamte Prozedur übersetzt wird. In ihrer einfachsten Form sind Prozeduren wie Sichten, also gespeicherte Abfragen, zu ver-wenden. Sie werden im Query Analyzer über den Befehl create procedure <Name> erstellt . Es ist üb-lich, das Präfix sp_ für stored procedure) voranzustellen (auf den Enterprise Manager und die Erstellung über Access kommen wir später zurück).

Aufgabe: Erstellen Sie als erstes Beispiel folgende Prozedur. Starten Sie dazu den Query Analyzer und stellen Sie die datenbank orders ein:

Wenn Sie F5 drücken, um die Prozedur zu erstellen, geschieht scheinbar nichts, auch wenn Sie die Meldung sehen: Befehl(e) erfolgreich abgeschlossen. Der Grund liegt darin, dass die Prozedur nicht ausgeführt wird, sondern zunächst nur gespei-chert wird.

16.1 Ausführen der Prozedur Um die Prozedur auszuführen, benötigen Suie das Befehlswort exec (=execute, Ausführen) Aufgabe: Geben Sie im Query Analyzer ein:

exec sp_sumpreis

Sie erhalten als Ergebnis:

(Anmerkung: der Query Analyzer führt die Prozedur auch ohne das Wort exec aus, also nur: sp_sumpreis eingeben und F5 drücken funktioniert auch)

16.2 Text der Prozedur anzeigen lassen Wenn Sie sich eine voeher erstellte Prozedur ansehen wollen, dann brauchen Sie den Befehl sp_helptext. Wenn Sie im Query Analyzer eingeben:

und anschließend wieder F5 drücken, dann folgt unten die Ausgabe:

- 69 -


16.3 Über den EnterpriseManager auf die Prozedur zugreifen Wenn Sie den Enterprise Manager verwenden, dann können Sie über zuerst die Datenbank öffnen und dann auf die Prozeduren zugreifen:

Sie sehen dann Ihre vorher erstellte Prozedur:

mit einem Doppelklick können Sie auch hier in die Syntax der Prozedur einsehen:

16.4 Prozedurengruppe erstellen Sie können eine Gruppe ge-speicherter Prozeduren erstel-len, die Sie nach Bedarf über die Eingabe der Nummer auf-rufen können. Verwenden Sie für das folgende Beispiel die Datenbank TIKO:

Aufgabe: Rufen Sie nun einzeln die Prozedur durch den Namen, gefolgt von einem Semikolon und der Nummer, auf: sp_ArtGruppe;3

- 70 -


16.5 Stored procedures vom Enterprise Manager aus anlegen Im Enterprise Manager können Sie eine Prozedur erstellen, wenn Sie die Daten-bank aktivieren, dann auf Gespeicherte Prozeduren gehen und dort mit der rech-ten Maustaste den Befehl Neue gespeicherte Prozedur aufrufen: Aufgabe: Erstellen Sie die folgende Prozedur mit dem Namen sp_dat:

Das aktuelle Datum wird durch die Funktion getdate() aufgerufen. Um das Format in der Form TT.MM.JJJJ auszugeben, wird zusätzlich die Anweisung convert() verwendet. Eine sinnvolle Möglichkeit besteht hier darin, die Syntax zu überprüfen. Wie vorher schon er-wähnt, kann nur gespeichert werden, wenn die Syntax komplett richtig ist. Mit OK wird die Prozedur gespeichert.

Aufgabe: Rufen Sie jetzt die Prozedur im Query Analyzer auf:

16.6 Prozeduren mit Übergabeparametern Ähnlich wie Parameterabfragen in Access dienen Übergabeparameter dazu, beim Aufruf der Prozedur bestimmte Eingaben vom Benutzer zu verlangen und diese zu übergeben. Ein Bei-spiel soll dies zeigen.

Aufgabe: Erstellen Sie die folgende Prozedur sp_Artpreis

create procedure sp_artpreis ( @artikel int ) as select ArtVKPreis as Preis from tblartikel where ArtNr=@Artikel return

- 71 -

mailto:ArtNr=@Artikel


Wenn Sie die Prozedur im Query Analyzer aufrufen, erhalten Sie allerdings eine Fehlermel-dung: Server: Nachr.-Nr. 201, Schweregrad 16, Status 4, Prozedur sp_artpreis, Zeile 0

Die sp_artpreis-Prozedur erwartet den @artikel-Parameter; dieser wurde nicht übergeben.

Mit dem Parameter kann der Query Analyzer nichts anfangen, deshalb diese Meldung. Es ist erforderlich, die Prozedur durch Access als Frontend zu verwenden.

Aufgabe: Öffnen Sie die vorher (S. 68) erstellte Tiko.adp-Datei in Access. Sie sehen hier die gespeicherten Prozeduren:

Wenn Sie die Prozedur sp_artpreis hier aufrufen, erfolgt die Abfrage des Artikels: Geben Sie als Beipiel die Nr. 1005 ein:

Dann wird der Artikelpreis dieses Artikels aufgerufen.

16.7 Prozeduren von Access aus erstellen Wenn Sie Prozeduren direkt von Access aus erstellen möchten, dann wählen Sie im geöffneten

Projekt unter den Befehl Neu. Der „Designer“ zeigt dann:

Nach der Zeile create procedure werden Variablen eingefügt, die hier aber - als Muster- in Kommentarzeichen eingefügt sind. Die eigentlichen Befehlszeilen werden zwischen As und return gesetzt.

Die Anweisung set nocount on werden SQL-Meldungen unterdrückt, die zum Beispiel dann er-folgen, wenn Daten geändert wurden. Die Verwendung dieser Zeile bringt Geschwindigkeitsvor-teile und kann deshalb immer verwendet werden.

- 72 -


16.8 Variablen und Datentypen Wie in jeder Programmiersprache werden Variablen verwendet, wenn z. B. Benutzereingaben verarbeitet werden. Die dazu verwendeten Datentypen sind dieselben wie die verwendeten Felddatentypen (varchar, int usw.), deshalb bedürfen diese hier keiner weiteren Erklärung. In Bezug auf die Variablen gilt folgende Unterscheidung: 1. Benutzerdefinierte Variablen: Der Name dieser Variablen beginnt immer mit einem @ . Sie werden mit der declare erzeugt.

2. Globale Variablen. Deren Name beginnt mit zwei @@ . Die Inhalte dieser Variablen sind systemdefiniert . Wichtig: Wenn es sich um Übergabeparameter handelt (wie im Beispiel sp_Artpreis,

S. 71 unten), dann entfällt das Wort declare . Wenn mehrere Variablen deklariert werden, dann werden sie durch ein Komma getrennt. Sinn-vollerweise (aber nicht wirklich erforderlich) erfolgt dazwischen ein Zeilenschaltung. Beispiel1:

create procedure sp_test_bsp zwei Übergabeparameter, die der Prozedur beim Aufruf mit-übergeben werden

( @var1 as int, @var2 as varchar(10) ) as....

Beispiel2: create procedure sp_beispiel ( @var1 as int, ) as declare @netto samllmoney declare @brutto smallmoney ...

16.9 Wertzuweisungen in Variablen Wenn Variablen deklariert werden, dann wird zunächst der Wlen einen anderen Wert zu geben, verwendet man den seBefehl. Am Beispiel: set @var1=wert oder select @var1=wert oder set @var1=(select wert from ...) oder select @var1=wert from ...

Wenn der Wert über eine select-Abfrage zugewiesen wird, dansein, dass sie der Variablen genau einen Wert zuweist, ansonsUm zu zeigen, wie mit einer Variablen, ei-nem Ergebnis aus der select-Abfrage und einer weiteren Berechnung verfahren wer-den kann, soll eine Prozedur sptest1 mit Access erstellt werden.

- 73 -

ein Übergabeparameter @var1zwei Variablen @netto und @brutto

ert 0 zugewiesen. Um der Variab-t oder den bekannten select-

n muss die Abfrage so formuliert ten erfolgt eine Fehlermeldung.


Hier wird zunächst die Variable @wert deklariert. In diese Variable wird das Ergebnis der se-lect-Abfrage eingelesen, die den Verkaufspreis des Artikels mit der Nummer 1005 ermittelt. Dann wird der Wert ausgegeben (mit select @wert). Die Ausgabe ist:

Expr<1> 7,52 DM

Nun soll die Prozedur so verändert werden, dass der Wert mit drei multipliziert wird. Außerdem soll statt Expr<1> die Überschrift Verkaufspreis vergeben werden.

Aufgabe: Verändern Sie die Prozedur so: Alter Procedure sptest1 As declare @wert smallmoney set nocount on set @wert=(select artvkpreis from tblartikel where artnr=1005) *3 select @wert as Verkaufspreis return

Die Ausgabe ist:

Verkaufspreis22,56 DM

Zu beachten ist bei der Syntax, dass der Aliasname Verkaufspreis nicht in der ersten select-Zeile erfolgen kann: falsch: set @wert=(select artvkpreis as Verkaufspreis from tblartikel where artnr=1005) *3 select @wert

Aufgabe: Nun soll die Prozedur so geändert werden, dass über einen Parameter erst nach der Artikelnummer gefragt wird. Anschließend soll der Verkaufspreis, die Mehrwertsteuer und der Bruttowert ausgegeben werden. Der Name soll sp_preis sein.

Die Ausgabe ist (bei der Artikelnummer 1187)

- 74 -


16.10 Lagerbuchung vornehmen Die Beziehung zwischen den Tabellen tblArtikel und tblLager ist über eine Zwischentabelle Lagerstand realisiert:

Diese Tabellen sollen als Beispiel dienen, um zu zeigen, wie eine if-Verzweigung einzusetzen ist.

Aufgabe: Erstellen Sie zunächst die Prozedur splager1, mit der eine Lagerstandsbuchung in der einfachsten Form durchzuführen ist.

create Procedure spLager1 ( @artikel As int, @lager As tinyint, @menge As int ) As UPDATE tblLagerstand SET LagMenge=LagMenge + @menge WHERE ArtNr = @artikel AND LagNr = @lager return

Hier werden drei Übergabeparameter eingesetzt, die die Artikelnummer, die Lagernummer und die Menge abfragen. In der Tabelle wird der Datensatz gesucht, wo die Artikelnummer und die Lagernummer dem abgegebenen Wert entspricht.

Aufgabe: Führen Sie eine Lagerbuchung für ArtNr 1005 und LagNr 2 durch (Erhöhung der

Menge um 10). Öffnen Sie dann die Tabelle, um sich vom Erfolg der Operation zu überzeugen.

16.11 IF-Verzeigung verwenden Die aus anderen Programmiersprachen bekannte if-Verzweigung ist in Transact-SQL so einzu-setzen:

steht als Anweisung mehr als eine Befehlszeile, muss Begin und end zur Abgrenzung der Anweisung einge-setzt werden.

IF BEDINGUNG Anweisung Else Anweisung

- 75 -


Die If-Verzweigung soll nun für den Fall eingesetzt werden, dass für einen Artikel eine Lagerbe-standsbuchung durchgeführt werden soll, für den es eventuell gearde noch keinen Datensatz in der Lagerstandstabelle gibt. Die Prüfbedingung dafür ist: IF EXISTS(SELECT ArtNr FROM tblLagerstand WHERE ArtNr=@artikel AND LagNr=@lager)

In diesem Fall ist die update-Anweisung wie vorher einzusetzen, andernfalls muss der insert-Befehl folgen, um einen neuen Datensatz einzufügen: INSERT INTO tblLagerstand (ArtNr, LagNr, LagMenge) VALUES (@artikel, @lager, @menge, @reserviert)

Die komplette Prozedur lautet dann: create Procedure spLager2 @artikel As int, @lager As tinyint, @menge As int As set nocount on IF EXISTS(SELECT ArtNr FROM tblLagerstand WHERE ArtNr=@artikel AND LagNr=@lager) UPDATE tblLagerstand SET LagMenge=LagMenge + @menge WHERE ArtNr = @artikel AND LagNr = @lager ELSE INSERT INTO tblLagerstand (ArtNr, LagNr, LagMenge) VALUES (@artikel, @lager, @menge) return

Aufgabe: Erstellen Sie diese Prozedur splager2 und führen Sie eine Buchung für den folgen-den Artikel durch:

Für den Artikel 1005 gab s vorher in LagNr1 kei-nen Datensatz

ArtNr LagNr LagMenge LagReserviert1005 1 44 0

Wenn Sie die Prozedur aufgerufen haben und die Übergabewerte für die Parameter eingetra-gen haben, bringt Access die folgende Meldung:

Um dies zu unterdrücken und zu sehen, ob eine Buchung durchgeführt worden ist, soll eine Erweiterung vorgenommen werden. Dazu kann eine interne Variable namens @@rowcount verwendet werden. Damit wird gezählt, wieviele Datensätze von einer Operation betroffen wa-ren. Im Falle, dass ein Datensatz betroffen ist, müsste diese Variable den Wert 1 zurückgeben.

Aufgabe: Öffnen Sie die Prozedur splager2 in der Entwurfsansicht und fügen Sie die folgende Zeile als vorletzte Zeile ein (vor die return-Anweisung)

select @@rowcount as erfolgreich

- 76 -


Löschen Sie dann noch einmal den vorher erstellten Datensatz ArtNr LagNr LagMenge LagReserviert1005 1 44 0

und rufen Sie dann die Prozedur noch einmal auf, um die Daten noch einmal einzutragen. Dann meldet die Prozedur zum Abschluss: Erfolgreich

1 Aufgabe: Die nächste Erweiterung der Prozedur soll noch darin bestehen, dass das System

meldet, ob eine Neuanlage erfolgt ist oder nicht, ob also der if-Zweig mit dem up-date-Befehl oder der else-Zweig mit dem insert-Befehl durchgeführt wurde. Dazu wird eine Variable neuanlage mit dem Datentyp bit (boolean zur Aufnahme von Wahrheitswerten true/false) deklariert. Diese Variable wird dann im if-Zweig mit 0 gefüllt (falsch, kein neuer Datensatz, da man mit update den bestehenden Daten-satz ändern konnte) bzw. im anderen Fall mit 1 für wahr, da hier dann mit insert ein neuer Datensatz angelegt wurde.

Die komplette Prozedur splager3 mit den Änderungen lautet dann:

Sie erhalten als Meldung, wenn Sie einen Datensatz neu eintragen:

Erfolgreich Neuanlage 1 Wahr

Aufgabe: Ändern Sie jetzt beim Artikel 1005. Lager 1, den Lagerwert. Dann muss die Meldung sein:

Erfolgreich Neuanlage 1 Falsch

- 77 -


16.12 Aufgaben und Beispiele zu Prozeduren 1. Erstellen Sie eine Prozedur mit dem Namen spPreisneu, mit dem der Preis eines Artikels um einen anzugebenden Prozentsatz erhöht wird. Lösung: (erste Version) Sie benötigen zwei Übergabeparameter für die Artikelnummer und den Prozentsatz. Dann erfolgt das update in der gewohnten Form. Alter Procedure spPreisNeu @artikel int, @prozent decimal(4,2) As set nocount on update tblArtikel set ArtVKPreis=ArtVKPreis+ArtVKPreis*@prozent/100 where Artnr=@artikel return

Die Prozedur bringt zum Schluß wieder die Meldung:

Wenn stattdessen zum Schluss der neue Preis genannt werden soll, dann muss zuerst noch eine neue Variable deklariert werden: declare @Neupreis smallmoney

Nach dem update muss der select-Befehl den Wert in die Variable einlesen: select @Neupreis=(select ArtVKPreis from tblArtikel where ArtNr=@Artikel) select @Neupreis as NeuerPreis

Dann kann die Ausgabe so erfolgen: NeuerPreis

11,01 DM Alter Procedure spPreisNeu2 @artikel int, @prozent decimal(4,2) As declare @Neupreis smallmoney set nocount on update tblArtikel set ArtVKPreis=ArtVKPreis+ArtVKPreis*@prozent/100 where Artnr=@artikel select @Neupreis=(select ArtVKPreis from tblArtikel where ArtNr=@Artikel) select @Neupreis as NeuerPreis return

- 78 -


Aufgabe: Schreiben Sie eine Prozedur, mit der Sie die Telefonnummer eines Angestellten ändern können. Die Tabelle hat den Namen tblPersonal, Sie benötigen die PersNr und das Feld PersTelefon.

Alter Procedure "spTelefon" @Persnr int, @perstelefon varchar(20) As set nocount on update tblPersonal set Perstelefon= @PersTelefon where Persnr=@persnr return

Aufgabe: Schreiben Sie die Prozedur so um, dass diese Meldung nicht erscheint:

17 Transaktionssteuerung3 Transaktionen sind Vorgänge, die entweder alle gemeinsam durchgeführt werden oder wieder zurückgenommen werden. Sie dienen der Sicherung der Datenintegrität, denn ein Vorgang als Ganzes ist nur dann vollständig, wenn alle Einzelschritte, die zur Durchführung erforderlich sind, vorgenommen werden konnten. Transaktionen werden mit der Anweisung Begin Transaction

eingeleitet. Wenn alle Vorgänge durchgeführt wurden, wird der Befehl Commit Transaction

gegeben. Das Zurückführen auf den frühenen Stand erreichen Sie mit Rollback Transaction

Dies geht natürlich nur, wenn noch nicht der Befehl commit transaction gegeben wurde. Das Schlüsselwort Transaction kann mit tran abgekürzt werden.

Aufgabe: Starten Sie für die nächsten Operationen den Query Analyzer, mit dem Sie Befehle nacheinander geben können und nicht (wie in Access) nur Befehle in einer Prozedur „am Stück“ abarbeiten können

In der Datenbank TIKO befindet sich eine Tabelle tbltestartikel, mit dem das Prinzip der Transaktionssteuerung nachvollzogen werden soll.

3 siehe hierzu wieder Tiemeyer, Konopasek, Unternehmensdatenbanken mit MS SQL Server und Access, S. 335 ff

- 79 -


In dieser Tabelle sind nur die ersten 10 Datensätze der Tabelle tblArtikel vorhanden, und au-ßerdem der besseren Übersicht halber nur die Felder ArtNr, ArtBezeichnung, ArtGruppe und ArtVKPreis. Tabelle tbltestartikel

Zunächst wird ein Befehl ohne Transaktion durchgeführt: Aufgabe: Ändern Sie den Preis des Artikels mit der Nummer 1001 auf 11 Euro:

update tblTestartikel set ArtVKPreis =11 where ArtNr=1001

Überprüfen Sie das Ergebnis: select * from tblTestartikel where ArtNr=1001

Das Ergebnis ist: Diese Änderung kann nicht mehr mit dem Befehl rollback tran rückgängig gemacht wer-den, denn es ist ja keine Transaktion gestartet worden.

Aufgabe: Jetzt soll explizit eine Transaktion gestartet werden. Geben Sie im Query Analyzer ein: Begin tran

Aufgabe: Dann sollen alle Artikel, die mehr als 10 Euro kosten, gelöscht werden: DELETE FROM tblTestartikel

WHERE ArtVKPreis > 10

Zusätzlich soll ein Artikel eingefügt werden: INSERT INTO tblTestartikel (ArtNr, ArtBezeichnung, ArtGruppe, ArtVKPreis) VALUES (1011, 'Kochtopf 10 Liter','KG', 43.99)

Wenn Sie jetzt alle Artikel ansehen, dann erhalten Sie dieses Ergebnis (SELECT * FROM tblTestartikel ORDER BY ArtNr): Es sind drei Zeilen gelöscht, und ein Artikel ist eingefügt wor-den.

- 80 -


Aufgabe: Geben Sie jetzt ein: ROLLBACK TRAN

Und lassen Sie dann wieder alle Artikel anzeigen, dann ist wieder das Ergebnis von Beginn zu sehen.

Aufgabe: Jetzt soll eine neue Transaktion gestartet werden, die zum Ende mit commit bestä-tigt wird (und deshalb dann nicht zurückgenommen werden kann)

Zunächst soll alle Preise um 5% erhöht werden: UPDATE tblTestartikel SET ArtVKPreis = ArtVKPreis * 1.05

Dann sollen die Artikel aus der Gruppe Haushalt gelöscht werden: DELETE FROM tblTestartikel

WHERE ArtGruppe = 'HH'

Dann soll schließlich der Artikel „Kochtopf“ wieder eingefügt werden: INSERT INTO tblTestartikel (ArtNr, ArtBezeichnung, ArtGruppe, ArtVKPreis)

VALUES (1011, 'Kochtopf 10 Liter','KG', 43.99)

Bestätigen Sie dann die Transaktion: COMMIT TRAN

Natürlich ist jetzt der Befehl rollback tran nicht erfolgreich.

17.1 Savepoint setzen Wenn Sie Transaktionen nur bis zu einem bestimmten Punkt zurücknehmen wollen, dann kön-nen Sie sogenannte Savepoints setzen. Verwenden Sie den Befehl: save tran savepointname

Später können Sie dann bis zu einem Savepoint zurück, wenn Sie den Befehl geben: rollback tran savepointname

18 Cursor Mit einem Cursor haben Sie die Möglichkeit, in der Datenbanktabelle zeilenweise vorzugehen. Sie können mit einem Cursor eine definierte Menge von Zeilen einer Tabelle verarbeiten. Inner-halb dieser Zeilen können Sie sich vor- und zurückbewegen. Dies ist dann sinnvoll, wenn eine bestimmte Aktion auf mehrere Datensätze hintereinander angewandt werden soll. Ein Beispiel wäre gegeben, wenn Sie für alle säumigen Kunden eine Mahnung erstellen wollen. Dazu wird mit einem Cursor gearbeitet, der alle Rechnungen enthält, die einer über das Datum gesteuerten Bedingung genügen. Die in Frage kommenden Datensätze werden dann abgear-beitet, und es wird eine Mahnung für jeden Datensatz erstellt.

18.1 Cursor deklarieren Ein Cursor wird mit der Anweisung declare eröffnet.Dann folgt eine select-Anweisung, die die Datensätze bestimmt, auf die der Cursor zutreffen soll. Die allgemeine Syntax für den Cursor lautet: declare cursor_name [insensitive] [scroll] Cursor

for select_anweisung

for read only | for update [of Spalten_Name]

Das Deklarieren eines Cursors mit dem Schlüsselwort insensitive hat zwei Auswirkungen:

1. Der Cursor gibt keine Änderugen an den zugrundeliegenden Daten wieder, die durch andere Benutzer oder Prozesse vorgenommen werden

- 81 -


2. der insensitive Cursor verwendet eine temporäre Kopie der Daten. Deshalb ist er schreibgeschützt, und an den ausgewählten Daten können keine Änderungen vorge-nommen werden.

18.2 Cursor öffnen Erst beim Öffnen eines vorher deklarieretn Cursors erfolgt der Zugriff auf die Daten. Das Öffnen geschieht mit der Anweisung Open cursor_name

18.3 Zeilen aus einem Cursor abrufen Daten aus dem Cursor werden mit der Anweisung Fetch Zeile für Zeile abgerufen. Die Syntax dazu lautet: fetch [next | Prior | First | Last | absolute n | relative n]

from cursor_name

[into @locale_variable1, @locale_variable2, ...]

Die Standardvariante ist fetch next, um die nächste Zeile abzurufen. Wenn man fetch next das erste Mal verwendet, wird der erste Datensatz abgerufen.

18.4 Cursor schließen Ein nicht mehr benötigter Cursor kann mit der Anweisung close wieder geschlossen werden. Ein solcher Cursor kann jederzeit mit open wieder geöffnet werden.

Wenn ein Cursor überhaupt nicht mehr benötigt wird, dann kann er mit der auch gelöscht wer-den. Dazu benötigen Sie die Anweisung deallocate . Die Syntax dieser beiden Befehle ist: close cursor_name

deallocate cursor_name

18.5 Beispiel: Lagerbuchung mit Set-Artikeln Die vorher erstellte Prozedur splager3 (Seite 77) soll hier noch einmal aufgegriffen werden. Sie ist eine vereinfachte Version der Prozedur splagerbuchung, die hier noch einmal gezeigt wird. Die Erweiterung zur vorher verwendeten Prozedur besteht darin, dass auch die reservierte Menge mit berücksichtigt wird. Alter Procedure spLagerbuchung @artikel As int, @lager As tinyint, @menge As int, @reserviert As int As set nocount on IF EXISTS(SELECT ArtNr FROM tblLagerstand WHERE ArtNr=@artikel AND LagNr=@lager) UPDATE tblLagerstand SET LagMenge=LagMenge + @menge, LagReserviert=LagReserviert + @menge WHERE ArtNr = @artikel AND LagNr = @lager ELSE INSERT INTO tblLagerstand (ArtNr, LagNr, LagMenge, LagReserviert) VALUES (@artikel, @lager, @menge, @reserviert) if @@error<>0 INSERT INTO tblTestartikel SELECT ArtNr, ArtBezeichnung, ArtGruppe, ArtVKPreis FROM tblArtikel WHERE ArtNr>1800 SELECT @@rowcount As Erfolgreich return

Die Erweiterung, die nun vorgenommen werden soll, um die Verwendung eines Cursors zu er-klären, ist die Tatsache, dass in der Datenbank Tiko sogenannte Set-Artikel vorgesehen sind. Das sind Artikel, bei deren Buchung nicht der Artikel selbst gebucht wird, sondern das Set, aus

- 82 -


dem er besteht. Das heißt, dass der Artikel mit den Mengen, die im Set enthalten sind, gebucht werden. Der folgende Abschnitt (kursiv) ist ein wörtliches Zitat aus dem vorher genannten Buch4: Es empfiehlt sich, die Lösung der Aufgabe auf zwei Prozeduren aufzuteilen:

• Die erste Prozedur prüft, ob es sich um einen Einzelartikel oder einen Setartikel handelt. Ist der zu buchende Artikel ein Einzelartikel, wird eine zweite Prozedur einmal aufgeru-fen, die die eigentliche Lagerbuchung vornimmt. Ist der zu buchende Artikel hingegen ein Setartikel, muss die zweite Prozedur für jeden Bestandsartikel einmal aufgerufen werden. Dafür bietet sich natürlich ein Cursor an, der alle Bestandsartikel des Setartikels enthält.

• Die zweite Prozedur nimmt die Lagerbuchung für einen Einzelartikel vor – wie die bishe-rige Prozedur sp Lagerbuchung- und liefert das Ergebnis an die erste aufrufende Proze-dur zurück.

Um eine Lagerbuchung vorzunehmen, wird vom Benutzer bzw. über die Anwendung immer die erste Prozedur aufgerufen. Nennen wir diese Prozedur daher spLagerbuchungAufruf. Zusätzlich zu den vier Übergabevariablen, die beim Aufruf der Prozedur befüllt werden, wird eine weitere Variable benötigt, um als Ergebnis zurückzuliefem, wie viele Einzelbuchungen vor-genommen worden sind (Das ist die Zeile DECLARE @ergebnis int). Dann wird festgestellt, ob der angegebene Artikel ein Setartikel ist. Dafür wird gezählt, wie viele Datensätze es in der Tabelle tbISetartikel gibt, wo die SetArtNr der übergebenen Artikelnum mer entspricht. Gibt es solche Datensätze, handelt es sich um einen Setartikel. Handelt es sich um einen Setartikel, wird ein Cursor benötigt, um alle Bestandsartikel aus der Datenbank auszulesen. Diese werden dann wie Einzelartikel gebucht. Es wird ein Cursor mit dem Namen set_cursor erzeugt, der die Artikelnummern aller Bestandsartikel und jene Menge, mit der sie im Set enthalten sind, liefert. Die Daten des Cursors sollen nicht geändert werden. Auch ist nicht zu erwarten, dass während des Buchungsvorgangs eine Änderung der Daten durch andere erfolgt, da es sich um Stammdaten handelt, die selten geändert werden. Deshalb kann ein insensitiver Cursor verwendet werden, was Performance-Vorteile bringt. Um Artikelnummer und Bestandsmenge der Bestandsartikel aus dem Cursor aufzunehmen, werden die Variablen ©setartikel und @setmenge deklariert. Da diese Variablen nur benötigt werden, wenn ein Setartikel gebucht wird, erfolgt die Variablendeklaration innerhalb des IF-Blocks. Die Variablen @teilmenge und @reserviertmenge werden benötigt, um die zu buchen-de Lager- und Reservierungsmenge je Bestandsartikel zu errechnen. In der Variablen @teilergebnis wird bei jedem Aufruf der zweiten Prozedur, der für jede zurückgegebene Zeile des Cursors erfolgt, der Rückgabewert dieser Prozedur gespeichert. Am Ende kann so die An-zahl der gebuchten Einzelteile als Ergebnis ausgegeben werden. Nach der Deklaration der be-nötigten Variablen wird der Cursor mittels der OPEN-Anweisung geöffnet und die erste Zeile abgerufen. Die Variable @ergebnis wird auf 0 gesetzt. Dies ist notwendig, da nach jeder erfolg-ten Einzelbuchung diese um den Rückgabewert erhöht wird. Wird sie zu Beginn nicht explizit auf 0 gesetzt, hat sie noch den Wert Null und behält diesen, da Null innerhalb einer Berechnung immer wieder nur Null als Ergebnis ergibt. In einer Schleife werden alle Zeilen des Cursors durchlaufen, solange der @@fetch_status den Wert 0 aufweist. Aus der Bestandsmenge, die der Cursor liefert, und der übergebenen Bu-chungsmenge (Omenge) und der Reservierungsmenge (@reserviertmenge) werden die Men-gen errechnet, mit denen der einzelne Bestandsartikel gebucht werden muss.

4 wieder Tiemeyer, Konopasek, Unternehmensdatenbanken mit MS SQL Server und Access, S. 332

- 83 -


Mit dem Befehl execute wird die zweite gespeicherte Prozedur aufgerufen (splagerbuchun-gEinzeln).Mit Output werden dabei Variablen übergeben.

Am Ende der Schleife wird die nächste Zeile aus dem Cursor abgerufen. Sind alle Bestandsarti-kel gebucht, wird der Cursor geschlossen und gelöscht. Der else-Zweig dient der Buchung eines normalen Einzelartikels. Dies geschieht über die Pro-zedur spLagerbuchungEinzeln. Alter Procedure spLagerbuchungAufruf @artikel int, @lager tinyint, @menge int, @reserviert int As set nocount on DECLARE @ergebnis int IF (SELECT Count(*) FROM tblSetartikel WHERE SetArtNr=@artikel) != 0 -- feststellen, ob der Artikel ein Setartikel ist BEGIN DECLARE set_cursor INSENSITIVE CURSOR FOR SELECT TeilArtNr, SetMenge FROM tblSetartikel WHERE SetArtNr = @artikel DECLARE @setartikel int DECLARE @setmenge int DECLARE @teilmenge int DECLARE @reserviertmenge int DECLARE @teilergebnis int OPEN set_cursor SET @ergebnis = 0 FETCH NEXT FROM set_cursor INTO @setartikel, @setmenge WHILE @@fetch_status = 0 BEGIN SET @teilmenge = @setmenge * @menge SET @reserviertmenge = @setmenge * @reserviert EXECUTE spLagerbuchungEinzeln @setartikel, @lager, @teilmenge, @reserviertmenge , @teilergebnis OUTPUT SET @ergebnis = @ergebnis + @teilergebnis FETCH NEXT FROM set_cursor INTO @setartikel, @setmenge END CLOSE set_cursor DEALLOCATE set_cursor END ELSE BEGIN EXECUTE spLagerbuchungEinzeln @artikel, @lager, @menge, @reserviert, @ergebnis OUTPUT END SELECT @ergebnis As Erfolgreich

Dies ist die aufgerufene Prozedur spLagerbuchungEinzeln: Alter Procedure spLagerbuchungEinzeln @artikel int,

- 84 -


@lager tinyint, @menge int, @reserviert int, @ergebnis int OUTPUT As set nocount on IF EXISTS(SELECT ArtNr FROM tblLagerstand WHERE ArtNr=@artikel AND LagNr=@lager) UPDATE tblLagerstand SET LagMenge=LagMenge + @menge, LagReserviert=LagReserviert + @menge WHERE ArtNr = @artikel AND LagNr = @lager ELSE INSERT INTO tblLagerstand (ArtNr, LagNr, LagMenge, LagReserviert) VALUES (@artikel, @lager, @menge, @reserviert) SET @ergebnis = @@rowcount return

19 Trigger Trigger sind in Transact-SQL geschriebene Programme. Sie werden automatisch gestartet, wenn ein bestimmtes Ereignis eintritt. Sie reagieren auf Änderungen in Tabellen: genauer ge-sagt auf das Einfügen, Löschen und Ändern von Daten. Ein Trigger kann auch Daten in ande-ren Tabellen oder Objekten beeinflussen. Dies unterscheidet ihn von einem check-constraint. Ebenfalls ist ein Trigger in der Lage, über Fremdschlüssel verbundene Daten zu ändern, so wie in Access über die Lösch- und Aktualisierungsweitergabe geschieht. Dies nennt man dann kaskadierende Trigger. Man unterscheidet Trigger nach dem SQL-Befehl, durch den er ausgelöst wird:

• Insert-Trigger • Update-Trigger • Delete-Trigger

Sie können Trigger von Access aus festle-gen: Wenn eine Tabelle markiert ist, errei-chen Sie mit einem Rechtsklick den Befehl Trigger: Im folgenden Dialogfeld wird der Triggername festgelegt:

Wenn Sie Neu anklicken, sehen Sie den Editor für den Trigger

- 85 -


19.1 Trigger zum Eintragen der Bestellmenge In der Tabelle tblArtikel der Datenbank TIKO findet sich das Feld ArtMengeBest, in der jeder-zeit die bestellte Menge sichtbar sein soll.

Wenn eine Bestellung erfasst wird, muss zunächst ein Datensatz in der Tabelle tblBestellungen und dann je Position ein Datensatz in der Tabelle tblBestellungenPositionen angelegt werden. Die bestellte Menge wird im Feld BestPMenge erfasst.

Die zunächst nahe liegende Lösung für das Übertragen der Bestellmenge in die Tabelle tblArti-kel ist über ein Formular auf der Grundlage der Bestelltabelle, über welches die Bestellmenge erfasst wird und wodurch dann eine Prozedur ausgelöst wird, die diese Menge auch in das ent-sprechende Feld der Tabelle Artikel einträgt. Noch sicherer jedoch ist dazu ein Trigger. Der Grund ist, dass der Trigger auf einer anderen Ebene ansetzt als die Prozedur. Der Trigger arbeitet auf Tabellenebene; würde das Formular umgangen und ein Artikel bzw. eine Bestellung auf andere Art bearbeitet, dann würde die Pro-zedur gar nicht wirken. Der Trigger hingegen tritt in jedem Fall in Aktion. Aufgabe: Erstellen Sie den Trigger für die Tabelle tblBestellungenPositionen:

create Trigger tblBestellungenPositionen_Insert On dbo.tblBestellungenPositionen For Insert As DECLARE @artikel int DECLARE @menge int SET @artikel = (SELECT BestPArtikel FROM Inserted) SET @menge = (SELECT BestPMenge FROM Inserted) UPDATE tblArtikel SET ArtMengeBestellt = IS NULL(ArtMengeBestellt, 0) + @menge WHERE ArtNr = @artikel

Hier geschieht folgendes: Zunächst werden zwei Variablen deklariert (declare ...), nämlich für die Aufnahme der Arti-kelnummer und die Menge, die bestellt werden soll. In diese Variablen werden Werte zugewiesen:

SET @artikel = (SELECT BestPArtikel FROM Inserted) SET @menge = (SELECT BestPMenge FROM Inserted)

- 86 -


Dazu muss man wissen, dass eine Tabelle Inserted als Pseudo-Tabelle existiert. Sie entspricht in der Struktur der Tabelle, in die ein Datensatz eingefügt wird. Sie enthält den Datensatz, der gerade eingefügt wird. Dann erfolgt der update-Befehl, der in die tblArtikel die bestellte Menge einfügt. Erklärungsbe-dürftig ist nur noch dieser Passus: ISNULL(ArtMengeBestellt, 0)

Nullwerte (=nicht vorhanden) in einer mathematischen Operation bewirken, dass das Gesamt-ergebnis 0 ist. Für den Fall, dass kein Eintrag vorhanden ist, muss ISNULL zu 0 werden, damit zum Beispiel 30 Stück, die als Menge eingetragen werden, zu 0 addiert 30 ergeben und d nicht 0.

Aufgabe: Testen Sie jetzt den gerade erstellten Trigger. Tragen Sie einen Datensatz in die Tabelle tblBestellungenpositionen ein:

....

Kontrollieren Sie dann die Tabelle tblArtikel: Sie finden beim Artikel 1001 dann die Bestellmen-ge 270 (vorher 20, dazu sind dann die 250 aus der neuen Bestellposition addiert worden).

19.2 Trigger beim Löschen eines Datensatzes Wenn die Bestellposition nachträglich wieder gelöscht wird, wird ein anderer Trigger benötigt, der die eingetragene Menge in der Tabelle tblArtikel wieder abzieht. Der Aufbau ist ähnlich wie beim vorigen Trigger: Aufgabe: Erstellen Sie den Trigger tblBestellungenPositionen_Delete

create Trigger tblBestellungenPositionen_Delete On dbo.tblBestellungenPositionen For Delete As DECLARE @artikel int DECLARE @menge int SET @artikel = (SELECT BestPArtikel FROM Deleted) SET @menge = (SELECT BestPMenge FROM Deleted) UPDATE tblArtikel SET ArtMengeBestellt = IS NULL(ArtMengeBestellt, 0) - @menge WHERE ArtNr = @artikel

Der Aufbau ist fast gleich dem vorigen Trigger, der beim Einfügen des Datensatzes wirkte. Der Datensatz wird der Pseudotabelle Deleted entnommen, die automatisch angelegt wird, analog zu der Tabelle Inserted beim vorigen Beispiel.

Aufgabe: Löschen Sie den Datensatz wieder, der zuvor in die Tabelle tblBestellungenPositionen eingefügt wurde:

Öffnen Sie dann noch einmal die tblArtikel: ArtMengeBest ist wieder auf 20 gesunken (ArtNr 1001)

- 87 -


19.3 Trigger beim Ändern von Datensätzen Zur Erläuterung des etwas umfangreicheren Triggers zum Ändern eines Datensatzes hier zu-nächst noch einmal wörtlich aus dem vorher angegebenen Buch:5

Der dritte Trigger reagiert auf Änderungen an bestehenden Datensätzen. Ändert ein Benutzer etwa die Menge oder gar die Artikelnummer in einem bereits gespeicherten Datensatz, muss eine Korrekturbuchung erfolgen. Folgende Anforderungen muss der Trigger erfüllen:

• Er soll nur reagieren, wenn die Artikelnummer oder die Menge geändert worden sind. Bei bloßer Änderung des Preises etwa ist keine Reaktion erforderlich.

• Wenn eine Änderung des Artikels erfolgt, muss - egal ob die Menge sich auch geändert hat oder nicht - die bestellte Menge des alten Artikels reduziert und jene des neuen Ar-tikels neu gebucht werden. Im Prinzip gleicht dieser Vorgang dem Löschen und sofort wieder Neuerfassen einer Bestellzeile.

• Wenn nur die Menge, aber nicht der Artikel geändert worden ist, muss lediglich eine Mengenkorrektur erfolgen. [...]

Sei Update-Triggern kommt häufig die Anweisung IF UPDATE(Spaltenname) zum Einsatz, da meist nicht generell auf irgendeine Änderung reagiert werden soll, sondern sehr differenziert auf Änderungen in bestimmten Feldern. Möchten Sie mehrere Felder darauf prüfen, ob zumindest eines von ihnen geändert wird, ver-knüpfen Sie die Abfrage für die einzelnen Felder mit OR: IF UPDATE(feld1) OR UPDATE(feld2) OR UPDATE(feld3) ...

Soll ein SQL-Block nur ausgeführt werden, wenn mehrere Felder gemeinsam geändert werden, verknüpfen Sie die Bedingungen mit AND: IF UPDATE(feld1) AND UPDATE(feld2) AND UPDATE(feld3) ...

Natürlich können Sie auch NOT verwenden, wenn die Bedingung lautet, dass ein bestimmtes Feld nicht geändert wird: IF UPDATE(feld1) AND NOT UPDATE(feld2) ...

Damit immer nur das erfolgt, was wirklich notwendig ist, wird zu Beginn mit IF UPDATE(BestPArtikel)

abgefragt, ob der Artikel von der Änderungen betroffen ist. Ist dies der Fall, erfolgt zuerst die Rückbuchung der alten Menge des alten Artikels. Dieser Anweisungsteil entspricht dem Delete-Trigger. Die anschließende Buchung des neuen Artikels entspricht genau genommen dem zu-vor erläuterten Insert-Trigger. Die zweite Bedingung prüft, ob ausschließlich die Menge geändert worden ist, nicht aber der Artikel. (Wäre der Artikel auch geändert, wäre dieser Fall ja bereits durch den ersten Prozedur-teil abgehandelt worden.) Mit NOT UPDATE (BestPArtikel) wird geprüft, ob dies auch der Fall ist. Die zu buchende Menge ergibt sich aus der Differenz des alten und des neuen Wertes für die Menge. Auf der folgenden Seite sehen Sie den Code für den update-Trigger:

5 S. 352 f

- 88 -


Der Trigger, der beim Ändern von Datensätzen in der Tabelle tblBestellungenPositionen ein-gesetzt wird: create Trigger tblBestellungenPositionen_Update On dbo.tblBestellungenPositionen For Update As DECLARE @artikel int DECLARE @menge int IF Update (BestPArtikel) BEGIN -- alten Atikel rückbuchen SET @artikel = (SELECT BestPArtikel FROM Deleted) SET @menge = (SELECT BestPMenge FROM Deleted) UPDATE tblArtikel SET ArtMengeBestellt = IS NULL(ArtMengeBestellt, 0) - @menge WHERE ArtNr = @artikel -- neuen Artikel buchen SET @artikel = (SELECT BestPArtikel FROM Inserted) SET @menge = (SELECT BestPMenge FROM Inserted) UPDATE tblArtikel SET ArtMengeBestellt = IS NULL(ArtMengeBestellt, 0) + @menge WHERE ArtNr = @artikel END IF Update(BestPMenge) AND NOT Update (BestPArtikel) BEGIN SET @artikel = (SELECT BestPArtikel FROM Inserted) SET @menge = (SELECT BestPMenge FROM Inserted) - (SELECT BestPMenge FROM Deleted) UPDATE tblArtikel SET ArtMengeBestellt = IS NULL(ArtMengeBestellt, 0) + @menge WHERE ArtNr = @artikel END

Aufgabe: Testen Sie diesen Trigger. Erfassen Sie zunächst einen neuen Datensatz in der Ta-belle tblBestellungenPositionen:

Kontrollieren Sie jetzt in der tblArtikel, ob die Änderung der Bestellmenge dort erfolgt ist. Öffnen Sie dann wieder tblBestellungenPositionen und führen Sie eine Änderung an der Arti-kelnummer durch:

Die Änderung der Menge muss jetzt in der Tabelle tblArtikel zu sehen sein!

- 89 -


Aufgabe 1: In der Datenbank Tiko gibt es eine Tabelle tblKunden und eine davon abhängige Tabelle tblKundenInteressen. Schreiben Sie einen Trigger zur Tabelle Kunden, der beim Löschen eines Kunden auch davon abhängige Datensätze in der ande-ren Tabelle löscht.

Aufgabe 2: In der Tabelle tblWareneingangPositionen werden die einzelnen Positionen auf-genommen, wenn ein Wareneingang verbucht wird. Dabei muss zunächst in der Tabelle tblWareneingang ein Datensatz angelegt werden:

die letzte Zeile ist neu – bitte eintragen! dann erst werden in der Detailtabelle tblWareneingangPositionen die einzelnen Artikel eingetragen. (in der Abbildung nur die letzten, neuen Zeilen)

Der nun zu erstellende Trigger soll folgende Aufgabe leisten: Das Feld WaePText soll die Be-zeichnung des Artikels aus der tblArtikel erhalten. Auch wenn damit eine Redundanz verbun-den ist, so ist dies in der Tiko-DB so angelegt. Bisher ist dieses Feld eines, das keinen Nullwert enthalten darf. Damit wir das Feld leer lassen können, muss zunächst im Tabellenentwurf diese Eigenschaft weggenommen werden:

hier anklicken!

Erstellen Sie nun den Trigger zur tblWareneingangpositionen, der die Artikelbezeichnung aus der Artikel-Tabelle ausliest und einträgt.

Aufgabe 3: Nun soll ein Trigger geschrieben werden, der für nachträgliche Änderungen in der Tabelle tblWareneingangpositionen zuständig ist. Dies soll für den Fall geschehen, dass eine Artikelnummer einer Position geändert wird und dann auch der Artikeltext angepasst werden muss. Nachträgliche Änderungen der Menge spielen hier keine Rolle, denn davon hängt keine andere Tabelle ab.

- 90 -


Lösung zu Aufg. 1 Alter Trigger tblKunden_Delete On dbo.tblKunden For Delete As SET NOCOUNT ON DELETE tblKundenInteressen FROM tblKundenInteressen INNER JOIN Deleted ON tblKundenInteressen.KdNr = Deleted.KdNr

Lösung zu Aufg. 2 Alter Trigger tblWareneingangPositionen_Artikeltext On dbo.tblWareneingangPositionen For Insert As DECLARE @artikel int DECLARE @bezeichnung varchar(60) SET @artikel = (SELECT WaePArtikel FROM Inserted) set @bezeichnung =(select tblArtikel.ArtBezeichnung from tblArtikel where tblArtikel.ArtNr=@artikel) UPDATE tblWareneingangPositionen set WaePText = @bezeichnung WHERE WaePArtikel = @artikel

Lösung zu Aufg. 3 Alter Trigger tblWareneingangPositionen_update On dbo.tblWareneingangPositionen For Update As DECLARE @artikel int DECLARE @bezeichnung varchar(60) SET @artikel = (SELECT WaePArtikel FROM Deleted) set @bezeichnung =(select tblArtikel.ArtBezeichnung from tblArtikel where tblArtikel.ArtNr=@artikel) UPDATE tblWareneingangPositionen set WaePText = @bezeichnung WHERE WaePArtikel = @artikel

- 91 -


20 Formulare im Zusammenarbeit mit Prozeduren Bisher sind Prozeduren, Transaktionen und Trigger separat verwendet worden, um ihre Funkti-onsweise zu erläutern. Den meisten Sinn machen Sie natürlich, wenn Sie als Teil einer Daten-bankanwendung eingesetzt sind. Am folgenden Beispiel soll schrittweise der Einsatz einer Prozedur an einem Formular gezeigt werden. Die ersten Schritte ergeben zunächst noch keine besondere Funktionalität, sondern dienen dazu, schrittweise das Vorgehen deutlich zu machen. Wie benötigen zunächst ein Formular, um die Wirkungsweise testen zu können. Aufgabe: Erstellen Sie das folgende Formular in Access in der Datenbank Tiko:

Als Datenherkunft für das Formular soll die gespeicherte Prozedur spartikel verwendet werden. Deshalb wird in den Eigenschaften des Formulars die Datenherkunft eingetragen:

(dazu Formular in der Entwurfsan-sicht öffnen, ohne dass etwas mar-kiert ist, dann Eigenschaften aufru-fen)

Die schon vorhandene Prozedur spartikel soll zunächst so verändert werden: Das Formular wird damit alle Artikel anzeigen, die über 200 Euro kosten.

- 92 -


Wenn jetzt über das Formular die Artikel aufgerufen werden, dann werden nur 20 Artikel aus-gewählt:

Der nächste Schritt soll darin bestehen, der Prozedur den Grenzwert über einen Parameter zu übergeben: Aufgabe: Verändern Sie die Prozedur spartikel so:

Alter Procedure spArtikel @preis smallmoney As set nocount on SELECT ArtNr, ArtBezeichnung, ArtVKPreis, ArtMengeBestellt FROM tblArtikel WHERE ArtVKPreis > @preis ORDER BY ArtBezeichnung

return

Beim nächsten Öffnen erfolgt dann die Ab-frage nach dem Preis, der als Untergrenze anzusetzen ist:

Soweit ist das Ganze bisher schon bekannt. Noch weiter verfeinern lässt es sich, wenn Sie fol-gende Änderung vornehmen: In den Eigenschaften zum Formular in der Entwurfsansicht gibt es die Eigenschaft Eingabeparameter (Nur in Access-Projekten vorhanden)

Hier ließe sich wieder ein fester Wert einstellen (150), aber dies macht ja weniger Sinn als der Parameter

- 93 -


Statt der Eingabe eines festen Übergabeparameters empfiehlt sich deshalb ein ungebundenes Textfeld im Formular selbst. Die Syntax, um den Wert zu übergeben, ist Formulare!Formularname!Steuerelementname Aufgabe: Fügen Sie dem Formular das

Textfeld hinzu: Geben Sie dem Textfeld den Namen txtpreis

Vergeben Sie dann als Eingabeparameter in den Eigenschaften für das Formular: @preis smallmoney=[Formulare]![frmArtikel]![txtpreis]

Wenn man jetzt das Formular öffnet, einen Wert eingibt und dann den Befehl

Datensätze Anzeige aktualisieren

wählt, erscheinen nur noch die gefilterten Werte.

21 Übungen / Beispiele zu Prozeduren und Transaktionen Zunächst soll für die folgende Übung eine Datenbank aus einer Excel-Datei erzeugt werden. In der Arbeitsmappe Hoteltab.xls befinden sich drei Tabellen: Hotel Hotel_ID Name Ort DZ EZ DZ_Preis EZ_Preis

1 Zum Löwen Glücksbühl 12 4 34 402 Rebstock Kirchheim 4 0 28 323 Zum Adler Hinterkofen 20 4 30 354 Zur Aussicht Kallscheuren 12 6 20 20

Buchung BID Gast_Id Hotel_ID DatumBuchung Anreise Abreise Bestätigung Zimmer

1 100 2 12.02.2004 15.05.2004 20.05.2004 ja DZ2 102 3 12.02.2004 20.05.2004 30.05.2004 ja DZ3 103 2 13.04.2004 20.05.2004 30.05.2004 DZ4 103 3 14.04.2004 21.04.2004 30.05.2004 DZ5 107 2 15.04.2004 16.04.2004 18.04.2004 DZ8 101 2 12.04.2004 30.04.2004 DZ

- 94 -


Gast GastID Name Vorname

100 Schmitz Walter101 Hornbach Frieda102 Kölsch Anna103 Weimar Hans104 Gärtner Fred105 Laurent Leo106 Hammer Petra107 Lohmann Beate108 Holzmann Fritz109 Jordan Josef110 Hunz Paul111 Hinz Günter112 Kunz Hans

Aufgabe: Diese drei Excel-Tabellen sollen zunächst in eine neue SQL-Server-Datenbank Hotel importiert werden. Anschließend soll ein Access-Projekt namens Hotel aufgebaut werden, damit Access als Frontend die Daten verwalten kann.

Aufgabe: Erzeugen Sie ein Diagramm, um die Beziehungen zwischen den Tabellen richtig darstellen zu können.

Aufgabe: Exportieren Sie zusätzlich noch die SQL-Server-Datenbank an Access. Geben Sie

dort den Namen Hotel_acc.

Diese Access-Umwandlung soll nur dazu dienen, mit SQL-Abfragen vorab etwas testen zu kön-nen, bevor dies auf Prozeduren im SQL-Server umgesetzt wird.

21.1.1 SQL-Abfragen zum Datum (Ermittlung freier Hotelkapazitäten) Aufgabe: Ermitteln Sie mit einer SQL-Abfrage Datum1, welche Buchungen im Hotel 2 vor dem

01.05.2004 getätigt wurde.

- 95 -


Lösung: select Anreise,abreise FROM Buchung where Hotel_id=2 and anreise <#2004-05-01#

Anreise abreise

16.04.2004 18.04.2004 12.04.2004 30.04.2004

Aufgabe: Jetzt soll mit einer SQL-Abfrage datum2 herauskommen, welche Buchungen zwi-schen dem 01.04.2004 und dem 01.05.2004 getätigt wurden.

Lösung: select Anreise,abreise,bid FROM Buchung where Hotel_id=2 and anreise between #2004-04-01# and #2004-05-01#

Anreise abreise bid

16.04.2004 18.04.2004 5 12.04.2004 30.04.2004 8

Aufgabe: Jetzt soll die Veränderung in der Abfrage datum3 darin bestehen, das Start- und das Enddatum über Parameter herauszubekommen:

Lösung: (wenn als Start- und Enddatum wieder das oben genannte Datum eingegeben wird) select Anreise,abreise,bid FROM Buchung where Hotel_id=2 and anreise between [dat1] and [dat2]

Anreise abreise bid

16.04.2004 18.04.2004 5 12.04.2004 30.04.2004 7

Aufgabe: Fast dieselbe Abfrage (datum4), aber es soll gezählt werden, wie viele Buchungen im angegebenen Zeitraum und Hotel es sind.

Lösung: select count(bid) as Anzahl_Buchung FROM Buchung where Hotel_id=2 and anreise between [dat1] and [dat2]

Anzahl_Buchung

2

Aufgabe: jetzt wird in der Abfrage (datum5) ein konkretes Datum (hier zum Beispiel: 17.4.2004) über den Parameter [mydatum] eingegeben, um zu sehen, wie viele Bu-chungen in dem Zeitraum sind. Die Logik dabei ist die: Wenn ein gewünschtes Anreisedatum in die Zeit fällt, wo an-dere Buchungen sind, dann ist die ermittelte Anzahl Zimmer schon belegt; SQL sucht die zwei Reservierungen aus, die dann aktuell sind.

Lösung: select anreise, abreise FROM Buchung where Hotel_id=2 and [mydatum] between anreise and abreise

anreise abreise

16.04.2004 18.04.2004 12.04.2004 30.04.2004

- 96 -


Aufgabe: Jetzt wird die Abfrage (datum6) so erweitert, dass die Tabelle Hotel mit einbezogen wird. Der Grund: Aus der Tabelle Hotel muss entnommen werden, wie viele Doppel-zimmer (DZ) dort insgesamt vorhanden sind.

Parameter-Eingabe dat1: 01.04.04, Parameter-Eingabe dat2: 01.05.04 . Hier sehen wir zwei Buchungen, die Anzahl DZ beträgt 4. Lösung: SELECT Buchung.bid , Hotel.dz FROM Buchung,Hotel WHERE Buchung.Hotel_id=2 and Hotel.Hotel_ID=Buchung.Hotel_ID

and Buchung.Anreise between [dat1] and [dat2];

bid dz 5 47 4

Es geht jetzt darum, die Buchungen und die Anzahl Hotelzimmer zu zählen, damit man sie ge-genüberstellen kann. Die folgende Abfrage wäre fehlerhaft und soll nur zur Erläuterung herangezogen werden. SELECT count(Buchung.bid), Hotel.dz FROM Buchung,Hotel WHERE Buchung.Hotel_id=2 and Hotel.Hotel_ID=Buchung.Hotel_ID and Buchung.Anreise between [dat1] and [dat2];

Fehlermeldung:

Es geht nicht, in einer Tabelle zu zählen (count) und dabei eine Ergebniszeile zu erhalten, und in der anderen Tabelle mehrere Zeilen als Ergebnis zu bekommen.

Aufgabe: In einer Abfrage datum7 arbeiten wir jetzt mit einem Trick. In der Tabelle Hotel muss ebenfalls eine Aggregatfunktion verwendet werden, dann funktioniert es. Es ist nur eine Zeile zu Hotel 2 vorhanden, das ermittelte select-Ergebnis ist 4, und davon der Mittelwert (avg) ist natürlich ebenfalls 4. So ist es aber möglich, das Ganze gegen-überzustellen. SQL kann so aus beiden Tabellen eine Zeile ausgeben. (Eingabe der Parameter-Werte für das Datum wie oben)

SELECT count(Buchung.bid) as gebucht, avg(Hotel.dz) as Zimmer FROM Buchung,Hotel WHERE Buchung.Hotel_id=2 and Hotel.Hotel_ID=Buchung.Hotel_ID and Buchung.Anreise between [dat1] and [dat2];

gebucht Zimmer

2 4

Aufgabe: Jetzt kann schließlich in der Abfrage datum8 bei einem gewünschten Anreisedatum, z. B. dem 18.4.2004, in der direkten Gegenüberstellung gesehen werden, ob Zim-mer frei sind.

- 97 -


Lösung: SELECT count(Buchung.bid) as gebucht, avg(Hotel.dz) as Zimmer FROM Buchung,Hotel WHERE Buchung.Hotel_id=2 and Hotel.Hotel_ID=Buchung.Hotel_ID and [mydatum] between buchung.anreise and buchung.abreise

gebucht Zimmer2 4

Aufgabe: Dies lässt sich auch in einem berechneten Feld noch gegenüberstellen, um zu se-hen, ob etwas frei ist. (datum9)

Lösung: SELECT count(Buchung.bid) as gebucht, avg(Hotel.dz) as Zimmer, avg([Hotel].[dz])-count([Buchung].[bid]) as frei FROM Buchung,Hotel WHERE Buchung.Hotel_id=2 and Hotel.Hotel_ID=Buchung.Hotel_ID and [mydatum] between buchung.anreise and buchung.abreise

gebucht Zimmer frei2 4 2

So lässt sich also berechnen, ob bei einem bestimmten Wunschdatum für die Anreise ein Zim-mer frei ist. Access alleine kann aber nicht prüfen, ob etwas frei ist, und davon die Möglichkeit der Buchung und damit die Aufnahme eines neuen Datensatzes in der Tabelle Buchung davon abhängig machen; deshalb soll das bisher Ermittelte dazu verwendet werden, in einer Prozedur im SQL Server (aber dann wieder in der adp-Datei, der Client-Verknüpfung zu Access) verwendet zu werden.

21.1.2 Prozeduren zur Buchung Über Prozeduren kann es jetzt möglich gemacht werden, den Vergleich zwischen vorhandenen und freien Zimmern eines Hotels dazu zu verwenden, um Transact-SQL entscheiden zu lassen, ob eine Buchung erfolgen kann. Über eine if-Entscheidung kann dann der neue Buchungsda-tensatz eingetragen werden (oder auch nicht, wenn nichts frei ist). Wir testen zunächst einmal in der ersten Prozedur, wie viele Zimmer das Hotel frei hat. Prozedur Buchung1: Alter Procedure Buchung1 @hotel int As set nocount on declare @wert int set @wert= (select hotel.dz from Hotel where @hotel=hotel.hotel_id) select @wert as Zimmer return

- 98 -


Die Prozedur ermittelt zunächst nur die Anzahl Zimmer im gegebenen Hotel (Hotel 2) Zimmer

4 Demgegenüber soll die Prozedur Buchung2 zunächst ermitteln, wieviele Buchung zu einem bestimmten Datum schon vorliegen: Alter Procedure Buchung2 @Anreisedatum smalldatetime, @Gast int, @hotel int As set nocount on declare @neugast int declare @datum smalldatetime declare @neuanlage bit declare @wert int set @wert= (SELECT count(Buchung.bid) FROM Buchung WHERE Buchung.Hotel_id=@hotel and @anreisedatum between buchung.anreise and bu-chung.abreise) select @wert as gebucht return

Ergebnis bei Eingabe von:

Anreisedatum: 18.4.2004 Gast: 102 Hotel: 2

gebucht

2

Um dies jetzt in einer Prozedur zusammenzufassen, sind folgende Schritte nötig: 1. Eingabe von Anreisedatum, Hotel und Gast 2. Ermittlung der Anzahl freie Zimmer im Hotel zum angegebenen Datum

freie Zimmer=gesamte Zimmerzahl minus gebuchte Zimmer)

3. Wenn freie Zimmer vorhanden, dann Eingabe des Datensatzes mit der Buchung (hier würde natürlich noch das Abreisedatum fehlen; wir mussten die ganze Zeit über ja nur prüfen, ob zum Anreisedatum etwas frei ist)

4. Wenn kein freies Zimmer, dann kann die Buchung nicht entgegengenommen werden.

Aufgabe: Erstellen Sie die Prozedur, mit der wir diese Schritte erreichen. Der zentrale Ver-gleich, der über die Aufnahme des Datensatzes entscheidet, ist:

(SELECT count(Buchung.bid) FROM Buchung WHERE Buchung.Hotel_id=@hotel and @anreisedatum between buchung.anreise and buchung.abreise)< (select avg(hotel.dz) from Hotel

where hotel.Hotel_id=@hotel)

- 99 -


Lösung: Alter Procedure Buchung3 @Hotel int, @Anreisedatum smalldatetime, @Gast int As set nocount on declare @neugast int declare @datum smalldatetime declare @neuanlage bit if (SELECT count(Buchung.bid) FROM Buchung WHERE Buchung.Hotel_id=@hotel and @anreisedatum between buchung.anreise and buchung.abreise)< (select avg(hotel.dz) from Hotel where hotel.Hotel_id=@hotel) begin insert into Buchung (Gast_ID, Hotel_ID, Anreise) values (@gast, @hotel, @Anreisedatum) set @neuanlage=1 select @@rowcount as Erfolgreich, @neuanlage as Neuanlage end else begin set @neuanlage=0 select @neuanlage as Neuanlage end return

Aufgabe: Testen Sie mit der Prozedur, ob ein Buchungsdatensatz aufgenommen wird, wenn ein Zimmer frei ist, und andernfalls nicht.

- 100 -

tiko setup.adp da- ten - robertkrah.de 2000 teil 3.pdf · die beispieldatenbank tiko besteht aus...

Documents