SQL triggers are a type of stored procedures which are executed based on certain events that take place in the database.

Structure

CREATE TRIGGER [name_of_trigger]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[sql statements]

1. CREATE TRIGGER [name_of_trigger] - Creates a trigger and the name of the trigger.
2. [before | after] - The trigger will execute before or after.
3. {insert | update | delete} - The trigger will execute on one of these operations.
4. on [table_name] - The name of the table.
5. [for each row] - Trigger on a row-level.
6. [sql statements] - When the trigger is fired, carry out these statements.

Example

Suppose we have a table with users and the the subtotal of their purchases. Let’s also assume that there is a 10% tax on each of these purchases and that before inserting any new rows, we want to know what the total is for each subtotal. Here’s how we calculate it using a trigger:

CREATE TRIGGER calc_total
before insert
on
user_purchases
for each row
set purchases.total = purchases.subtotal * 10%;