# 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/


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://vikram-bajaj.gitbook.io/cs-gy-6083-principles-of-database-systems/main-1/sql/advanced-sql/triggers.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.