SQLite Trigger, Views & Index with Example

By : Richard Peterson Richard Peterson
Hours Updated

In the daily use of SQLite, you will need some administrative tools over your database. You can also use them to make querying the database more efficiently by creating indexes, or more reusable by creating views.

SQLite View

Views are very similar to tables. But Views are logical tables; they are not stored physically like tables. A view is composed of a select statement.

You can define a view for your complex queries, and you can reuse these queries whenever you want by calling the view directly instead of rewriting the queries again.

CREATE VIEW statement

To create a view on a database, you can use the CREATE VIEW statement followed by the view name, and then put the query you want after that.

Example: In the following example we will create a View with the name “AllStudentsView” in the sample database “TutorialsSampleDB.db” as following:

Step 1) Open My Computer and navigate to the following directory “C:\sqlite” and then open “sqlite3.exe“:

SQLite View

Step 2) Open the database “TutorialsSampleDB.db” by the following command:

SQLite View

Step 3) Following is a basic syntax of sqlite3 command to create the View

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

There should be no output from the command like this:

SQLite View

Step 4) To ensure that the view is created, you can select the list of views in the database by running the following command:

SELECT name FROM sqlite_master WHERE type = 'view';

You should see the view “AllStudentsView” is returned:

SQLite View

Step 5) Now our view is created, you can use it as a normal table something like this:

SELECT * FROM AllStudentsView;

This command will query the view “AllStudents” and select all the rows from it as shown in the following screenshot:

SQLite View

Temporary Views

Temporary views are temporary for the current database connection used to create it. Then if you close the database connection all the temporary views will be deleted automatically. Temporary views are created using one of the following commands:

  • CREATE TEMP VIEW, or
  • CREATE TEMPORARY VIEW.

Temporary views are useful if you want to do some operations for that time being and don’t need it to be a permanent view. So, you just create a temporary view, then do your processing using that view. Later when you close the connection with the database, it will be deleted automatically.

Example:

In the following example, we will open a database connection, then create a temporary view.

After that, we will close that connection, and we will check whether the temporary view still exists or not.

Step 1) Open sqlite3.exe from the directory “C:\sqlite” as explained before.

Step 2) Open a connection to the database “TutorialsSampleDB.db” by running the following command:

.open TutorialsSampleDB.db

Step 3) Write the following command that will create a temp view “AllStudentsTempView” :

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

SQLite View

Step 4) Ensure that the temp view “AllStudentsTempView” is created by running the following command:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite View

Step 5) Close sqlite3.exe and open it again.

Step 6) Open a connection to the database “TutorialsSampleDB.db” by the following command:

.open TutorialsSampleDB.db

Step 7) Run the following command to get the list of temporary view created on the database:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

You shouldn’t see any output as the temp view we created is deleted when we closed the database connection in the previous step. Otherwise, as long as you keep the connection with the database opened, you would be able to see the temporary view with data.

SQLite View

Notes:

  • You cannot use the statements INSERT, DELETE, or UPDATE with views, only you can use “select from views” command as shown in step5 in the CREATE View example.
  • To delete a VIEW, you can use the “DROP VIEW” statement:
DROP VIEW AllStudentsView;

To ensure that the view is deleted, you can run the following command which gives you the list of views in the database:

SELECT name FROM sqlite_master WHERE type = 'view';

You will find no views returned as the view was deleted, as following:

SQLite View

SQLite Index

If you have a book, and you want to search for a keyword on that book. You will search for that keyword in the index of the book. Then you will navigate to the page number for that keyword to read more information about that keyword.

However, if there is no index on that book nor page numbers, you will scan the whole book from the beginning to the end until you find the keyword you are searching for. And this is very difficult especially when you have an index and very slow process to search for a keyword.

Indexes in SQLite (and the same concept valid for other database management systems as well) works in the same way as the indexes found in the back of the books.

When you search for some rows in an SQLite table with search criteria, SQLite will search on all the rows of the table until it finds the rows you are looking for that matches the search criteria. And that process becomes very slow when you have larger tables.

