articles

Home / DeveloperSection / Articles / SQLite- TRIGGERS EXAMPLE

SQLite- TRIGGERS EXAMPLE

Tarun Kumar11006 28-Sep-2015

Previously, we learn about the SQLite : SQLite- Triggers

In our previous blog we learn about TRIGGER in SQLite and saw some points. Now in this article we will learn how to create TRIGGER and how to use TRIGGERS in SQLite database.

As we know that triggers are the database callback functions, that automatically performed or invoked whenever any database event occurs. 

Ok, here is the basic syntax of creating a trigger:


SYNTAX:


CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name

BEGIN

……… Trigger logic goes here ………

END;

Above, event name means it can be INSERT, DELETE OR UPDATE command operated on the mentioned table name. You can also specify optionally FOR EACH ROW after the table name.  

EXAMPLE : lets take an example where we will create an audit trial for every record that are being inserted in DEPARTMENT table, I am already created an EMPLOYEE table with some columns like ID, NAME, AGE, ADDRESS, CONTACT. 

Now, we will create a new table called AUDIT, this table we will use to store log messages, to create table AUDIT here is the code:

CREATE TABLE AUDIT(

    EMP_ID INT NOT NULL,

    ENTRY_DATE TEXT NOT NULL,

    TRIGGER_NAME TEXT NOT NULL

);

 

Here, in AUDIT table ID is the record ID, and EMP_ID is the ID which will come from


EMPLOYEE table and DATE will keep timestamp when the record will be created


in EMPLOYEE table and also here we are saving the trigger name in column


TRIGGER_NAME.

 

Now, let’s create an INSERT trigger on EMPLOYEE table, code is as follows:


CREATE TRIGGER insert_log AFTER INSERT

ON EMPLOYEE

BEGIN

   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE, TRIGGER_NAME) VALUES (new.ID, datetime('now'), 'INSERT');

END;

 

UPDATE trigger on EMPLOYEE table, code is as follows:


CREATE TRIGGER update_log AFTER UPDATE

ON EMPLOYEE

BEGIN

   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE, TRIGGER_NAME) VALUES (new.ID, datetime('now'), 'UPDATE');

END;

 

DELETE trigger on EMPLOYEE table, code is as follows:


CREATE TRIGGER delete_log AFTER DELETE

ON EMPLOYEE

BEGIN

   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE, TRIGGER_NAME) VALUES (new.ID, datetime('now'), 'DELETE');

END;

 


Now, when we insert record into EMPLOYEE table which should result in creating


an insert log record in AUDIT table.  So let’s create one record in EMPLOYEE table


as follows: 


INSERT INTO EMPLOYEE (ID, NAME, AGE, ADDRESS, CONTACT)

VALUES (1, 'AJAY', 32, 'CALCUTTA', 9811233234);

 

Now one record in EMPLOYEE table has been created which is as follows:


ID

NAME

AGE

ADDRESS

CONTACT

1

AJAY

32

CALCUTTA

9811233234

 

Same time, one record will be created in AUDIT table. This record is the result of a trigger, which we have created on INSERT operation on EMPLOYEE table. To see the trigger is working properly or not check the AUDIT table log is inserted or not after INSERT operation on EMPLOYEE table. Here is the following code:


SELECT * FROM AUDIT;

EMP_ID

ENTRY_DATE

TRIGGER_NAME

1

2015-09-28 11:14:58

INSERT

 

Similarly we execute UPDATE or DELETE operations on EMPLOYEE table, log record also be updated, here is the code:


UPDATE EMPLOYEE SET CONTACT='1111111111' WHERE ID=1;

 

Now, see the records of AUDIT table and update log also be inserted:

 

EMP_ID

ENTRY_DATE

TRIGGER_NAME

1

2015-09-28 11:14:58

INSERT

1

2015-09-28 11:22:57

UPDATE

[Delete log also works as like insert and update.] 


Listing TRIGGERS


We can see all the triggers from sqlite_master table, using following code: 


SELECT name FROM sqlite_master

WHERE type = 'trigger';

 

When we execute the above statement a list down with one entry as follows:


NAME

insert_log

update_log

delete_log

 

We can also see all the triggers on a particular table, then use AND clause with


table name as follows:


SELECT name FROM sqlite_master

WHERE type = 'trigger' AND tbl_name = 'EMPLOYEE';

 

When we execute the above statement it will display the list of triggers that are


used in EMPLOYEE table.


NAME

insert_log

update_log

delete_log

 

Dropping TRIGGERS


We can also delete the triggers using DROP command which is as follows: 


DROP TRIGGER 'insert_log'; 

Updated 11-Jul-2020

Leave Comment

Comments

Liked By