Summary: this tutorial discusses SQLite trigger, which is a database object fired automatically when the data in a table is changed.
What is an SQLite trigger
An SQLite trigger is a named database object that is executed automatically when an
DELETE statement is issued against the associated table.
When do we need SQLite triggers
You often use triggers to enable sophisticated auditing. For example, you want to log the changes in the sensitive data such as salary and address whenever it changes.
In addition, you use triggers to enforce complex business rules centrally at the database level and prevent invalid transactions.
CREATE TRIGGER statement
To create a new trigger in SQLite, you use the
CREATE TRIGGER statement as follows:
CREATE TRIGGER [IF NOT EXISTS] trigger_name
[BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]
In this syntax:
- First, specify the name of the trigger after the
- Next, determine when the trigger is fired such as
INSTEAD OF. You can create
AFTERtriggers on a table. However, you can only create an
INSTEAD OFtrigger on a view.
- Then, specify the event that causes the trigger to be invoked such as
- After that, indicate the table to which the trigger belongs.
- Finally, place the trigger logic in the
BEGIN ENDblock, which can be any valid SQL statements.
If you combine the time when the trigger is fired and the event that causes the trigger to be fired, you have a total of 9 possibilities:
INSTEAD OF INSERT
INSTEAD OF DELETE
INSTEAD OF UPDATE
Suppose you use a
UPDATE statement to update 10 rows in a table, the trigger that associated with the table is fired 10 times. This trigger is called
FOR EACH ROW trigger. If the trigger associated with the table is fired one time, we call this trigger a
FOR EACH STATEMENT trigger.
As of version 3.9.2, SQLite only supports
FOR EACH ROW triggers. It has not yet supported the
FOR EACH STATEMENT triggers.
If you use a condition in the
WHEN clause, the trigger is only invoked when the condition is true. In case you omit the
WHEN clause, the trigger is executed for all rows.
Notice that if you drop a table, all associated triggers are also deleted. However, if the trigger references other tables, the trigger is not removed or changed if other tables are removed or updated.
For example, a trigger references to a table named
people, you drop the
people table or rename it, you need to manually change the definition of the trigger.
You can access the data of the row being inserted, deleted, or updated using the
NEW references in the form:
NEW references are available depending on the event that causes the trigger to be fired.
The following table illustrates the rules.:
|NEW is available
|Both NEW and OLD are available
|OLD is available
SQLite triggers examples
Let’s create a new table called leads to store all business leads of the company.
CREATE TABLE leads (
id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
phone text NOT NULL,
email text NOT NULL,
source text NOT NULL
BEFORE INSERT trigger example
Suppose you want to validate the email address before inserting a new lead into the
leads table. In this case, you can use a
BEFORE INSERT trigger.
First, create a
BEFORE INSERT trigger as follows:
CREATE TRIGGER validate_email_before_insert_leads
BEFORE INSERT ON leads
WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
RAISE (ABORT,'Invalid email address')
We used the
NEW reference to access the email column of the row that is being inserted.
To validate the email, we used the
LIKE operator to determine whether the email is valid or not based on the email pattern. If the email is not valid, the
RAISE function aborts the insert and issues an error message.
Second, insert a row with an invalid email into the
INSERT INTO leads (first_name,last_name,email,phone)
SQLite issued an error: “Invalid email address” and aborted the execution of the insert.
Third, insert a row with a valid email.
INSERT INTO leads (first_name, last_name, email, phone)
VALUES ('John', 'Doe', 'email@example.com', '4089009334');
Because the email is valid, the insert statement executed successfully.
AFTER UPDATE trigger example
The phones and emails of the leads are so important that you can’t afford to lose this information. For example, someone accidentally updates the email or phone to the wrong ones or even delete it.
To protect this valuable data, you use a trigger to log all changes which are made to the phone and email.
First, create a new table called
lead_logs to store the historical data.
CREATE TABLE lead_logs (
id INTEGER PRIMARY KEY,
Second, create an
AFTER UPDATE trigger to log data to the
lead_logs table whenever there is an update in the
CREATE TRIGGER log_contact_after_update
AFTER UPDATE ON leads
WHEN old.phone <> new.phone
OR old.email <> new.email
INSERT INTO lead_logs (
You notice that in the condition in the
WHEN clause specifies that the trigger is invoked only when there is a change in either email or phone column.
Third, update the last name of
last_name = 'Smith'
id = 1;
log_contact_after_update was not invoked because there was no change in email or phone.
Fourth, update both email and phone of
John to the new ones.
phone = '4089998888',
email = 'firstname.lastname@example.org'
id = 1;
If you check the log table, you will see there is a new entry there.
You can develop the
AFTER INSERT and
AFTER DELETE triggers to log the data in the
lead_logs table as an exercise.
DROP TRIGGER statement
To drop an existing trigger, you use the
DROP TRIGGER statement as follows:
DROP TRIGGER [IF EXISTS] trigger_name;
In this syntax:
- First, specify the name of the trigger that you want to drop after the
- Second, use the
IF EXISTSoption to delete the trigger only if it exists.
Note that if you drop a table, SQLite will automatically drop all triggers associated with the table.
For example, to remove the
validate_email_before_insert_leads trigger, you use the following statement:
DROP TRIGGER validate_email_before_insert_leads;
In this tutorial, we have introduced you to SQLite triggers and show you how to create and drop triggers from the database.