DML-Trigger - oth

Datenbanken
9. Übung
Aufgabe: Integritäts-Sicherheitsvorkehrungen in Oracle
Lösungsschritte. Man unterscheidet: statische Integritätsbedingungen (Column bzw. Table
Constraints , Assertions) und dynamische Integritätsbedingungen.
1. Statische Integritätsbedingungen
Constraints
CREATE TABLE tabelle
( col datentyp [DEFAULT wert]
[colConstraint ... colConstraint],
.
.
col datentyp [DEFAULT wert]
[colConstraint ... colConstraint],
.
.
[ tableConstraint, ]
.
.
[tableConstraint]);
colConstraint: ist eine Bedingung, die nur eine Spalte betrifft
tableConstraint: kann mehrere Spalten betreffen.
Jedes colConstraint bzw. tableConstraint hat die Form [ constraint name ]
bedingung
Semantische Integrität. Zur Sicherung der semantischen Integrität können 4 Arten von Constraints1
eingesetzt werden:
NOT NULL
Mit dieser Constraint wird sichergestellt, daß das Feld nach Neuanlage des Datensatzes auf jeden Fall
ein Wert enthält
DEFAULT
Diese Klausel enthält einen Ausdruck, dessen Wert dem Attribut zugewiesen wird, wenn explizit kein
anderer Wert zugewiesen wurde. In Wertzuweisungen der DEFAULT-Klausel dürfen SYSDATE,
USER, UID oder USERENV enthalten sein. DEFAULT kann in CREATE TABLE-, ALTER TABLE- und
MODIFY-Befehlen verwendet werden.
UNIQUE
Mit dieser Klausel kann für eine oder mehrere Spalten festgelegt werden, daß die Datensätze einer
Tabelle für diese Spalte(n) eindeutige Werte besitzen müssen. Die Spalten dürfen Nullwerte enthalten.
Für UNIQUE definierte Spalten wird automatisch ein UNIQUE INDEX angelegt, deshalb können in der
UNIQUE-Constraint auch die Bedingungen für die Generierung eines Index wie im CREATE INDEXBefehl enthalten sein.
CHECK
Mit der CHECK-Constraint können Spaltenwerte dahingehend überprüft werden, ob sie den in der
CHECK-Klausel definierten Regeln entsprechen
Syntax: [constraint name] check (condition)
„condition“ kann sich auf alle Spalten einer Tabelle beziehen. Nur einfache Bedingungen sind
zugelassen. So ist es bspw. nicht erlaubt, sich auf andere Tabellen zu beziehen oder Abfragen für
Bedingungsprüfungen zu formulieren. Auch SYSDATE und USER können nicht in einer Bedingung
verwendet werden. Einfache Vergleiche zu Attributen und logische Verbindungen (and, or, not) sind
dagegen erlaubt. Die Bedingungsprüfung schließ ein : „is not null“.
1
vgl. 2.3.2.5, 2.
1
Datenbanken
Entitätsintegrität: Mit der PRIMARY KEY –Klausel werden für die Spalten des Primärschlüssels implizit
die Constraints NOT NULL und UNIQUE festgelegt.
Referentielle Integrität: Sie wird über die FOREIGN KEY –Klausel gesichert.
Syntax: [constraint name] foreign key (column(s))
references table [(column(s))] [on delete cascade]
Der „foreign key“ muß sich auf den vollständigen „primary key“ der referenzierten Tabelle beziehen.
Nicht erlaubt sind Teilmengen der Attribute (Spaltenbezüge), die den „primary key“ bilden.
Falls eine Constraint über „create table ...“ definiert bzw. mit „alter table ...“ hinzugefügt wurde, ist
diese „constraint“ gesetzt („enabled“). Sie kann zurückgesetzt („disabled“) werden über
alter table table disable
constraint name | primary key | unique [column(s)] [cascade]
Ein „constraint“ bzgl. eines „primary key“ kann nur zurückgesetzt werden, wenn alle "foreign
constraints“, die diesen "primary key“ verwenden, zurückgesetzt wurden. Die cascade-Klausel besorgt
dies automatisch. Falls eine Tabelle Referenz für einen foreign key ist, kann sie nur über das
Kommando drop table tabelle cascade constraints gelöscht werden. Alle anderen
Datenbankobjekte, die sich auf diese Tabelle beziehen, bleiben im DBS erhalten (z.B. Trigger), sie
sind aber nicht gültig.
Informationen über Integritäts-Bedingungen und deren Status (enabled, disabled) sind im „data
dictionary“ in den Tabellen USER_CONSTRAINTS und USER_CONS_CONSTRAINTS vorhanden.
Bsp.: Erzeugen von Tabellen der Datenbank für das Personalwesen, die die Entitätsintegrität und
referentielle Integrität mit Hilfe von Constraints erreichen2.
2. Dynamische Integritätsbedingungen
Oracle unterscheidet drei Trigger-Typen: DML-Trigger, Instead-of-Trigger, System-Trigger.
DML-Trigger. Anweisungen aus dem DML-Bereich von SQL wie INSERT, UPDATE, DELETE
Können einen DML-Trigger auslösen. Dabei kann man bei der Programmierung des Triggers
bestimmen, ob die Anweisungen vor oder nach dem auslösenden Ereignis (vor oder nach dem
Eintragen, Löschen, Aktualisieren also) ausgelöst werden sollen.
Instead-of-Trigger. Auch Instead-of-Trigger reagieren auf DML-Operationen allerdings ausschließlich
nur auf Sichten (relationale oder Objekt-Sichten).
System-Trigger. Der System-Trigger ist ein Werkzeug zum Abfangen von System-Ereignissen und
DDL-Befehle. Systemereignisse können bspw. das Starten und Hochfhren der Datenbank, das Anund Abmelden von Benutzern sowie Fehler sein.
DML-Trigger
Trigger3 sind einer Tabelle zugeordnete PL/SQL-Module, die bei einer DML-Aktion gegen die Tabelle
ausgeführt werden. Es handelt sich um eigenständige, im Data Dictionary unkompiliert abgelegte
Objekte. Trigger sind in Oracle PL/SQL-Prozeduren. Eine derartige Prozedur ist mit einer Tabelle
assoziiert und wird automatisch vom DBS aufgerufen, wenn eine Modifikation (Ereignis, event) bzgl.
dieser Tabelle auftritt.
Notwendige Systemberechtigungen. Für das Anlegen eines Trigger auf eine Tabelle, muß entweder
die Tabelle dem User gehören oder der User besitzt für die Tabelle die Berechtigung ALTER bzw.
ALTER ANY TABLE. Außerdem muß er die Berechtigung CREATE TRIGGER besitzen. Um im
Schema eines anderen Benutzers einen Trigger anzulegen, benötigt man die Systemberechtigung
CREATE ANY TRIGGER. Die Systemberechtigung CREATE TRIGGER gehört zur Resource-Rolle,
die von Oracle zur Verfügung gestellt wird.
Eine Trigger-Definition besteht aus
- dem Namen des Triggers create [or replace] trigger trigger_name
2
3
Vgl. ueb9.sql
vgl. 2.3.2.5 3.
2
Datenbanken
- dem "Trigger-Zeitpunkt" before | after
- "triggering event(s)" insert or update [of column(s)] or delete on tabelle
Außerdem lassen sich diese drei Trigger-auslösenden Ereignisse derart kombinieren, daß ein
Trigger immer dann ausgelöst wird, wenn eine INSERT-, UPDATE- oder DELETE-Anweisung
ausgeführt wird.
- dem Trigger-Typ (optional)4 for each row
Wird diese Klausel weggelassen, ist der Trigger befehlsorientiert, andernfalls zeilenorientiert. Ein
befehlsorientierter Trigger wird genau einmal vor bzw. nach der Ausführung desjenigen Befehls
ausgeführt, der das Ereignis auslöst. Ein zeilenorientierter Trigger wird je einmal pro Datensatz
ausgeführt, der durch den Befehl verändert wurde.
Nur mit einem befehlsorientierten Trigger ist es möglich, sich auf einen Attributwert vor und nach der
Modifikation zu beziehen. Jeder "update"-Trigger kann dann den alten Attributwert mit
:old.spalte und den neuen Attributwert mit :new.spalte referenzieren. Ein "insert"-Trigger
kann den neuen Attributwert mit :new.spalte, ein "delete"-Trigger den alten Attributwert mit
:old.spalte referenzieren. Man kann diese Bezüge auch in Vergleichen verwenden, z.B. "if
:old.gehalt < :new.gehalt then ...". In einem "before"-Trigger ist es sogar möglich, sich
auf die neuen Werte in der Zeile zu beziehen, z.B. :new.gehalt := :new.gehalt * 1.05 bzw.
:new.gehalt := :old.gehalt .Derartige Modifikationen sind in zeilenorientierten "after"Triggern nicht erlaubt..
- der Trigger-Restriktion (nur für zeilenorientierte Trigger): when condition
Die WHEN-Klausel kann einfache Bedingungen5 enthalten. Allerdings können auch sysdate und
user verwendet werden. Bei Vergleichen zwischen altem und neuen Attributwert darf ":"
weggelassen werden, z.B. old.attribut bzw. new.attribut.
- dem Trigger-Körper: PL/SQL-Block
Der Trigger-Körper besteht aus einem PL/SQL-Block. Alle SQL (ausgenommen commit und rollback)
und PL/SQL-Kommandos dürfen in einem PL/SQL Trigger-Block verwendet werden. Die Pl/SQL
Kontrollstruktur "if" kann mit den Konstrukten if inserting, if updating [(column)] und if
deleting, ausgestattet werden.
Auf der Grundlage aller in einer CREATE TRIGGER-Anweisung möglichen Permutationen, kann
eine Tabelle bis zu 12 verschiedene Trigger aufweisen:
- 6 Trigger auf Zeilenebene für BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE, AFTER
DELETE, AFTER INSERT, AFTER UPDATE
- 6 Trigger auf Anweisungsebene für BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE,
AFTER DELETE, AFTER INSERT, AFTER UPDATE
Ein BEFORE-Trigger auf Zeilenebene wird vor Ausführung des Trigger auslösenden Ereignisses
ausgelöst. Somit kann ein BEFORE-Trigger auf Zeilenebene zur Änderung von Spaltenwerten einer
Zeile eingesetzt werden. Ein AFTER-Trigger auf Zeilenebene wird nach Auftreten des Triggerauslösenden Ereignisses ausgelöst.
Die Ausführung eines PL/SQL-Block von einem Trigger bildet eine Transaktion um das TriggeringEvent. So kann bspw. eine "insert"-Anweisung in einem PL/SQL-Block einen anderen Trigger
auslösen, evtl. zu einem kaskadierenden Ausführen von Triggern führen. Solche Triggersequenzen
terminieren erfolgreich, wenn keine Ausnahme innerhalb eines PL/SQL-Blocks auftritt und keine
Integritätsbedingung verletzt wurde. Sobald ein Trigger eine Ausnahme (exception) in einem
PL/SQL-Block auslöst, werden alle Modifikationen seit Beginn der Transaktion zurückgesetzt. In
einem PL/SQL-Block kann eine "exception" die Anweisung raise_application_error
auslösen. In Kombination mit einem zeilenorientierten Trigger kann sich
raise_application_error auf alte bzw. neue Attributwerte beziehen.
Aufruf von gespeicherten Prozeduren in einem Trigger: Aus dem PL/SQL-Block eines DatenbankTrigger kann eine gespeicherte Prozedur oder eine Funktion, ob eigenständig oder Teil eines Pakets,
aus dem PL/SQL-Block eines Datenbank-Trigger aufgerufen werden.
Aktivieren und Deaktivieren von Triggern.
Deaktivieren: ALTER TRIGGER trigger_name DISABLE
Aktivieren: ALTER TRIGGER trigger_name ENABLE
Löschen eines Trigger: DROP TRIGGER trigger_name
4
5
row level trigger
vgl. Bedingungen der CHECK-Klausel unter CONSTRAINTs
3
Datenbanken
Anpassen an Fehlerbedingungen. In einem Trigger können verschiedene Fehlerbedingungen
festgelegt sein. Für jede definierte Fehlerbedingung kann eine Fehlermeldung gewählt werden, die
beim Anlegen dieses Fehlers angezeigt wird. Die Fehlernummern und Meldungen, die der Benutzer
sieht, werden über die Prozedur RAISE_APPLICATION_ERROR eingerichtet, die sich aus jedem
Trigger aufrufen lässt.
Informationen über Trigger. Im Data Dictionary sind Informationen über Trigger in der Tabelle
USER_TRIGGERS gespeichert. Die Informationen umfassen den Trigger-Namen, den Trigger-Typ
und den Code für den PL/SQL-Block.
Trigger auf Zeilenebene und Trigger auf Anweisungesebene. ROW-Level-Trigger (ZeilenebenenTrigger werden mit der optionalen Klausel FOR EACH ROW angelegt Ohne diese Klausel liegt ein
Statement-Level-Trigger (Anweisungsebenen-Trigger) vor, der nur einmal je Anweisungsebene
angestoßen wird. So bewirkt
update job set gehalt = 1.1 * gehalt where titel = 'Systemplaner';
unter Verwendung der Row-Level-Semantik die Auslösung für jede Zeile einmal, die Statement-LevelSemantik führt zur einmaligen Auslösung je Anweisung. Zeilenorientierte Trigger sind wegen
zahlreicher Reaktionen meistens kritisch. Oracle führt einen exklusiven LOCK auf die betroffene
Tabelle zu Beginn einer INSERT-, UPDATE- , DELETE- Anweisung aus. Diese Kennzeichnung der
Tabellen wird "mutating" genannt. Andere Benutzer sind während dieser Zeit vom Zugriff
ausgeschlossen. Die einzige Möglichkeit des Zugriffs während dieser Zeit ist die Verwendung von
:old.spalte und :new.spalte in Verbindung mit einem zeilenorientierten Trigger.
Instead-of-Trigger
Dieser Trigger dient zum Überwachen von Sichten bei INSERT-, DELETE- und UPDATEAnweisungen. Über Insted-of-Trigger wird mitgeteilt, wie die darunterliegenden Tabellen, die
Bestandteil einer View sind, zu aktualisieren sind. Instead-of-Trigger lassen sich nur auf Sichten
ausführen, die änderbar sind6. Die Sichten dürfen keine Mengenoperationen (UNION, MINUS),
Aggregatfunktionen, Gruppierungen oder Verkettungen (GROUP BY oder START WITH) sowie
DISTINCT enthalten.
Definition: CREATE [OR REPLACE] TRIGGER trigger-name
INTEAD OF ereignis
[referenzklausel]
[WHEN trigger-bedingung]
[FOR EACH ROW]
anweisungsabschnitt
6
vgl. Ueb7.doc
4