# Triggers

A trigger is a statement that is executed upon modification to the database.

We must specify:

* under what condition a trigger must be executed
* the action to be performed when the trigger is executed

For example, we can **use a trigger to enforce referential integrity**:

Say we have a relation called timeslot, and time\_slot\_id is not a primary key of timeslot, so we cannot create a foreign key constraint from another relation section to timeslot.

We can use a trigger on section and timeslot to enforce referential integrity:

```
create trigger timeslot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (select time_slot_id from time_slot)) /* time_slot_id not present in time_slot */
begin
 rollback
end;
```

* Triggering events can be **insert**, **delete** or **update**
* Triggers on update can be restricted to specific attributes

  Ex. `after update of takes on grade`
* Values of attributes before and after an update can be referenced as follows:
  * **referencing old row as**: for deletes and updates
  * **referencing new row as**: for inserts and updates
* Triggers can be activated before an event, which can serve as extra constraints\
  Ex. convert blank grades to null before they are inserted:

  ```
  create trigger setnull_trigger before update of takes
  referencing new row as nrow
  for each row
   when (nrow.grade = ‘ ‘)
   begin atomic
    set nrow.grade = null;
   end;
  ```

## Statement-Level Triggers

Instead of executing a separate action for each affected row, a single action can be executed for all rows affected by a transaction:

* Use **for each statement** instead of **for each row**
* Use **referencing old table** or **referencing new table** to refer to temporary tables (called *transition tables*) containing the affected rows

This is more efficient when dealing with SQL statements that update a large number of rows/