SQLite Triggers

  Total Views : 736
  Comments : 0
  Keywords : create triggers in sqlite with example, Use of triggers in sqlite with examples, sqlite raise triggers automatically for insert / update / delete operations

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 update, after insert, after update statements with example

SQLite Triggers

In SQLite trigger is a database object and it will raise automatically whenever an insert, update and delete operations performed on particular table or view in database.

 

Generally, in SQLite triggers are specific to 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 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 particular table so the triggers are dropped automatically when the associated table dropped.

Benefits of Trigger in SQLite

  • It will help us to prevent invalid transactions by imposing business rules
  • Maintain data integrity
  • We can enable event logging for the changes in table data.

 

In SQLite we can create triggers by using CREATE TRIGGER statement.

Syntax of Creating SQLite Trigger

Following is the syntax of creating 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 above syntax we defined multiple parameters to create trigger we will learn all the parameters in detailed.

 

IF NOT EXISTS – It’s Optional and it will prevent throwing error in case if we try to create 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 trigger can be invoked such as INSERTUPDATE 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 following statements.

 

CREATE TABLE Product

(pid INTEGER PRIMARY KEY,

pname TEXT NOT NULL,

amount REAL,

quantity INTEGER);

Now we will create trigger (trg_validate_products_before_insert) on “Product” table to raise before insert of any data using 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 NEW reference to access quantity and amount columns and added validation to check quantity and amount values while inserting new row. 

 

Now we will try to insert invalid amount value in Product table using following statements.

 

INSERT INTO Product(pid,pname,amount,quantityVALUES(1,'Marbles',-5,20);

Error: Invalid Amount

The above statement return 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 Product table using following statements.

 

INSERT INTO Product(pid,pname,amount,quantityVALUES(1,'Marbles',100,-3);

Error: Invalid Quantity

The above statement also return Invalid Quantity because we tried to insert value which is less than zero. 

 

Now we will try to insert valid data in Product table using following statements.

 

INSERT INTO Product(pid,pname,amount,quantityVALUES(1,'Marbles',100,3);

 

sqliteSELECT 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. 

SQLite AFTER UPDATE Trigger

Now we will see how to raise trigger 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 trigger immediate next to the completion of query execution first we will create 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 Product_logs table whenever there is an update in Product table amount or pname columns. By this, we can keep track of product prices in 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 product_log table using following statement.

 

sqliteSELECT 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 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.

 

sqliteSELECT 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 trigger after updating table columns.

SQLite AFTER DELETE Trigger

Now we will see how to raise trigger after deleting the records in table using AFTER DELETE statement. 

 

We will create trigger to log data in product_log table whenever we delete any records from 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.

 

sqliteDELETE FROM product WHERE pid=1;

Once we delete now check records of product_log table using following statement.

 

sqliteSELECT 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.

SQLite AFTER INSERT Trigger

Now we will see how to raise triggers after insertion of data in table using AFTER INSERT statement with example.

 

Create trigger using following statements to raise trigger and log the data in product_log table whenever we insert new records in 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,amountVALUES(2,'ERASER', 200,5);

Now we will check the records of product_log table using following statement.

 

sqliteSELECT 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.

SQLite BEFORE UPDATE Trigger

Now we will see how to raise trigger before updating the table records using BEFORE UPDATE statement with example.

 

Let’s create trigger with BEFORE UPDATE statement using following statements on 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 NEW reference to access quantity and amount columns of row that is going to be updated in 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 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 the cases when we try to update invalid values in amount and quantity columns we got validation errors because of our BEFORE UPDATE trigger.

SQLite BEFORE DELETE 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.

 

sqliteSELECT 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

 

sqliteSELECT 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 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_idFROM 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 Publisher table based on pub_id then first it will check that same pub_id available in book table or not. In case if it available then it will raise exception like “Child table having data” otherwise it will delete the data.

 

Let’s try to delete record from 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.

SQLite Delete / Remove Trigger

In SQLite by using DROP TRIGGER command we can easily remove or delete triggers which are associated with tables.

Syntax to Delete Trigger

Following is the syntax to delete or drop or remove triggers in SQLite.

 

DROP TRIGGER [IF EXISTS] trigger_name

In above syntax IF EXISTS will help us to prevent throwing errors in case if we are trying to delete triggers which are not exists in database.

Example to Delete Trigger

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. 

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User