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';
0 Comments