Therefore, we have decided to cover the topic of SQLite Triggers in Ubuntu 20.04 while using the SQLite C-library of the SQL database. Let’s start with the opening of a terminal of Ubuntu 20.04. It must be started with the Ctrl+Alt+T as we have to do most of our work within it. Within the shell terminal, you need to try out the update and upgrade instructions combined with the apt package to make your system up to date, bug-free, and upgrade its packages.
You might encounter the following output displayed below at the end of the update and upgrade process. Let’s move forward toward the SQLite database.
Let’s start the SQLite C-package of a database within our shell of Ubuntu 20.04 with the help of the keyword “sqlite3”. The command shell for SQLite will be generated on your screen as shown below.
The triggers are always created as a response to some queries in a database and the queries are applied to the tables. We have no table in our database right now. So, we need to create new tables. To apply triggers, we need at least 2 tables. Therefore, we have been creating a new table STUDENT with 5 columns in it (i.e., SID, NAME, RNO, MARKS, and STATUS.) The NAME and STATUS column is of TEXT type while the rests of the columns are of INT or REAL type.
After this, we have been creating a new table DATA with three columns: ID, STATUS, and RECORD. This table will be utilized at the time of invoking triggers. We have tried the SELECT instruction following the “*” character to fetch the records of both newly created tables (i.e., STUDENT and DATA.) The query for both tables is showing that the tables are empty.
MARKS REAL NOT NULL, STATUS TEXT NOT NULL);
CREATE TABLE DATA(ID INT NOT NULL, STATUS TEXT NOT NULL, RECORD TEXT);
SELECT * FROM STUDENT;
SELECT * FROM DATA;
Let’s insert some records within the STUDENT table and see if inserting the records in this table may affect the other table “DATA” or not. Thus, we have been trying the INSERT INTO instruction to add a total of 5 records within all 5 columns of a table (i.e., ID, NAME, RNO, MARKS, and STATUS.) After inserting the 5 records successfully, we have been trying the SELECT instruction followed by the asterisk “*” character to display all the records of a table “STUDENT”. The execution of this command has been presenting the 5 records at our shell screen of SQLite database.
After that, we have to check the DATA table if it’s affected or not. So, we have tried the SELECT instruction with the “*” character for the DATA table as well. We have found that the table is still empty and the insertion of data within the STUDENT table doesn’t affect table “DATA” yet. Let’s move forward toward the creation of a trigger.
(2, "Ema", 355, 98, "PASS"), (3, "Julia", 349, 22, "FAIL"), (4, "John", 335, 47, "PASS"),
(5, "Paul", 250, 25, "FAIL");
SELECT * FROM STUDENT;
SELECT * FROM DATA;
So, we will be creating a new trigger to let the insertion of records in one table affect the other. Thus, we will be creating an INSERT trigger on the STUDENT table, so that using the CREATE TRIGGER instruction on the SQLite shell shall be followed by the name of a trigger to be created (i.e., “I log”).
After the name of a trigger, you have to use the keyword “AFTER” along with the query type for which this trigger will be executed (i.e., INSERT, DELETE, UPDATE, etc.) So, we have been using the “AFTER INSERT” keyword to make this trigger executed after the execution of the insertion command followed by the “ON” clause along with the name of a table on which this trigger will be applied (i.e., on the STUDENT table.) The trigger condition will begin with the “BEGIN” and stops with the keyword “END”.
Within both these keywords, we will write our trigger statement that will be invoked after the execution of a specific INSERT query. The trigger contains an INSERT query to add the entry within the DATA table upon the execution of insertion instruction for the STUDENT table. The column “RID” and “STATUS” of the DATA table will be using the values from the STUDENT table as per the keyword “new” followed by the column names of a STUDENT table (i.e. new.ID, new.STATUS.) The column RECORD of the DATA table will be assigned with the “Record Inserted” value as shown.
BEGIN
INSERT INTO DATA(RID, STATUS, RECORD) VALUES (NEW.ID, NEW.STATUS, "Record Inserted");
END;
The trigger “i_log” has been created successfully in the SQLite database and is ready for use. We will be inserting some records within the STUDENT table to make the trigger get executed automatically upon the usage of the INSERT INTO instruction. After the execution of the insertion command, we have tried the SELECT query to see the records of the STUDENT table. Both new records have been added to this table. After this, we have tried to fetch the records of a DATA table using the SELECT instruction. This time, the DATA table also shows 2 entries that are being added by the automatic trigger “i_log” on the Insert query for the STUDENT table.
(7, "Taylor", 441, 64, "PASS");
SELECT * FROM STUDENT;
SELECT * FROM DATA;
Conclusion:
This was on the use of triggers to perform a specific query on the execution of another query in the SQLite database. We have performed the INSERT instruction trigger in this article. All the steps are clearly described in detail.
