SQLite- TRIGGERS EXAMPLE

Previously, we learn about the SQLite: SQLite- Triggers

In our previous blog welearn about TRIGGER in SQLite and saw somepoints. Now in this article we will learn how to create TRIGGER and how touse TRIGGERS in SQLite database.

As we know that triggers are thedatabase callback functions, that automatically performed or invoked wheneverany database event occurs.

Ok, here is the basic syntax of creating atrigger:

SYNTAX:

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name
BEGIN
……… Trigger logic goes here ………
END;

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

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

Now, we will create a new table calledAUDIT, this table we will use to store log messages, to create table AUDIT hereis 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 tablewhich should result in creating

an insert log record in AUDIT table. Solet’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 willbe created in AUDIT table. This record is the result of a trigger, which wehave created on INSERT operation on EMPLOYEE table. To see the trigger isworking properly or not check the AUDIT table log is inserted or not after INSERToperation 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 UPDATEor DELETE operations on EMPLOYEE table, log record also be updated, here is thecode:

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

Now, see the records of AUDIT tableand 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 insertand update.]

Listing TRIGGERS

We can see all the triggers fromsqlite_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.