Oracle: Trigger
Was ist und macht ein Trigger
BearbeitenTrigger sind eventgesteuerte Prozeduren, die automatisch bei bestimmten Ereignissen durchgeführt werden.
Es gibt 3 Auslöser
- INSERT
- UPDATE
- DELETE
Zusätzlich kann noch der Ausführungszeitpunkt bestimmt werden
- BEFORE - vor der Änderung
- AFTER - nach der Änderung
- INSTEAD OF - anstelle der Änderung
Seit Oracle 9i können Trigger für folgende weitere Ereignisse definiert werden:
- DDL-Statements: CREATE, ALTER, DROP
- An- und Abmeldungen: LOGON, LOGOFF
- Start/Stop der Datenbank: STARTUP, SHUTDOWN
- Bei Systemfehler: SERVERERROR
Ein weiteres Kriterium ist, wie oft der Trigger gestartet werden soll
- ROW-Trigger: for each row
- werden pro geänderter Zeile ausgeführt
- Haben dadurch Zugriff auf die Attribute des Tupels vor und nach Ausführung des Triggers
- Anwendungsbeispiel: Protokollierung, Überprüfung von Aktionen, ...
- Statement-Trigger: for each statement
- werden pro ausgeführtem Statement einmalig ausgeführt, egal wieviele Zeilen betroffen sind
- Inhalt der Tupel ist nicht bekannt
- Anwendungsbeispiel: Zugriffsschutz, ...
Syntax eines Triggers
Bearbeiten create or replace trigger <triggername>
before/after insert or update or delete
on <tablename>
REFERENCING NEW AS <newROW> OLD AS <oldROW>
for each row/for each statement
when (<Bedingung>)
DECLARE
variablen deklaration
BEGIN
if INSERTING then
...
end if;
if UPDATING then
...
end if;
if DELETING then
...
end if;
EXCEPTION
Fehlerbehandlung
END <triggername>;
Nur in ROW-Triggern werden die Alten (old) und Neuen (new) Werte der Tabelle zur Verfügung gestellt
Syntax bei Bedingungen: new.spaltenname, old.spaltenname
Syntax bei Aktionen: :new.spaltenname, :old.spaltenname
Bei BEFORE-Triggern besteht die Möglichkeit die NEW-Werte zu ändern
Weitere Infos: http://www.psoug.org/reference/instead_of_trigger.html
Dictionary-View zu Triggern
Bearbeitenselect * from user_triggers
Sequenzen und Timestamps in den Triggern (Vorlage)
Bearbeiten-- (ersetze Platzhalter <% %>.)
CREATE SEQUENCE SQ_<%tableName%> START WITH 1 INCREMENT BY 1 MINVALUE 1;
CREATE OR REPLACE TRIGGER TS_<%tableName%>
BEFORE INSERT OR UPDATE
ON <%tableName%>
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF (INSERTING) THEN
--SELECT SYSDATE INTO :NEW.CHG_DATE FROM DUAL;
--SELECT SYSDATE INTO :NEW.CRE_DATE FROM DUAL;
IF (:NEW.<%pkFieldName%> IS NULL) THEN
SELECT SQ_<%tableName%>.NEXTVAL INTO :NEW.<%pkFieldName%> FROM DUAL;
END IF;
ELSIF (UPDATING) THEN
--SELECT SYSDATE INTO :NEW.CHG_DATE FROM DUAL;
END IF;
END;
-- Kein Setzen von SEQUENCE_NAME.nextval und von Timestamp in den Insert / Update Abfragen ist nun notwendig.
-- INSERT:
-- statt:
INSERT INTO tabelle1 (id, name, CRE_DATE, CHG_DATE) VALUES (SEQUENCE_NAME.nextval, 'Test', SYSDATE, SYSDATE );
-- nun:
INSERT INTO tabelle1 (name) VALUES ('Test');
-- UPDATE:
-- statt:
UPDATE tabelle1 SET name='Test 2', CHG_DATE = SYSDATE WHERE id = 1;
-- nun:
UPDATE tabelle1 SET name='Test 2' WHERE id = 1;