Indexes will speed up search queries for data and will help to perform data retrieving from tables. Indexes are defined on the table columns.

Improving performance with Indexes:

Indexes can improve the performance of searching data on a table. When you create an index on a column, SQLite will create a data structure for that index where each field value has a pointer to the whole row where the value belongs.

Then, if you run a query with a search condition on a column that is part of an index, SQLite will look up for the value on the index first. SQLite won’t scan the whole table for it. Then it will read the location where the value points for the table row. SQLite will locate the row on that location and retrieve it.

However, if the column you are searching for, is not a part of an index, SQLite will perform a scan for the column values to find the data that you are looking for. It will be usually a slower process if there is no index.

Imagine a book with no index on it and you need to search for a specific word. You will scan the whole book from the first page to the last page looking for that word. However, if you have an index on that book, you will look for the word on it first. Get the page number where it is located, and then navigate to it. Which will be much faster than scanning the whole book from cover to cover.

SQLite CREATE INDEX

To create an index on a column, you should use the command CREATE INDEX. And you should define it as follows:

  • You have to specify the name of the index after the CREATE INDEX command.
  • After the name of the index, you have to put the keyword “ON”, followed by the table name in which the index will be created.
  • Then the list of column names that are used for the index.
  • You can use one of the following keywords “ASC” or “DESC” after any column name to specify a sort order used to order the index data.

Example:

In the following example, we will create an index “StudentNameIndex” on the students table in the “Students” database as following:

Step 1) Navigate to the folder “C:\sqlite” as explained before.

Step 2) Open sqlite3.exe.

Step 3) Open the database “TutorialsSampleDB.db” by the following command:

.open TutorialsSampleDB.db

Step 4) Create a new index “StudentNameIndex” using the following command:

CREATE INDEX StudentNameIndex ON Students(StudentName);

You should see no output for this:

SQLite Index

Step 5) To ensure that the index was created you can run the following query, which give you the list of indexes created in the table Students:

PRAGMA index_list(Students);

You should see the index we just created returned:

SQLite Index

Notes:

  • Indexes can be created not only based on columns but expressions too. Something like this:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

The “OrderTotalIndex” will be based on the OrderId column and also on the multiplication of Quantity column value and the Price column value. So any query for “OrderId” and “Quantity*Price” will be efficient as the query will use the index.

  • If you specified a WHERE clause in the CREATE INDEX statement, the index will be a partial index. In this case, there will be entries in the index for only the rows that match the conditions in the WHERE clause. For example, in the following index: 
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;

    ( In above example, the index will be a partial index as there is a WHERE clause specified. In this case, the index will be applied only to those orders that have quantity value larger than 10000. Note that, this index is called a partial index because of the WHERE clause, not the expression used on it. However, you can use the expressions with normal indexes.)

  • You can use CREATE UNIQUE INDEX statement instead of CREATE INDEX to prevent duplicate entries for the columns and thus all the values for the indexed column will be unique.
  • To delete an index, use the DROP INDEX command followed by the index name to delete.

SQLite Trigger

Introduction to SQLite Trigger

Triggers are automatic predefined operations executed when a specific action occurs on a database table. A trigger can be defined to be fired whenever one of the following actions occur on a table:

  • INSERT into a table.
  • DELETE rows from a table.
  • UPDATE one of the table columns.

SQLite supports FOR EACH ROW trigger so that, the predefined operations in the trigger will be executed for all the rows involved in the actions occurred on the table (whether it is insert, delete, or update).

SQLite CREATE TRIGGER

