SQL: Database Triggers

So, I have written a number of posts on using SQL. I have gone over how to create databases and tables, insert/drop data from tables, and how to select/filter data from a table. Now what if we had a large dataset and we wanted certain actions to happen automatically? That is where the subject of database triggers come in.

First, how might database triggers be helpful? Well, I’m sure that many of can think of multiple actions we might want to be automatic rather than waiting and doing it ourselves, but let’s take for example an online store.

The first thing to consider is the schema of an online store database. If you have been practicing SQL for a while, you might have come across a common example where you have a database with tables such as clients, orders, payments, employees, and products.

Now, what type of actions might we want to be automatic? One to consider is stock quantity. Sometimes when you go to an online store to view a certain product, the website will mention that there are only 14 left in stock. How do they know that? Obviously they check the stock quantity in the products table, but they also need to make sure that that number is correct in real time. If you only have a small number in stock, it would be unfortunate if your employee updated the stock quantity at the end of the day and found out that more products had been sold than there were in stock. This is a case where a database trigger could be really useful. As soon as someone confirms their purchase, a trigger can be tripped and cause the stock quantity to drop by one.

So, we now have an example of why we would want to use a database trigger. Let us next take a look at the syntax of a database trigger.

Database Trigger Syntax

CREATE TRIGGER <trigger_name>
    <trigger_time> <trigger_event> ON <table_name> FOR EACH ROW
    BEGIN
        <procedure>
    END;

Above, we have the syntax for a database trigger and it can look a bit intimidating. We will go over each variable in sequence.

1. CREATE TRIGGER : This statement is like any other create statement and will start the creation process of the trigger.
2. <trigger_name> : You have to give the trigger a unique name to call on.
3. <trigger_time> : This is where you determine when you want to trip the trigger. The options are BEFORE SQL completes the action or AFTER .
4. <trigger_event> : This is the event or action that trips the trigger, such as INSERT , Delete , or UPDATE .
5. <table_name> : the name of table that the trigger is watching.
6. <procedure> : This describes that actions that should be taken when the trigger is tripped.

So, overall you can create a trigger that can store a sequence of actions to take before or after the action that tripped the trigger in the first place.

Let’s return to our example with an online store.

Database Trigger Example

So, going back over our first example: we have an online store and we are interested in the orders and products table. After an order is made, we want to update the products table by decreasing the quantity of the product by one.

The first step is creating the trigger:

CREATE TRIGGER update_quantity

The second step is to determine the time and event that we want the trigger to look for:

CREATE TRIGGER update_quantity
    AFTER INSERT ON orders FOR EACH ROW

Finally we want to create the sequence that is to be followed when the trigger is tripped:

DELIMITER $$
CREATE TRIGGER update_quantity
    AFTER INSERT ON orders FOR EACH ROW
    BEGIN
        UPDATE products
        SET quantity = quantity - 1
        WHERE products.id = NEW.product_id;
    END$$
DELIMITER ;

Now we have a trigger that will update the quantity by subtracting one from the total. You have probably also noticed a couple new statements as well.

The DELIMITER changes what ends the written code in a console. When writing the trigger we want to have the procedure end with a ; , however if we did so while writing in the console, we wouldn’t be able to finish creating our trigger. So we changed the DELIMITER to $$ so we could end the procedure section with a ; . Once we finished creating the trigger, we change the DELIMITER back to ; so when it is triggered, the procedure can be completed.

The other change is NEW. When creating a trigger, it creates two pseudo-records: OLD and NEW . What they equal depends on the trigger_event .

1. INSERT : If the trigger_event is an INSERT , OLD is empty and NEW is the data being inserted.
2. UPDATE : If the trigger_event is an UPDATE , OLD is the old information and NEW is the updated data values.
3. DELETE : If the trigger_event is a DELETE , OLD is the deleted data and NEW is empty.

Warnings about Triggers

A few things to know about triggers, is that they can be hard to debug. Another warning is that if you have too many triggers, one trigger may cause another trigger to activate. If you are not careful, it can cause a cascade of triggers that you didn’t intend.

I hope this post helps your understanding of using database triggers in SQL. Thanks for reading!