SQL Triggers

name: portada
layout: true
class: portada-slide, middle, right
---

# SQL Triggers
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Triggers

__Triggers__ (__triggers__) are a set of SQL statements that are executed
when certain events happen.

Events which trigger can be specified for are:
- __INSERT__: When a value is inserted into a table.
- __UPDATE__: When a value is updated in a table.
- __DELETE__: When a value is deleted from a table.

Triggers can be defined to execute __BEFORE__ or __AFTER__ each event.
- Example: `BEFORE INSERT` will be executed before a value is inserted into a table.

.center[[MariaDB Triggers](https://mariadb.com/kb/en/trigger-overview/)]

---
# Create Trigger
.center[[MariaDB CREATE TRIGGER](https://mariadb.com/kb/en/create-trigger/)]

```
CREATE [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES } other_trigger_name ]
    trigger_stmt;
```
- Example:

```
CREATE TRIGGER increment_animal 
    AFTER INSERT ON animals 
    FOR EACH ROW 
    UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
```

---
# Trigger Variables
- In a __INSERT__ or __UPDATE__ trigger, the new data can be accessed with the __NEW__ vairable.
- In a __DELETE__ or __UPDATE__ trigger, the old data can be accessed with the __OLD__ vairable.

```
CREATE TRIGGER new_value
    AFTER INSERT ON values
    FOR EACH ROW 
    UPDATE history SET history.new_value = NEW.value;

CREATE TRIGGER last_value
    AFTER DELETE ON values
    FOR EACH ROW 
    UPDATE history SET history.last_value = OLD.value;
```

---
# Complex triggers
Triggers can consist of multiple statements enclosed by a __BEGIN__ and __END__ (just like procedures).

```
DELIMITER //
CREATE TRIGGER the_mooses_are_loose
    AFTER INSERT ON animals
    FOR EACH ROW
    BEGIN
        IF NEW.name = 'Moose' THEN
            UPDATE animal_count
            SET animal_count.animals = animal_count.animals + 100;
        ELSE 
            UPDATE animal_count
            SET animal_count.animals = animal_count.animals + 1;
        END IF;
    END; //

DELIMITER ;
```

---
# Abort Trigger and cancel event
Triggers can be aborted and the event cancelled if
a exception is thrown.

Exceptions canbe thrown with the __SIGNAL__ statement:
```
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Exception message';
```

---
# Trigger Advantages
- Triggers provide another way to check the integrity of data
- Counteracting invalid exchanges
- Triggers handle errors from the database layer
- Normally triggers can be useful for inspecting the data changes in tables
- Triggers give an alternative way to run scheduled tasks. 
  Using triggers, we don’t have to wait for the scheduled events to run because the triggers are 
  invoked automatically before or after a change is made to the data in a table

---
# Trigger Disadvantages
- Triggers may increase the overhead of the database
- Triggers can be difficult to troubleshoot because they execute automatically 
  in the database, which may not invisible to the client applications

---
# Bibliography
- https://www.edureka.co/blog/triggers-in-sql/