3AHD 2002/03 ADAT Plasser 26.2.2003 Übungsblatt 11: PL/SQL: Trigger Aufgabe 1: Trigger: Protokollierung von Änderungen, Zugriffsschutz Stellen Sie mit einem Trigger sicher, daß alle delete- und insert-Befehle, sowie updateBefehle mit Bezug auf die Felder sal und comm in der Tabelle Protokoll eingetragen werden. Protokoll (username, datum, empno, oldSal, oldComm, newSal, newComm, operation(=delete| insert | update) ) Der Trigger soll außerdem alle Veränderungen der Tabelle durch Benutzer die im Namen nicht die Zeichenkette „3a“ enthalten unterbinden. Beispiel: user 3ah01 darf, plasser darf nicht. Hinweise: Username wird von Funktion user geliefert Das Tagesdatum wird von Funktion sysdate geliefert Aufgabe 2: Trigger: Datenergänzung, Datenaufbereitung Erstellen Sie einen Trigger, der beim Einfügen von Sätzen in die Tabelle emp fehlende Werte ( Wert ist NULL) für job, hiredate, sal, comm, deptno mit ‘SALESMAN’, Systemdatum, 0 , 0 , 10 ersetzt. Beispiel: insert into emp (empno, ename, sal) values (1000,’JIM’,5000) führt zum selben Ergebnis wie insert into emp (empno,ename,job,hiredate,sal,comm,deptno) values (1000,’JIM’,’SALESMAN’,<Tagesdatum>,5000,0,10). Die vom Trigger ersetzten Werte sind fett dargestellt. Sorgen Sie dafür, daß ename in emp immer in Groß-/Kleinschreibung eingetragen wird. Entspricht der eingegebene Namensstring nicht diesem Format, soll konvertiert werden. Aufgabe 3: Trigger: Semantische Integrität Stellen Sie mit Hilfe von Triggern sicher, daß folgende Bedingungen erfüllt werden: 1. Das Gehalt eines Mitarbeiter darf nur um 10% erhöht werden, außer der Mitarbeiter hat den Job „PRESIDENT“. Bei einem Verstoß gegen diese Regel sollen altes und neues Gehalt und das maximal mögliche neue Gehalt ausgegeben werden. 2. Die Gehaltssumme einer Abteilung darf den Wert von 20 000 $ nicht überschreiten. 3. Das Gehalt eines Angestellten (sal + comm!) darfs nie größer als das Gehalt seines Chefs werden. Aufgabe 4: Trigger: Semantische Integrität, PL/SQL-Prozedur 1. Erzeugen Sie eine Tabelle jobSal mit den Attributen job, minsal, maxsal. 2. Programmieren Sie eine Prozedur, die in die Tabelle jobSal für jeden job das bisher in emp gespeicherte minimale und maximale Gehalt einträgt. 3. Programmieren Sie einen Trigger, der für die Zukunft sicher stellt, daß das Gehalt eines Mitarbeiters innerhalb der Grenzen seines Berufs liegt. Wird die Bedingung verletzt, soll die Meldung Änderung nicht möglich bzw Neuanlage nicht möglich ausgegeben werden. Ebenso liegt ein Fehler vor, wenn job in jobSal nicht eingetragen ist. Die Überprüfung soll von einem Trigger vorgenommen werden. (if inserting|updating|deleting) 3AHD 2002/03 ADAT Plasser 26.2.2003 Übungsblatt 11: PL/SQL: Trigger