SQLite Triggers

SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a tiny, quick, self-contained, reliable, full-featured SQL database engine.

A trigger is a schema object that gets executed or “triggered” automatically when SQLite commands like INSERT, UPDATE, or DELETE are executed against a certain table. Triggers are mostly used in complex infrastructure that involves several databases and their tables, to keep a log of the changes made in that database or tables. Triggers can also be used to prevent invalid transactions in case of important and sensitive databases or tables.

In this article, We will see how we can create a trigger in SQLite and some of the examples where a trigger can be used, also we will see how we can delete the trigger using the DROP command.

As mentioned earlier, the trigger is a named database object (named because every trigger must have a unique name, no two triggers can associated with the same database or table can have the same name) especially used to respond when a specific event has occurred to let the user know that it has been successful. Trigger works with 3 SQLite commands –

• INSERT Trigger: It responds/triggers when some value or a new row is being inserted in the associated table.
• DELETE Trigger: It responds/triggers when some value or an entire row is being deleted from the associated table.
• UPDATE Trigger: It responds/triggers when some value or an entire row is being updated in the associated table.

Syntax:

CREATE TRIGGER  <Trigger_Name> 
   [ AFTER | BEFORE | INSTEAD OF ] [ INSERT | UPDATE | DELETE ] 
   ON <Table_Name>
   WHEN <condition>
BEGIN
 <Statements_Related _to_Condition>
END;

Explanation of the Syntax:

• 1st line: In the very first line we will use the CREATE TRIGGER command and then provide the trigger name.
• 2nd line: In the next line we have to provide the trigger type, means that when the trigger should respond, BEFORE the execution of a supported command or AFTER the execution of a supported command. INSTEAD OF Trigger can only be used in case of a view, not a table. After that we need to provide on which operation a Trigger should execute i.e INSERT, DELETE or UPDATE.
• 3rd Line: Now we have to mention that the trigger will be associated with which table using the ON command.
• 4th Line: Then the condition will be given using the WHEN command, on which a trigger will respond.
• From 5th Line Till End: Now from the 5th line i.e BEGIN we will configure the trigger, means what will the trigger do or how it should respond on execution of the decided command i.e INSERT UPDATE or DELETE. The END command marks the end of the trigger body.

In this section, We will see how we can create a trigger using the CREATE TRIGGER statement. For this tutorial, we will use an already created table called Students which consist of Student_ID, FirstName, LastName, Class and Section as Colums. The contents of that Students table is given below:

SQLite also supports two commands, NEW and OLD, using which we can see the changes made in the table and how it was previously. All the three supported Trigger operations doesn’t support both NEW and OLD together. The Following table signifies which operation is supported by which operation:

We will now create a Trigger on the Students Table which checks whether the FirstName and the LastName provided by the user is Alphabetic or not. If not, the trigger will raise an exception with a message and immediately stop the execution of the INSERT statement.

Write the below command to create the trigger:

Query:

CREATE TRIGGER check_alphabetic_names
BEFORE INSERT ON Students
BEGIN
    SELECT CASE
        WHEN NEW.FirstName IS NOT NULL AND NEW.LastName IS NOT NULL
             AND (NEW.FirstName GLOB '*[^a-zA-Z]*' AND NEW.LastName GLOB '*[^a-zA-Z]*') THEN
            RAISE(ABORT, 'Error: FirstName and LastName must be alphabetic')
    END;
END;

Explanation of the Query: Firstly, we are using the CREATE TRIGGER command to create a trigger and then providing it’s name, user can give any name. After that we are signifying the type of the Trigger i.e AFTER or BEFORE. Then we are mentioning that on execution of which statement the trigger will respond, here the trigger will respond on the execution of INSERT statement. Then we are providing the table name with which the Trigger will be associated with. Then the Trigger Body is starting with the BEGIN statement. As mentioned earlier, this trigger will look after that the datatype of the NEW FirstName and LastName is Alphabetic or not, also it will check that none of those two are kept as NULL. If the condition match, then the trigger will RAISE an ABORT statement with a message “Error:FirstName and LastName must be Alphabetic”, now this is an user provided message, the user can write any message they want to display. After that both the END statements marks the end of the enitre body and the WHEN statement.

Now we will try to insert a new row into the Students Table where the FirstName is not Alphabetic:

Query:

INSERT INTO Students VALUES (30,25,'Singh',10,'C');

Output:

Explanation: As we can see in the output, when we are trying to insert a new row into the Students table and passing the FirstName as a Number, there is a Runtime Error displayed by the SQLite with the message specified earlier.

Example 1: UPDATE Trigger

Here we will see how the UPDATE TRIGGER works in SQLite. We will create an UPDATE TRIGGER which will respond when any new row is being added to the Students Table.

