Trigger
Rec. on?
Trigger
• Konzept, um automatisch auf vordefin- ierte Ereignisse in der Datenbank zu reagieren
• Trigger sind mit Datenbanktabelle verknüpft
• Auslösung durch DML Befehle INSERT, UPDATE und DELETE
• Einsatzbereiche:
• Datenintegrität
• Datensicherheit
• referentielle Integrität (besser durch Constraints)
• Produktivitätssteigerung
• Ausführungszeitpunkte:
• Vor der Ausführung des DML Befehls
• Nach der Ausführung des DML Befehls
• Anstelle eines DML-Befehls
• Bearbeitung durch Trigger bezieht sich entweder
• auf das DML-Statement (State- ment Trigger)
• auf jeden einzelnen betroffenen Datensatz (Row Trigger)
Rec. on?
Trigger
• Syntax:
CREATE [OR REPLACE] TRIGGER <Name>
{ BEFORE | AFTER | INSTEAD OF } { INSERT | DELETE | UPDATE }
[OF <Spaltenliste> ]}
ON <Tabellenname>
[FOR EACH ROW]
[ WHEN (<Prädikat>)]
<Triggerrumpf>
• Erläuterungen
• INSTEAD OF Trigger bei Views 1
• Vordefinierte Variablen :new und :old für den Zugriff auf die alten/neuen Werte (nur bei ROW, INSTEAD OF-Trigger)
• WHEN darf keine SQL-Anfragen enthalten
• keine zyklischen Abhängigkeiten erlaubt
• Aktivieren/Deaktivieren eines Triggers:
ALTER TRIGGER <TRIGGERNAME>
ENABLE | DISABLE;
• Tabelle user_triggers enthält Trigger-
code
Trigger Beispiele (ROW)
create or replace trigger example_row_trigger after update of population on city
for each row begin
dbms_output.put_line('Bevölkerungszuwachs in '||:old.name||
' von '||:old.population||' auf '||:new.population);
end;
/
SQL> set serveroutput on SQL> update city
set population=round(population*1.05) where country='D';
Bevölkerungszuwachs in Bremen von 549182 auf 576641 Bevölkerungszuwachs in Stuttgart von 588482 auf 617906 Bevölkerungszuwachs in Potsdam von 138268 auf 145181 Bevölkerungszuwachs in Wiesbaden von 266081 auf 279385 Bevölkerungszuwachs in Schwerin von 118291 auf 124206 ...
Trigger Beispiele (STATEMENT)
create or replace trigger example_statement_trigger after delete on city
begin
dbms_output.put_line('User '||user()||
' hat ein DELETE Statement auf Tabelle city durchgeführt');
end;
/
show errors
SQL> delete city
where population < 100000;
User SCAN0004 hat ein DELETE Statement auf Tabelle city durchgeführt 184 Zeilen wurden gelöscht.
SQL>
Trigger Beispiele (Check)
create or replace trigger check_establishment_time before insert on city
begin
if to_char(sysdate,'HH24:MI') between '00:00' and '12:00' then
raise_application_error(-20001,'Städte dürfen nur nachmittags gegründet werden');
end if;
end;
/
show errors
SQL> insert into city (name, country, population) values('Smifftown', 'D', 142252352);
insert into city (name, country, population) *
FEHLER in Zeile 1:
ORA-20001: Städte dürfen nur nachmittags gegründet werden ORA-06512: in "SMIFF.CHECK_ESTABLISHMENT_TIME", Zeile 3
ORA-04088: Fehler bei der Ausf³hrung von Trigger 'SMIFF.CHECK_ESTABLISHMENT_TIME'
Abarbeitungsreihenfolge
create table trigger_test_table ( id number primary key,
text varchar2(10) );
create or replace TRIGGER before_update before update on trigger_test_table begin
dbms_output.put_line('before_update_stmt');
end;
/
create or replace TRIGGER after_update after update on trigger_test_table begin
dbms_output.put_line('after_update_stmt');
end;
/
create or replace TRIGGER before_update_row before update on trigger_test_table
for each row begin
dbms_output.put_line('before_update_row ('||
:old.text||'->'||:new.text||')');
end;
/
create or replace TRIGGER after_update_row after update on trigger_test_table
for each row begin
dbms_output.put_line('after_update_row ('||
:old.text||'->'||:new.text||')');
end;
/
Abarbeitungsreihenfolge
insert into trigger_test_table values(1, 'eins');
insert into trigger_test_table values(2, 'zwei');
insert into trigger_test_table values(3, 'drei');
set serveroutput on
update trigger_test_table set text=upper(text) where id < 3;
before_update_stmt
before_update_row (eins -> EINS) after_update_row (eins -> EINS) before_update_row (zwei -> ZWEI) after_update_row (zwei -> ZWEI) after_update_stmt
2 Zeilen wurden aktualisiert.
select *
from trigger_test_table;
ID TEXT
--- --- 1 EINS
2 ZWEI 3 drei SQL>
upper(...) wandelt eine
Zeichenkette in Großbuchstaben um
Unterscheidung der auslösenden Triggeraktion
create or replace TRIGGER before_update
before insert or update or delete on trigger_test_table begin
if (INSERTING) then
dbms_output.put_line('insert operation');
ELSIF (UPDATING) then
dbms_output.put_line('update operation');
ELSIF (DELETING) then
dbms_output.put_line('delete operation');
ELSE
raise_application_error(-20001, 'Das darf eigentlich nicht passieren!');
END IF;
end;
/
show errors
Trigger - Einsatzgebiete
• ROW Trigger
• BEFORE INSERT/UPDATE:
• Um Spaltenwerte zu setzen oder zu transformieren
• erweiterte Konsistenzchecks; eventuell verwerfen des Statements mit
raise_application_error(error_id, 'Text');
• AFTER INSERT/UPDATE: um weitere Aktionen anzuschucken
• BEFORE/AFTER DELETE
• zusätzliche Aktionen vor oder nach dem Löschen
• STATEMENT Trigger
• Auditing
• Checks
Instead Of Trigger (1)
-- Szenario: Temperaturtabelle (in Fahrenheit) mit Zeitstempel und Ort der Messung -- View der die Temperatur in der Küche in Celsius ausgibt
--
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
create table temperature ( timestamp date,
location varchar2(20), fahrenheit number(5,1),
primary key (timestamp, location) );
create or replace
view kitchen_temperature (timestamp, celcius) as
select timestamp, (fahrenheit-32) * 5.0/9.0 from temperature
where location='kitchen';
Instead Of Trigger (2)
create or replace trigger add_temperature_value instead of insert on kitchen_temperature
for each row begin
insert into temperature
values(:new.timestamp, 'kitchen', :new.celcius * 9.0/5.0 + 32);
end;
/
show errors
insert into temperature
values(sysdate-2/24, 'kitchen', 67);
insert into kitchen_temperature values(sysdate-1/24, 25.1);
-- Übung: Trigger für Update auf kitchen_temperature
aktiviert add_temperature_value trigger
kann auch weggelassen werden, da
Instead-Of Trigger, immer auf Datensatz Basis arbeiten
Mutating Table Problem
• Eine Tabelle wird als „Mutating Table“ bezeichnet, wenn
• Änderungen an dieser Tabelle den Trigger aktiviert haben oder
• diese Tabelle aufgrund von „ON DELETE ...“ Constraints eventuell modifiziert werden muss.
• Auf diese Tabellen darf von einem Row-Trigger aus nicht zugegriffen 1 werden.
• Workarounds:
• auf diese Tabellen nicht zugreifen ;-)
• Innerhalb des ROW-Triggers temporäre Daten ablegen und diese in einem AFTER-Statement-Trigger verarbeiten
• Trigger auf View definieren und mit „Instead of“ -Trigger arbeiten
Beispiel zu Mutating Table Problem
• Aus Tutorium 1 (PL/SQL): Wenn ein Posten einer Bestellung gelöscht wird, dann soll eine Neunumerierung der Positionen stattfinden (Szenario Tutorium 1, Übung 1)
create table posten (
bestellung_fk number not null references bestellung (id)
on delete cascade,
artikel_fk number not null references artikel(id)
on delete cascade, position number not null,
anzahl number check (anzahl > 0),
constraint CONS_KEINE_GLEICHEN_ARTIKEL
unique(bestellung_fk, artikel_fk) );
Tabelle 1: Posten
bestellung_fk artikel_fk position anzahl
... ... ... ...
5 4 1 1
5 7 2 10
5 21 3 2
5 8 4 1
6 1 1 1
6 21 2 20
... ... ... ...
Beispiel zu Mutating Table Problem
• 1.Ansatz
create or replace TRIGGER positionen_neu_berechnen0 after delete on posten
for each row begin
update posten
set position=position-1
where bestellung_fk = :old.bestellung_fk and position > :old.position;
end;
/
• Resultat (beim Löschen eines Postens):
ORA-04091: Tabelle SCAN0004.POSTEN wird gerade geõndert, Trigger/Funktion sieht dies m÷glicherweise nicht
ORA-06512: in "SCAN0004.POSITIONEN_NEU_BERECHNEN0", Zeile 3 ORA-04088: Fehler bei der Ausf³hrung von Trigger
'SCAN0004.POSITIONEN_NEU_BERECHNEN0'
Beispiel zu Mutating Table Problem
• Lösung 1: Aufspalten in einen ROW- und einen Statement Trigger.
create table temp (
bestellung number references bestellung(id), geloeschte_position number
);
create or replace TRIGGER positionen_neu_berechnen1 after delete on posten
for each row begin
insert into temp
values(:old.bestellung_fk, :old.position);
end;
/
Da man im Row Trigger
nicht auf Datensätze der Tabelle Posten zugreifen
verschieben das auf den anschließenden Statement Trigger kann machen wir es einfach nicht, sondern
(da ist es erlaubt)
Beispiel zu Mutating Table Problem
create or replace TRIGGER positionen_neu_berechnen2 after delete on posten
declare
cursor cur_position is select * from temp
order by geloeschte_position desc;
begin
for data in cur_position loop update posten
set position=position-1
where bestellung_fk = data.bestellung
and position > data.geloeschte_position;
end loop;
delete
from temp;
end;
/
• Lösung 2: Mittels Instead of Trigger und View: siehe Beispiele zur Vorlesung
Auslesen der im Row-Trigger abgespeicherten Datensätze und durchführen der Update Operation auf Tabelle Posten.
Anschließend löschen der Daten- sätze aus Tabelle temp.
weitere Trigger
• Data Definition Language Event Trigger:
• before / after create
• before / after grant
• before / after alter
• before / after drop
• ...
Triggerbeispiel
create table person (
p_id number(8) primary key,
vorname varchar2(60) not null, nachname varchar2(60) not null, geburtsdatum date not null,
todesdatum date );
create table ist_verheiratet_mit (
ehemann_fk number(8) references person(p_id) on delete cascade, ehefrau_fk number(8) references person(p_id) on delete cascade, hochzeitsdatum date not null,
trennungsdatum date,
primary key(ehemann_fk, ehefrau_fk, hochzeitsdatum)
);
Konsistenzchecks
• Heiraten darf man nur, wenn
• man mindestens 18 Jahre alt ist (Mindestalter)
• man nicht bereits verheiratet ist (keine Polygamie)
• ...
1. Check: Mindestalter
create or replace
TRIGGER bitte_nicht_mit_kindern
before insert on ist_verheiratet_mit for each row
declare
cursor hochzeitsalter(p_hochzeitsdatum in date, er in number, sie in number) is select (p_hochzeitsdatum - p.geburtsdatum)/365 hochzeitsalter
from person p
where p.p_id in (er, sie);
begin
for data in hochzeitsalter(:new.hochzeitsdatum, :new.ehemann_fk,
:new.ehefrau_fk) loop if data.hochzeitsalter < 18 then
raise_application_error(-20002,'Mindestens einer der Lümmel ist zu '||
'jung um zu heiraten');
end if;
end loop;
end;
2. Check: keine Polygamie
create or replace
TRIGGER bitte_keine_polygamie
before insert on ist_verheiratet_mit for each row
declare
aktuelle_beziehungen number(1);
begin
select count(*)
into aktuelle_beziehungen from ist_verheiratet_mit
where (ehemann_fk=:new.ehemann_fk or ehefrau_fk=:new.ehefrau_fk) and (hochzeitsdatum < :new.hochzeitsdatum and
(trennungsdatum > :new.hochzeitsdatum or trennungsdatum is null));
if aktuelle_beziehungen > 0 then
raise_application_error(-20001,'Mindestens einer der Lümmel ist am '||
:new.hochzeitsdatum||' bereits verheiratet');
end if;
end;
/
Ablauf
insert into person (p_id, vorname, nachname, geburtsdatum) values (1, 'Peter', 'Schulz', '20.10.1939');
insert into person (p_id, vorname, nachname, geburtsdatum) values (2, 'Heide', 'Lauer', '26.9.1940');
insert into person (p_id, vorname, nachname, geburtsdatum) values (3, 'Monika', 'Krieger', '2.2.1941');
insert into ist_verheiratet_mit (ehemann_fk, ehefrau_fk, hochzeitsdatum) values (1,2,'11.10.1954');
insert into ist_verheiratet_mit (ehemann_fk, ehefrau_fk, hochzeitsdatum) values (1,2,'11.10.1964');
update ist_verheiratet_mit set trennungsdatum='28.2.1970'
where ehemann_fk=1 and ehefrau_fk=2 and hochzeitsdatum='11.10.1964';
insert into ist_verheiratet_mit (ehemann_fk, ehefrau_fk, hochzeitsdatum) values (1,3,'1.7.1968');
Ablauf
-- Eingabe der 3 Personen wie vorherige Seite
SQL> insert into ist_verheiratet_mit (ehemann_fk, ehefrau_fk, hochzeitsdatum) 2 values (1,2,'11.10.1954');
insert into ist_verheiratet_mit (ehemann_fk, ehefrau_fk, hochzeitsdatum)
*
FEHLER in Zeile 1:
ORA-20002: Mindestens einer der Lümmel ist zu jung um zu heiraten ORA-06512: in "SCAN0004.BITTE_NICHT_MIT_KINDERN", Zeile 15
ORA-04088: Fehler bei der Ausführung von Trigger 'SCAN0004.BITTE_NICHT_MIT_KINDERN' -- weitere statements wie auf vorheriger Folie ...
SQL> insert into ist_verheiratet_mit (ehemann_fk, ehefrau_fk, hochzeitsdatum) 2 values (1,3,'1.7.1968');
insert into ist_verheiratet_mit (ehemann_fk, ehefrau_fk, hochzeitsdatum) *
FEHLER in Zeile 1:
ORA-20001: Mindestens einer der Lümmel ist am 01.07.68 bereits verheiratet ORA-06512: in "SCAN0004.BITTE_KEINE_POLYGAMIE", Zeile 13
ORA-04088: Fehler bei der Ausführung von Trigger 'SCAN0004.BITTE_KEINE_POLYGAMIE'