To create a new TRIGGER, you can use the CREATE TRIGGER statement as following:

  • After the CREATE TRIGGER, you should specify a trigger name.
  • After the trigger name, you have to specify, when exactly the trigger name should be executed. You have three options:
  • BEFORE – the trigger will be executed before the INSERT, UPDATE, or the delete statement specified.
  • After – the trigger will be executed after the INSERT, UPDATE, or the delete statement specified.
  • INSTEAD OF – It will replace the action happened which fired the trigger with the statement specified in the TRIGGER. INSTEAD OF trigger is not applicable with tables, only with views.
  • Then, you have to specify the type of action, the trigger will fire when it happens. Either DELETE, INSERT, or UPDATE.
  • You can choose an optional column name so that the trigger won’t fire unless the action happened on that column.
  • Then you have to specify the table name in which the trigger will be created.
  • Inside the body of the trigger, you should specify the statement that should be executed for each row when the trigger is fired.

    • Triggers will be activated (fired) only depending on the type of the statement specified on the create trigger command. For example:

    • The BEFORE INSERT trigger will be activated (fired) before any insert statement.
    • The AFTER UPDATE trigger will be activated (fired) after any update statement, … and so on.

    Inside the trigger, you can refer to the newly inserted values using the “new” keyword. Also, you can refer to the deleted or updated values using the old keyword. As following:

    • Inside INSERT triggers – new keyword can be used.
    • Inside UPDATE triggers – new and old keywords can be used.
    • Inside DELETE triggers – old keyword can be used.

    Example

    In the following, we will create a trigger that will fire before inserting a new student into the “Students” table.

    It will log the newly inserted student into the table “StudentsLog” with an automatic time stamp for the current date time when the insert statement happened. As following:

    Step 1) Navigate to the directory “C:\sqlite” and run sqlite3.exe.

    Step 2) Open the database “TutorialsSampleDB.db” by running the following command:

    .open TutorialsSampleDB.db

    Step 3) create the trigger “InsertIntoStudentTrigger” By running the following command:

    CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

    The function “datetime()” will give you the current date time stamp when the insert statement happened. So that we can log the insert transaction with automatic time stamps added to each transaction.

    The command should run successfully, and you get no output:

    SQLite Trigger

    The trigger “InsertIntoStudentTrigger” will fire each time you insert a new student in the students table. The “new” keyword refers to the values that will be inserted. For example, the “new.StudentId” will be the student id that will be inserted.

    Now, we will test how the trigger behaves when we insert a new student.

    Step 4) Write the following command that will insert a new student in the students table:

    INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

    Step 5) Write the following command which will select all the rows from the “StudentsLog” table:

    SELECT * FROM StudentsLog;

    You should see a new row returned for the new student we just inserted:

    SQLite Trigger

    This row was inserted by the trigger before inserting the new student with id 11.

    In this example we used the trigger “ InsertIntoStudentTrigger ” we created, to log any insert transactions in the table “StudentsLog” automatically. The same way you can log any update, or delete statements.

    Preventing unintended updates with triggers:

    Using BEFORE UPDATE triggers on a table, you can prevent the update statements on a column based on an expression.

    Example

    In the following example, we will prevent any update statement from updating the “studentname” column in the Students table:

    Step 1) Navigate to the directory “C:\sqlite” and run sqlite3.exe.

    Step 2) Open the database “TutorialsSampleDB.db” by running the following command:

    .open TutorialsSampleDB.db

    Step 3) Create a new trigger “preventUpdateStudentName” on the table “Students” by running the following command

    CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

    The “RAISE” command will raise an error with an error message “ You cannot update studentname “, and then it will prevent the update statement from executing.

    Now, we will verify that the trigger works well, and it prevents any update for the studentname column.

    Step 4) Run the following update command, which will update the student name “Jack” to be “Jack1“.

    UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

    You should get the error message we specified on the trigger, saying that “You cannot update studentname” as following:

    SQLite Trigger

    Step 5) Run the following command, which will select the list of student’s names from the students table.

    SELECT StudentName FROM Students;

    You should see that the student name “Jack” still the same and it doesn’t change:

    SQLite Trigger

    Summary

    Views, Indexes, and Triggers are very powerful tools for administrating an SQLite database. You can track the data modification operations when they happen on a table. You can also optimize the database data retrieval operation by creating indexes.

    You Might Like: