Database trigger

A database trigger , usually only trigger called ( English for trigger ) is a function of various database management systems , especially large relational database management systems.

In one particular type of changes (. For example INSERT, UPDATE, DELETEin SQL ) data in a table a stored program is called up, which allows this change, prevented, and / or performs other activities.

properties

Triggers are used, among other things, to maintain data consistency (integrity checks) and to insert, delete or change reference data. The trigger is executed ("fired") either before the change is made to the referenced table or afterwards. Some relational database management systems (RDBMS) also offer an “Instead of Insert” trigger, which allows other SQL statements to be executed instead of the INSERT operation.

You have to specify whether the trigger should be triggered for each changed data record or for each instruction call. Example: If an update instruction ( UPDATE) changes 100 records, the trigger is activated 100 times in the first case and only once in the second case.

Since triggers can insert, change and delete data records themselves, a trigger can trigger further triggers when executed. A whole chain of processing can be initiated in this way.

To create triggers, the various RDBMS have built-in procedural programming languages such as PL / pgSQL from PostgreSQL , PL / SQL (from Oracle ) or SQL PL (from IBM DB2 ). With some RDBMS, stored procedures can also be called within trigger processing . This enables a program written in a different programming language (C, COBOL, Java) to be called. With the RDBMS mentioned here, triggers are possible both on tables and on views .

In addition to SQL standard triggers that are executed before and after the firing event, there are also INSTEAD OF triggers that are used instead of the firing event in Oracle, IBM DB 2, Microsoft SQL Server and PostgreSQL - not in the SQL standard Event. INSTEAD OF triggers can also be defined on a view.

See also : event (programming)

Examples of triggers

Trigger PL / SQL syntax

   CREATE [OR REPLACE] TRIGGER <trigger_name>
    {BEFORE|AFTER|INSTEAD OF} {INSERT|DELETE|UPDATE [OF <feld_name>]}
     ON <table_name>
      [REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]
        [FOR EACH ROW [WHEN (<trigger_condition>)]]
        <trigger_body>

PL / SQL code example

 CREATE TRIGGER mitarbeiter_trig_gehalt_biu
 BEFORE INSERT OR UPDATE OF gehalt ON mitarbeiter
 FOR EACH ROW
  -- Triggername mitarbeiter_trig_gehalt_biu
  -- (_biu steht für B-efore I-nsert U-pdate )
  -- Tabelle: MITARBEITER
  -- Trigger soll nur auslösen,
  -- wenn Gehalt kleiner oder gleich 5000 ist oder neues Gehalt kleiner altes Gehalt
  -- in der folgenden WHEN-Klausel erfolgt der Zugriff auf :old und :new OHNE das Zeichen :
 WHEN (new.gehalt <= 5000 or new.gehalt < old.gehalt)
 DECLARE
   v_mitarbeiter_name VARCHAR2(255):=null;
   -- Cursor holt "Name, Vorname" von Mitarbeitertabelle
   -- Explizite Definition des Cursors hat
   -- Performance-Vorteile gegen "select into <variable>" im Trigger-Body
   cursor c_name is select name||', '||vorname
                    from mitarbeiter
                    where mitarbeiternummer = :NEW.MITARBEITERNR;
 BEGIN
   -- Mitarbeiter-Name ermitteln und in Variable v_mitarbeiter_name speichern
   open c_name;
   fetch c_name into v_mitarbeiter_name;
   close c_name;
   -- Bei Gehalt kleiner oder gleich 5000 Exception auslösen
      --> Fehler wird in der Regel bis zur Anwendung hochgereicht
   -- Exception-Nr -20000 - -29999 können selbst definiert werden
   IF :NEW.GEHALT <= 5000 THEN
     RAISE_APPLICATION_ERROR(
         -20901,
         'Managergehalt für Mitarbeiter '||v_mitarbeiter_name||' zu klein!'
     );
   END IF;
   IF :NEW.GEHALT < :OLD.GEHALT THEN
     RAISE_APPLICATION_ERROR(
         -20902,
         'Gehaltskürzung für Mitarbeiter '||v_mitarbeiter_name||' nicht erlaubt!'
     );
   END IF;
 END;
 /

literature

• H. Faeskorn-Woyke, B. Bertelsmeier, P. Riemer, E. Bauer: Database systems, theory and practice with SQL2003, Oracle and MySQL. Pearson studies, Munich / Boston 2007, ISBN 978-3-8273-7266-6 .
• Klaus R. Dittrich, Angelika M. Kotz, Jutta A. Mülle: An event / trigger mechanism to enforce complex consistency constraints in design databases. In: ACM SIGMOD. 15/3/1986, ACM Press, New York, pp. 22-36, ISSN  0163-5808 , doi: 10.1145 / 15833.15836

Web links

Wiktionary: Database triggers  - explanations of meanings, word origins, synonyms, translations

• ECMA rules
• INSTEAD OF TRIGGER
• Trigger (German)

Individual evidence

1. ↑ PostgreSQL: Documentation: 10: CREATE TRIGGER. Retrieved August 11, 2018 .