To UPDATE TRIGGER to work properly, we need to first create another table which will hold all the logs of the said table with which the trigger will be associated. Let’s create that second table first.

Query:

CREATE TABLE Student_History
(
id INTEGER PRIMARY KEY,
Student_ID INTEGER NOT NULL,
OldClass TEXT NOT NULL,
NewClass TEXT NOT NULL,
change_time TIMESTAMP NOT NULL
);

Explanation: The above command will create a new table called Student_History which contains the columns id (which will store the ID of the Log), StudentID (Same as of the Students Table), OldClass (Will hold the value of the old class), NewClass (will hold the value of updated class) and ChangeTimestamp (This is for extra caution, this column will hold the timestamp at what time the value has been updated.)

Let’s Create an Trigger:

Query:

CREATE TRIGGER audit_student_update
AFTER UPDATE ON Students
BEGIN
INSERT INTO Student_History
VALUES (NULL, OLD.Student_ID,OLD.Class,NEW.Class, DATETIME('now'));
END;

Explanation: Here we are creating an UPDATE TRIGGER whose name is audit_student_update, this trigger will hold the updates made in the Students Table. After than we are providing the Trigger Body, in which we are using the INSERT INTO statement to insert the values we want to store in the Student_History Table, for this example the values are the OLD Student_ID, OLD Class, NEW Class and the Timestamp when the change has been made. As the id column of Student_History table has been made a INTEGER PRIMARY KEY, even though we are passing NULL value, SQLite will consider that column as auto-increment which starts from 1. If we try to give any explicit value, SQLite will throw an error.

Checking if the Trigger has Been Made Successfully –

To check if the trigger has been made successfully, write and execute the below command:

Query:

SELECT name FROM  sqlite_master WHERE type = 'trigger';

Output:

Explanation: As we can see in the output, the list of triggers has been given.

Now we will update the Student_ID of someone from the Students table and provide a new value to it.

Query:

UPDATE students SET class = 12 WHERE student_id = 21;

If we now print everything from the Student_History table we will receive the following output.

Query:

SELECT * FROM  Student_History;

Output:

Example 3: DELETE Trigger

Here we will see how to use the DELETE TRIGGER in SQLite. We will create a DELETE TRIGGER which will respond whenever there is a deletion operation happened on the students table. We will also create a different table in which we will store the values deleted and the timestamp when they were deleted.

Query:

CREATE TABLE Student_Delete_History 
(
    id INTEGER PRIMARY KEY,
    Student_ID INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Class TEXT,
    Section TEXT,
    change_time TIMESTAMP NOT NULL
);

Explanation: This table will hold all the values present in the Students table as well as the Timestamp when the values were deleted from the parent table Students.

We will now create a DELETE TRIGGER.

Query:

CREATE TRIGGER audit_student_deletion
AFTER DELETE ON Students
BEGIN
    INSERT INTO Student_Delete_History
    VALUES (NULL, OLD.Student_ID, OLD.FirstName, OLD.LastName, OLD.Class, OLD.Section, DATETIME('now'));
END;

Explanation: Here we are creating another trigger named audit_student_deletion, this will keep notice about the deleted rows from the Students Table and add the deleted values into another table named Student_Delete_History. Again as the id column of the mentioned table is being defined as an INTEGER PRIMARY KEY, SQLite will treat it as an auto-increment column, that’s why even after passing NULL values in the result we will get 1. This will also hold the TimeStamp of when the deletion happened.

Now we will try to delete a row from the Students table.

Query:

DELETE  FROM  students WHERE student_id = 12;

Now we will check if the deleted values/row has been added to the new table.

Query:

SELECT * FROM student_delete_history;

Output:

Explanation: We can clearly see in the output of the Student_Delete_History table, that the deleted row from the Students table has been added successfully here with the timestamp of when it was deleted.

Now we will see how we can delete a trigger using the DROP TRIGGER Statement of SQLite. The generic syntax of DROP TRIGGER statement is below:

Syntax:

DROP TRIGGER <Trigger_Name>;

The syntax is pretty simple, after the DROP TRIGGER command we just simply need to pass the Trigger name which we want to delete. For this example, we will DROP the latest trigger i.e Audit_Student_Deletion.

Query:

DROP TRIGGER Audit_Student_Deletion;

Now we will check the list of triggers to see it has been successfully deleted or not.

Query:

SELECT name FROM sqlite_master WHERE type = 'trigger';

Output:

Explanation: We can clearly see in the output that the trigger Audit_Student_Deletion has been successfully deleted.

We saw how the TRIGGER in SQLite can be used for several purposes and how it can track about the commands like INSERT UPDATE and DELETE. Triggers are mainly used as a tool to store the log files, this is why most of the times a new table should be created in which the logs can be stored. TRIGGERS are very useful when it comes to debugging or see the history of a table and how it has been changed periodically.