Here we will learn what are triggers in SQLite, how to create and use triggers in SQLite, delete triggers in SQLite and how to use triggers in SQLite with before insert, before the update, after insert, after update statements with example
In SQLite trigger is a database object and it will raise automatically whenever an insert, update and delete operations performed on a particular table or view in the database.
Generally, in SQLite triggers are specific to the particular table so those triggers will raise automatically whenever we perform any operations like insert, update or delete on that particular table.
The triggers in SQLite will help us to maintain the data integrity on the database and these triggers will raise automatically to perform defined rules to prevent invalid transactions BEFORE or AFTER INSERT, UPDATE, or DELETE operations.
In SQLite triggers are specific to a particular table so the triggers are dropped automatically when the associated table dropped.
In SQLite, we can create triggers by using the CREATE TRIGGER statement.
Following is the syntax of creating a trigger in SQLite using CREATE TRIGGER.
CREATE TRIGGER [IF NOT EXISTS] trigger_name
[BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]
ON table_name
[FOR EACH ROW | FOR EACH STATEMENT] [WHEN condition]
BEGIN
Trigger_action_body
END;
If you observe the above syntax we defined multiple parameters to create trigger we will learn all the parameters in detail.
IF NOT EXISTS – It’s Optional and it will prevent throwing error in case if we try to create an existing trigger.
Trigger_name – Its name of the trigger which we are going to create.
[BEFORE|AFTER|INSTEAD OF] – It will determine when the trigger action can be performed is it BEFORE or AFTER or INSTEAD OF the event.
[INSERT|UPDATE|DELETE] – It will determine in which action triggers can be invoked such as INSERT, UPDATE or DELETE.
table_name – Its name of the table on which we are going to create trigger.
[FOR EACH ROW | FOR EACH STATEMENT] – Present SQLite will support only FOR EACH ROW so we don’t need to explicitly specify FOR EACH ROW and its Optional.
Trigger_action_body – It contains SQLite statements which will execute whenever the trigger action performed.
Now we will see how to use triggers with examples for that create new tabled called “Product” using the following statements.
CREATE TABLE Product
(pid INTEGER PRIMARY KEY,
pname TEXT NOT NULL,
amount REAL,
quantity INTEGER);
Now we will create a trigger (trg_validate_products_before_insert) on the “Product” table to raise before insert of any data using the following statements.
CREATE TRIGGER trg_validate_products_before_insert BEFORE INSERT
ON Product
BEGIN
SELECT
CASE
WHEN NEW.quantity < 0 THEN
RAISE(ABORT, 'Invalid Quantity')
END;
SELECT
CASE
WHEN NEW.amount<=0 THEN
RAISE(ABORT, 'Invalid Amount')
END;
END;
In above statement, we used a NEW reference to access quantity and amount columns and added validation to check quantity and amount values while inserting a new row.
Now we will try to insert invalid amount value in Product table using following statements.
INSERT INTO Product(pid,pname,amount,quantity) VALUES(1,'Marbles',-5,20);
Error: Invalid Amount
The above statement returns Invalid Amount because we added validation to allow new row insertion only when quantity and amount column values are greater than zero.
Now we will see what will happen when we try to insert invalid quantity value in the Product table using the following statements.
INSERT INTO Product(pid,pname,amount,quantity) VALUES(1,'Marbles',100,-3);
Error: Invalid Quantity
The above statement also returns Invalid Quantity because we tried to insert value which is less than zero.
Now we will try to insert valid data in the Product table using the following statements.
INSERT INTO Product(pid,pname,amount,quantity) VALUES(1,'Marbles',100,3);
sqlite> SELECT * FROM product;
pid pname amount quantity
---------- ---------- ---------- ----------
1 Marbles 100.0 3
The data inserted successfully without having any error. This is how we can use triggers to raise BEFORE or AFTER execution of operations on tables.
Now we will see how to raise triggers after updating the table records using AFTER UPDATE statement with example.
By using AFTER UPDATE we can raise the trigger immediately after completion of query execution.
To see how to raise the trigger immediately after completion of query execution first, we will create a new table called products_log using following statements.
CREATE TABLE Product_log
(pid INTEGER,
operation TEXT,
old_amount REAL,
new_amount REAL,
old_pname TEXT,
new_pname TEXT);
Now, create an AFTER UPDATE trigger to log the data into the Product_logs table whenever there is an update in the Product table amount or pname columns. By this, we can keep track of product prices in the future also.
CREATE TRIGGER trg_product_after_update AFTER UPDATE
on Product
WHEN OLD.amount <> NEW.amount
OR OLD.pname <> NEW.pname
BEGIN
INSERT INTO Product_log
(pid,operation,old_amount,new_amount,old_pname,new_pname)
VALUES(OLD.pid,'UPDATE',
OLD.amount,NEW.amount,OLD.pname,NEW.pname);
END;
In above trigger statement we defined condition in WHEN clause to invoke trigger only when there is a change in pname or amount columns of Product table.
Now we will update amount for pid 1 to 20rs using following statement.
UPDATE product SET amount = 20 WHERE pid = 1;
Now we will check the records of the product_log table using following statement.
sqlite> SELECT * FROM product_log;
pid operation old_amount new_amount old_pname new_pname
---------- ---------- ---------- ---------- ---------- ----------
1 UPDATE 100.0 20.0 Marbles Marbles
Now, we will try to update pname to “pencil” in the Product table where pid is 1.
UPDATE Product SET pname ='Pencil' WHERE pid = 1;
Once we update let’s check the records of product_log table using following statement.
sqlite> SELECT * FROM product_log;
pid operation old_amount new_amount old_pname new_pname
---------- ---------- ---------- ---------- ---------- ----------
1 UPDATE 100.0 20.0 Marbles Marbles
1 UPDATE 20.0 20.0 Marbles Pencil
This is how we can use AFTER UPDATE statement to raise the trigger after updating table columns.
Now we will see how to raise the trigger after deleting the records in a table using AFTER DELETE statement.
We will create a trigger to log data in the product_log table whenever we delete any records from the product table using following statements.
CREATE TRIGGER trg_product_after_delete AFTER DELETE on Product
BEGIN
INSERT into product_log
(pid,operation,old_pname,old_amount)
VALUES(OLD.pid,'DELETE ' || date('now'),OLD.pname,OLD.amount);
END;
Now, let’s delete one record from Product table using following query.
sqlite> DELETE FROM product WHERE pid=1;
Once we delete now check records of product_log table using following statement.
sqlite> SELECT * FROM product_log;
pid operation old_amount new_amount old_pname new_pname
---------- ---------- ---------- ---------- ---------- ----------
1 UPDATE 100.0 20.0 Marbles Marbles
1 UPDATE 20.0 20.0 Marbles Pencil
1 DELETE 20.0 Pencil
This is how we can use AFTER DELETE statement to raise trigger after deleting table data.
Now we will see how to raise triggers after insertion of data in the table using AFTER INSERT statement with example.
Create a trigger using the following statements to raise the trigger and log the data in the product_log table whenever we insert new records in the Product table.
CREATE TRIGGER trg_product_after_insert AFTER INSERT on Product
BEGIN
INSERT into product_log
(pid,operation,old_pname,old_amount)
VALUES(NEW.pid,'INSERT ' || date('now'),NEW.pname,NEW.amount);
END;
Now, let’s insert one record into Product table using following statement.
INSERT INTO product (pid,pname,quantity,amount) VALUES(2,'ERASER', 200,5);
Now we will check the records of product_log table using following statement.
sqlite> SELECT * FROM product_log;
pid operation old_amount new_amount old_pname new_pname
---------- ---------- ---------- ---------- ---------- ----------
1 UPDATE 100.0 20.0 Marbles Marbles
1 UPDATE 20.0 20.0 Marbles Pencil
1 DELETE 20.0 Pencil
2 INSERT 5.0 ERASER
This is how we can use AFTER INSERT statement to raise trigger after insertion of data in tables based on our requirements.
Now we will see how to raise trigger before updating the table records using BEFORE UPDATE statement with example.
Let’s create a trigger with the BEFORE UPDATE statement using the following statements on the Product table.
CREATE TRIGGER trg_validate_products_before_update BEFORE UPDATE
ON Product
BEGIN
SELECT
CASE
WHEN NEW.quantity < 0 THEN
RAISE(ABORT,'Invalid Quantity')
END;
SELECT
CASE
WHEN NEW.amount<=0 THEN
RAISE(ABORT,'Invalid Amount')
END;
END;
If you observe above statements we added validation that amount of product and quantity must be greater than zero and we used a NEW reference to access quantity and amount columns of row that is going to be updated in the Product table.
Now we will try to update invalid amount to Product table using following statements.
UPDATE Product SET Amount = -3 WHERE pid = 2;
Error: Invalid Amount
When we try to update invalid value in the Amount column of Product table it throws error like as shown.
Now we will try to update invalid quantity to Product table using following statement.
UPDATE Product SET quanity = -10 WHERE pid = 2;
Error: Invalid Quantity
In both cases when we try to update invalid values in amount and quantity columns we got validation errors because of our BEFORE UPDATE trigger.
Now we will see how to raise trigger before deleting the table records using BEFORE DELETE statement with example.
Consider we have two tables called publisher and book like as shown following.
sqlite> SELECT * FROM book;
Book_id Book_name Price pub_id
---------- ---------- ---------- ----------
1 RAMAYANA 100 1
2 MAHABHARAT 300 2
3 Akbar birb 150 1
4 SHIVA PURA 200 1
sqlite> SELECT * FROM publisher;
Pub_id Pub_name
---------- ----------
1 JBL
2 Cyberwit
3 Indian Exp
4 Economics
Here the pub_id column in book table is a foreign key references to publisher table.
Now we will create a trigger on Publisher table using BEFORE DELETE statement.
CREATE TRIGGER trg_product_before_delete BEFORE DELETE on Publisher
BEGIN
SELECT CASE
WHEN (SELECT count(pub_id) FROM book WHERE pub_id=OLD.pub_id)>0 THEN
RAISE(ABORT,"Child table having data")
END;
END;
Here if we try to delete data from the Publisher table based on pub_id then first it will check that same pub_id available in a book table or not. In case if it available then it will raise exceptions like “Child table having data” otherwise it will delete the data.
Let’s try to delete record from the publisher table where pub_id is 1 using following statements.
sqlite>DELETE FROM publisher WHERE pub_id=1;
Error: Child table having data
This is how we can use BEFORE DELETE trigger to raise before deleting the records from table based on our requirements.
In SQLite by using the DROP TRIGGER command we can easily remove or delete triggers which are associated with tables.
Following is the syntax to delete or drop or remove triggers in SQLite.
DROP TRIGGER [IF EXISTS] trigger_name
In the above syntax IF EXISTS will help us to prevent throwing errors in case if we are trying to delete triggers that do not exist in database.
Following is the example to delete or drop trigger in SQLite using DROP TRIGGER statement.
DROP TRIGGER trg_product_after_insert;
This is how we can use Triggers in SQLite based on our requirements.