Datenbank/Triggers und Stored Functions

Aus SELFHTML-Wiki
Wechseln zu: Navigation, Suche

Trigger

Was sind Trigger?

Trigger kann man übersetzen als "Auslöser". Den JavaScript-Programmierern sind diese auch bekannt, als "Events", wie z. B. "onLoad", "onChange" usw. Bei MySQL versteckt sich hinter dem Trigger gleich noch die komplette Handlungsanweisung. Sie ist fest mit dem Auslöser verbunden.

Pro Tabelle sind folgende Trigger möglich:

  • Before Insert
  • Before Update
  • Before Delete
  • After Insert
  • After Update
  • After Delete

Wird z. B. ein Trigger BEFORE UPDATE ON `tablename` definiert und nun ein update `tablename`" veranlasst, springt der Trigger an. Die hinterlegten Handlungsanweisungen werden (sofern sie keine Fehler enthalten) ausgeführt. Scheitert der Trigger, scheitert auch das Update.

Testtabelle anlegen

Wie man leicht erkennen kann, wird unsere Testtabelle geeignet sein, ein ganz einfaches kleines Forum damit aufzubauen.


Testtabelle anlegen 
CREATE TABLE `postings` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `posting` VARBINARY(254) NOT NULL COMMENT 'Binary segmented key for sort order',
    `update_count` BIGINT(20) NOT NULL DEFAULT '0',
    `subject` VARCHAR(500) NOT NULL,
    `bodytext` MEDIUMTEXT NOT NULL,
    `notinmenue` TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'Not listed, blocked, or similar',
    `start` DATETIME NOT NULL COMMENT 'Anzeigen ab',
    `stop` DATETIME NOT NULL COMMENT 'Anzeigen bis',
    `id_creator` BIGINT(20) NOT NULL COMMENT 'Anleger',
    `id_owner` BIGINT(20) NOT NULL COMMENT 'Eigentümer',
    `creator_ip` VARCHAR(15) NOT NULL,
    `create_date` DATETIME NOT NULL,
    `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `id_update_user` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'Last updated by',
    `update_ip` VARCHAR(15) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE INDEX `posting` (`posting`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1;

Anhand der Testtabelle wollen wir ein paar Konzepte für Triggers, Functions, Procedures und Routines ausprobieren.

Vorgesehene Aufgaben für unsere Triggers

  • Beim Insert automatisch das Create_Date eintragen
  • Beim Insert automatisch die Spalte `posting` berechnen.
  • Beim Insert automatisch den Owner auf den Wert des Creators setzen, wenn Owner nicht angegeben wurde
  • Beim Update darauf achten, dass zwischenzeitlich kein Update eines anderen Users stattgefunden hat

Einen Trigger einrichten

Um Trigger einzurichten oder zu löschen benötigt der angemeldete User das Trigger-Recht MySQL-Manual: GRANT

Als ersten Trigger richten wir uns einen Insert-Trigger auf unsere Tabelle ein, der immer dann aktiv wird, wenn ein neuer Datensatz eingefügt wird. Solange MySQL noch keine Funktionen als Default-Werte unterstützt, ist es z. B. eine sinnvolle Aufgabe für einen solchen Insert-Trigger, das Erstellungsdatum des Datensatzes in die Spalte create_date einzutragen.

Bevor wir aber einen neuen Insert-Trigger definieren, müssen wir einen eventuell schon vorhandenen löschen. Für jede Tabelle können nur je ein "Before-Trigger" und ein "After-Trigger" pro Insert, Update oder Delete definiert werden.

Wenn wir den Trigger über den MySQL-Client oder eine API einrichten (zu Heidi kommen wir später), müssen wir die Delimiters für Statements beachten. Da der Trigger nachher aus mehreren Statements besteht, müssen diese für die Einrichtung gebunden werden. Wir weisen die Schnittstelle daher mit der Anweisung DELIMITER $$ an, für den Codeblock des Triggers während des Einrichtungs-Statements nicht den normalen Statement-Begrenzer zu beachten, sondern $$. Welcher Delimiter hier gewählt wird, ist nahezu unerheblich. Er darf nur nicht im Trigger-Code selbst enthalten sein.

Nachdem wir mit dem Trigger-Statement fertig sind, schalten wir den Delimiter wieder zurück.

Trigger einrichten 
DROP TRIGGER IF EXISTS `postings_before_insert`;
DELIMITER $$
 
CREATE DEFINER=`root`@`localhost` TRIGGER `postings_before_insert` BEFORE INSERT ON `postings` 
FOR EACH ROW 
 
BEGIN
 
    SET NEW.create_date = now();
 
END$$

DELIMITER ;

Mit FOR EACH ROW aktiviert der Trigger für jede veränderte Zeile der Tabelle seine zugeordnete Prozedur. Lässt man die Anweisung weg, hat man einen "Statement-Trigger" vorliegen, der nur einmal für das (übergeordnete) Statement ausgeführt wird, auch wenn mehrere Zeilen der Tabelle betroffen sind.

In der Variablen "NEW.<varname>" steht der neue Wert für die Spalte. Wurde im Insert-Statement ein Spaltenwert angegeben, steht dieser innerhalb des Triggers in dieser Variable zur Verfügung. Wir belegen die Variable aber hier mit dem Wert der Funktion now() und überschreiben dadurch ggf. einen im Insert-Statement übergebenen Wert.

Mögliche Konstrukte in Triggers

Variablen deklarieren und belegen

Ohne Variablen geht meistens nichts. In Triggers, Functions und Procedures müssen Variablen deklariert werden, bevor sie benutzt werden können.

Variablen deklarieren und belegen 
DECLARE <variable1, variable2, ...> <type>;

DECLARE counter INT DEFAULT 4711;

set <variable1> = <value1>;
set <variable2> = <value2>;

Bedingungen

Bedingungen 
IF <bedingung> THEN 
   <Anweisung(en)>;
ELSEIF <bedingung> THEN 
   <Anweisung(en)>;
ELSE
   <Anweisung(en)>;
END IF;

Schleifen

Schleifen definieren 
-- Simple While Loop --

WHILE <Bedingung> DO
    <Anweisungen>;
END WHILE;



-- Labled While Loop, similar to "break" --

<label>: WHILE <Bedingung> DO
    <Anweisung>;
    IF <Bedingung> THEN
        LEAVE <label>;
    END IF;
END WHILE <label>;



-- labled While Loop, similar to "continue"

<label>: WHILE <Bedingung 1> DO 
    <Anweisung 1>;
    <Anweisung 2>;

    IF <Bedingung 2> THEN
        ITERATE <label>;  -- Rücksprung zu <label>:
    END IF;

    <Anweisung 3>;
    <Anweisung 4>;
END WHILE <label>;

Funktionsaufrufe

Funktionen aufrufen 
SET <variable> = function();

Exceptions in Triggers, Functions oder Procedures formulieren

Seit MySQL 5.5(?) wird ein Signalling System für Messages, Warnings und Exceptions in Triggers, Functions und Procedures bereitgestellt. Die Anwendung ist allerdings etwas gewöhnungsbedürftig:

Der Aufruf findet über

SIGNAL sqlstate 'CCFFF';

statt. wobei CC = Signal-Klasse und FFF eine beliebige nicht wirklich allgemeingültig festgelegte Fehlernummer darstellt. Jeder Datenbankentwickler hat hierzu eigene Ideen. Eine kleine gemeinsame Menge findet man in den ANSI-Vereinbarungen.

(siehe Anhang A)

SQL-States:

  • Class = '00' (success);
Illegal. SQLSTATE values that begin with '00' indicate success and are not valid for SIGNAL.
  • Class = '01' (warning);
The value of the warning_count system variable goes up. SHOW WARNINGS shows the signal.
SQLWARNING handlers catch the signal. If the signal is unhandled in a function, statements do not end.
  • Class = '02' (not found);
NOT FOUND handlers catch the signal. There is no effect on cursors. If the signal is unhandled in a function, statements end.
  • Class > '02' (exception);
SQLEXCEPTION handlers catch the signal. If the signal is unhandled in a function, statements end.
  • Class = '40';
Treated as an ordinary exception.


bis Class 44 sind die Codes belegt von irgendjemand, 45 ist tatsächlich die erste freie Klasse, danach folgen irgendwann wieder belegte. Das ist alles nur irgendwie festgelegt...

Error: 1644 SQLSTATE: HY000 (ER_SIGNAL_EXCEPTION).

HY000 = MySQL-Error not mapped to SQL-STATES, also eine "Dummy-SqlState".

Exceptions im Trigger einrichten 
DROP TRIGGER IF EXISTS `posting_before_update`;
delimiter $$

CREATE DEFINER=`root`@`localhost` TRIGGER `postings_before_update` BEFORE UPDATE ON `postings` 
FOR EACH ROW 

BEGIN

    declare msg varchar(255);
        
    IF new.update_count <> (old.update_count + 1)  then
        set msg = concat('9001: Record has been updated in the meantime: ', cast(old.update_count as char));
        SIGNAL sqlstate '45001' set message_text = msg;
    ELSEIF mod(length(new.posting), 2) <> 0 then
        set msg = concat('9002: Wrong format for `posting`: ');
        SIGNAL sqlstate '45002' set message_text = msg;
    else
        set new.update_count = old.update_count + 1;
    end if;

END$$

delimiter ;

Der "Academic-Lock"-Trigger

Die Bezeichnung "Academic" ist eine kleine Anleihe auf das "ct" von Vorlesungen und akademischen Veranstaltungen. Ich übersetze es hier mal mit "mit Zeitverzögerung". Die Datensatzsperre findet also nicht physisch statt, sondern logisch. Sie wirkt erst mit Zeitverzögerung.

Bei abgesicherten Lese-Schreibvorgängen (Updates) muss ein Datensatz vor dem Lesen gegen Veränderung gesperrt werden und darf erst nach dem Schreiben wieder freigegeben werden. Im Webumfeld liegen zwischen dem Lesen und dem Schreiben oft mehrere Roundturns und damit eine lange Zeit. Außerdem ist es nicht möglich, in einem zustandslosen Protokoll einen Datensatz auf Filesystem/Datenbankebene tatsächlich vom Lesebeginn bis zum Schreibende für einen User zu sperren, da es keine Möglichkeit gibt festzustellen, ob der User nach dem Sperren und Lesen überhaupt noch da ist. Man würde also Gefahr laufen, "Lost Locks" (verlorene Sperren) zu produzieren.

Wir haben daher einen Merker im Datenmodell untergebracht: `update_count`.

Mit jedem Update wird nun der Merker um eins hochgezählt. Nur, wenn der im Update-Statement vorgegebene Wert für `update_count` mit dem erwarteten übereinstimmt, wird das Update durchgeführt. Anderenfalls hat vermutlich schon ein anderer Teilnehmer eine Schreiboperation durchgeführt.

Wir überwachen diesen Wert mit einem Trigger, und im Fehlerfall löst unser überwachender Trigger dann eine Exception aus und verhindert das Update.


Warum update_count + 1?

Formulierung für den Client

Mehrere Überprüfungen in Triggern

es gibt nur den einen Trigger

Stored Functions

ToDo (weitere ToDos)

  • Stored Functions
    • Was sind stored Functions?
    • eine einfache Umwandlungsfunktion
    • Aufruf von stored Functions in Triggers
  • Stored Procedures
    • was sind Stored Procedures?
    • Übergabewerte, Rückgabewerte
    • Eine "Select-Kontrolle" aufbauen
    • Vertikale Zugriffsrechte

In einem gemeinsamen Datenbestand gibt es meistens einen umfangreichen Bedarf an Benutzerrechten. Horizontale Benutzerrechte lassen sich problemlos in der Datenbank konfigurieren. Welcher User hat auf welche Spalte welche Rechte?

Vertikale Rechte - welcher User darf mit welchem Datensatz was tun - können wir mit stored Procedures einrichten und überprüfen.

  • Stored Routines
    • die Komponenten von Stored Routines
    • Stored Routines als Trennschicht zwischen Datenbank und API

Siehe auch:

Abgerufen von „http://wiki.selfhtml.org/index.php?title=Datenbank/Triggers_und_Stored_Functions&oldid=96725“