SQLite Triggers
Introduction on Triggers
A trigger is an event-driven action that is run automatically when a specified change operation ( INSERT, UPDATE, and DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.
Table of contents
Benefits of using triggers in business
Sample database, table, table structure, table records
SQLite Trigger : Example AFTER INSERT
SQLite Trigger : Example BEFORE INSERT
SQLite Trigger : Example AFTER UPDATE
SQLite Trigger : Example BEFORE UPDATE
SQLite Trigger : Example AFTER DELETE
SQLite Trigger : Example BEFORE DELETE
SQLite Trigger : Example INSERT using INSTEAD OF
SQLite Trigger : Example UPDATE using INSTEAD OF
SQLite Trigger : Example DELETE using INSTEAD OF
Uses for triggers:
- Enforce business rules
- Validate input data
- Generate a unique value for a newly-inserted row in a different file.
- Write to other files for audit trail purposes
- Query from other files for cross-referencing purposes
- Access system functions
- Replicate data to different files to achieve data consistency
Benefits of using triggers in business:
- Faster application development. Because the database stores triggers, you do not have to code the trigger actions into each database application.
- Global enforcement of business rules. Define a trigger once and then reuse it for any application that uses the database.
- Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
- Improve performance in client/server environment. All rules run on the server before the result returns.
Implementation of SQL triggers is based on the SQL standard. It supports constructs that are common to most programming languages. It supports the declaration of local variables, statements to control the flow of the procedure, assignment of expression results to variables, and error handling.
SQLite: Create trigger
A trigger is a named database object that is associated with a table, and it activates when a particular event (e.g. an insert, update or delete) occurs for the table/views. The statement CREATE TRIGGER creates a new trigger in SQLite. The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations that are automatically performed when a specified database event occurs.
Here is the syntax :
Syntax:
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [BEFORE | AFTER] database-event ON [database-name .]table-name trigger-action trigger-action is further defined as: [FOR EACH ROW | FOR EACH STATEMENT] [WHEN expression] BEGIN trigger-step; [trigger-step;] * END
Parameters:
| Name | Description |
|---|---|
| trigger-name | The name of the trigger. A trigger must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified — the trigger inherits the schema of its table. |
| BEFORE AFTER INSTEAD OF |
Determines whether the function is called before, after, or instead of the event. A constraint trigger can only be specified as AFTER. |
| database-event | One of the INSERT, UPDATE, DELETE that will fire the trigger. |
| table-name | The name of the table or view the trigger is for. |
| FOR EACH ROW FOR EACH STATEMENT |
Specifies whether the trigger procedure should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default. |
| expression | A Boolean expression that determines whether the trigger function will actually be executed. |
| trigger-step | Action for the trigger, it is the sql statement. |
There is two SQLite extension to triggers 'OLD' and 'NEW'. OLD and NEW are not case sensitive.
- Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger
- In an INSERT trigger, only NEW.col_name can be used.
- In a UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.
- In a DELETE trigger, only OLD.col_name can be used; there is no new row.
Sample database, table, table structure, table records for various examples
emp_details
SQLite Trigger: Example AFTER INSERT
In the following example, we have two tables : emp_details and emp_log. To insert some information into emp_logs table (which have three fields emp_id and salary and edttime) every time, when an INSERT happen into emp_details table we have used the following trigger :
Here is the trigger ins_same_rec:
CREATE TRIGGER aft_insert AFTER INSERT ON emp_details
BEGIN
INSERT INTO emp_log(emp_id,salary,edittime)
VALUES(NEW.employee_id,NEW.salary,current_date);
END;
Records of the table (on some columns): emp_details
sqlite> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; employee_id first_name last_name job_id salary commission_pct ----------- ---------- ---------- ---------- ---------- -------------- 100 Steven King AD_PRES 24000 101 Neena Kochhar AD_VP 17000 102 Lex De Haan AD_VP 17000 103 Alexander Hunold IT_PROG 9000 104 Bruce Ernst IT_PROG 6000 105 David Austin IT_PROG 4800 106 Valli Pataballa IT_PROG 4800 107 Diana Lorentz IT_PROG 4200 108 Nancy Greenberg FI_MGR 12000 109 Daniel Faviet FI_ACCOUNT 9000 110 John Chen FI_ACCOUNT 8200 111 Ismael Sciarra FI_ACCOUNT 7700 112 Jose Manue Urman FI_ACCOUNT 7800
Records of the table (all columns): emp_log
sqlite> SELECT * FROM emp_log; emp_id salary edittime ---------- ---------- ---------- 100 24000 2011-01-15 101 17000 2010-01-12 102 17000 2010-09-22 103 9000 2011-06-21 104 6000 2012-07-05 105 4800 2011-06-02
Now insert one record in emp_details table see the records both in emp_details and emp_log tables :
INSERT INTO emp_details
VALUES(236, 'RABI', 'CHANDRA', 'RABI','590.423.45700', '2013-01-12', 'AD_VP', 15000, .5,NULL,NULL);
sqlite> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; employee_id first_name last_name job_id salary commission_pct ----------- ---------- ---------- ---------- ---------- -------------- 100 Steven King AD_PRES 24000 101 Neena Kochhar AD_VP 17000 102 Lex De Haan AD_VP 17000 103 Alexander Hunold IT_PROG 9000 104 Bruce Ernst IT_PROG 6000 105 David Austin IT_PROG 4800 106 Valli Pataballa IT_PROG 4800 107 Diana Lorentz IT_PROG 4200 108 Nancy Greenberg FI_MGR 12000 109 Daniel Faviet FI_ACCOUNT 9000 110 John Chen FI_ACCOUNT 8200 111 Ismael Sciarra FI_ACCOUNT 7700 112 Jose Manue Urman FI_ACCOUNT 7800 236 RABI CHANDRA AD_VP 15000 0.5
sqlite> SELECT * FROM emp_log; emp_id salary edittime ---------- ---------- ---------- 100 24000 2011-01-15 101 17000 2010-01-12 102 17000 2010-09-22 103 9000 2011-06-21 104 6000 2012-07-05 105 4800 2011-06-02 236 15000 2014-10-13
itemscope itemtype="http://schema.org/WebPageElement/Heading">SQLite Trigger : Example BEFORE INSERT
In the following example, before inserting a new record in emp_details table, a trigger check the column value of FIRST_NAME, LAST_NAME, JOB_ID and
- If there are any space(s) before or after the FIRST_NAME, LAST_NAME, LTRIM() function will remove those.
- The value of the JOB_ID will be converted to upper cases by UPPER() function.
CREATE TRIGGER befo_insert BEFORE INSERT ON emp_details
BEGIN
SELECT CASE
WHEN ((SELECT emp_details . employee_id FROM emp_details WHERE emp_details.employee_id = NEW.employee_id ) ISNULL)
THEN RAISE(ABORT, 'This is an User Define Error Message - This employee_id does not exist.')
END;
END;
Now insert a row into emp_details table (check the employee_id column, whether it is exists or not.) :
INSERT INTO emp_details(employee_id,first_name,last_name)values(250,'Jeson','Flap');
Now, here is the output.
sqlite> INSERT INTO emp_details(employee_id,first_name,last_name)values(250,'Jeson','Flap'); Error: This is an User Define Error Message - This employee_id does not exist.
SQLite Trigger: Example AFTER UPDATE
We have two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS. stu_log table has two columns user_id and description.
sqlite> SELECT * FROM student_mast; student_id name st_class ---------- ------------------------------ ---------- 1 Steven King 7 2 Neena Kochhar 8 3 Lex De Haan 9 4 Alexander Hunold 10
Let we promote all the students in next class i.e. 7 will be 8, 8 will be 9 and so on. After updating a single row in student_mast table a new row will be inserted in stu_log table where we will store the current user id and a small description regarding the current update. Here is the trigger code :
Here is the trigger for that event-
CREATE TRIGGER aft_update AFTER UPDATE ON student_mast
BEGIN
INSERT into stu_log (description) values('Update Student Record '||
OLD.NAME || ' Previous Class : '||OLD.ST_CLASS ||' Present Class '||
NEW.st_class);
END;
Now update the student_mast table:
UPDATE student_mast SET st_class = st_class + 1;
The trigger shows you the updated records in 'stu_log'. Here is the latest position of student_mast and stu_log tables :
sqlite> SELECT * FROM student_mast; student_id name st_class ---------- -------------------- ---------- 1 Steven King 8 2 Neena Kochhar 9 3 Lex De Haan 10 4 Alexander Hunold 11
sqlite> SELECT description FROM stu_log; description -------------------------------------------------------------------------------------- Update Student Record Steven King Previous Class : 7 Present Class 8 Update Student Record Neena Kochhar Previous Class : 8 Present Class 9 Update Student Record Lex De Haan Previous Class : 9 Present Class 10 Update Student Record Alexander Hunold Previous Class : 10 Present Class 11 sqlite> SELECT * FROM student_mast;
SQLite Trigger: Example BEFORE UPDATE
We have two tables student_mast and student_marks. Here are the sample tables below. The student_id column of student_mast table is the primary key and in student_marks table, it is a foreign key, the reference to student_id column of student_mast table.
Table - student_mast; student_id name st_class ---------- ------------------------------ ---------- 1 Steven King 7 2 Neena Kochhar 8 3 Lex De Haan 9 4 Alexander Hunold 10 Table - student_marks student_id name sub1 sub2 ---------- -------------------- ---------- --------- 1 Steven King 2 Neena Kochhar 3 Lex De Haan 4 Alexander Hunold
Here is the trigger
CREATE TRIGGER befo_update BEFORE UPDATE ON student_mast
BEGIN
SELECT CASE
WHEN ((SELECT student_id FROM student_marks WHERE student_id = NEW.student_id ) ISNULL)
THEN RAISE(ABORT, 'This is a User Define Error Message - This ID can not be updated.')
END;
END;
Now we are going to update the primary key column of student_mast table and look the result below.
sqlite> UPDATE student_mast SET student_id=10 WHERE st_class=9; Error: This is an User Define Error Message - This ID can not be updated.
SQLite Trigger: Example AFTER DELETE
In our 'AFTER UPDATE' example, we had two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS and stu_log table has two columns user_id and description. We want to store some information in stu_log table after a delete operation happened on student_mast table. Here is the trigger :
Here is the trigger
CREATE TRIGGER aft_delete AFTER DELETE ON student_mast
BEGIN
INSERT into stu_log (description) VALUES ('Update Student Record '||
OLD.NAME||' Class : '||OLD.ST_CLASS||' -> Deleted on '||
date('NOW'));
END;
Let delete a student from student_mast
sqlite> DELETE FROM STUDENT_MAST WHERE STUDENT_ID = 1;
Here is the latest position of student_mast, stu_log tables :
sqlite> SELECT * FROM STUDENT_MAST; student_id name st_class ---------- -------------------- --------- 2 Neena Kochhar 9 3 Lex De Haan 10 4 Alexander Hunold 11 sqlite> SELECT description FROM stu_log; description ------------------------------------------------------------------------------------ Update Student Record Steven King Previous Class : 7 Present Class 8 Update Student Record Neena Kochhar Previous Class : 8 Present Class 9 Update Student Record Lex De Haan Previous Class : 9 Present Class 10 Update Student Record Alexander Hunold Previous Class : 10 Present Class 11 Update Student Record Steven King Class : 8 -> Deleted on 2014-10-13
SQLite Trigger: Example BEFORE DELETE
We have two tables student_mast and student_marks. Here are the sample tables below. The student_id column of student_mast table is the primary key and in student_marks table, it is a foreign key, a reference to student_id column of student_mast table.
Table - student_mast; student_id name st_class ---------- ------------------------------ ---------- 1 Steven King 7 2 Neena Kochhar 8 3 Lex De Haan 9 4 Alexander Hunold 10 Table - student_marks student_id name sub1 sub2 ---------- -------------------- ---------- --------- 1 Steven King 2 Neena Kochhar 3 Lex De Haan 4 Alexander Hunold
Here is the trigger
CREATE TRIGGER befo_delete BEFORE DELETE ON student_marks
BEGIN
SELECT CASE
WHEN (SELECT COUNT(student_id) FROM student_mast WHERE student_id=OLD.student_id) > 0
THEN RAISE(ABORT,
'Foreign Key Violation: student_masts rows reference row to be deleted.')
END;
END;
Let try to delete a student from student_marks and see the result.
sqlite> DELETE FROM student_marks WHERE name='Steven King'; Error: Foreign Key Violation: student_masts rows reference row to be deleted.
SQLite trigger using INSTEAD OF
Here is the sample table emp_details.
employee_id first_name last_name email ----------- ---------- ---------- ---------- 100 Steven King SKING 101 Neena Kochhar NKOCHHAR 102 Lex De Haan LDEHAAN 103 Alexander Hunold AHUNOLD 104 Bruce Ernst BERNST 105 David Austin DAUSTIN 106 Valli Pataballa VPATABAL 107 Diana Lorentz DLORENTZ 108 Nancy Greenberg NGREENBE 109 Daniel Faviet DFAVIET 110 John Chen JCHEN 111 Ismael Sciarra ISCIARRA 112 Jose Manue Urman JMURMAN 236 RABI CHANDRA RABI
Now create a view name emp_details_view.
CREATE VIEW emp_details_view
AS
SELECT employee_id,first_name,last_name,email
FROM emp_details
ORDER BY first_name,last_name;
Now see the just created view .
sqlite> SELECT name FROM sqlite_master WHERE type='view'; name emp_details_view
Here is the view.
employee_id first_name last_name email ----------- ---------- ---------- ---------- 103 Alexander Hunold AHUNOLD 104 Bruce Ernst BERNST 109 Daniel Faviet DFAVIET 105 David Austin DAUSTIN 107 Diana Lorentz DLORENTZ 111 Ismael Sciarra ISCIARRA 110 John Chen JCHEN 112 Jose Manue Urman JMURMAN 102 Lex De Haan LDEHAAN 108 Nancy Greenberg NGREENBE 101 Neena Kochhar NKOCHHAR 236 RABI CHANDRA RABI 100 Steven King SKING 106 Valli Pataballa VPATABAL
INSERT TRIGGER using INSTEAD OF
Here is the example
CREATE TRIGGER view_ins_trig
INSTEAD OF INSERT
ON emp_details_view
BEGIN
INSERT INTO emp_details(employee_id,first_name,last_name,email)
SELECT new.employee_id, new.first_name,new.last_name,new.email;
END;
Now insert the rows in the emp_details_view and the triggers will propagate those changes to the underlying table..
INSERT INTO emp_details_view (employee_id,first_name,last_name,email)
VALUES (250,'Andrai', 'Marku','and_mar');
Now look the view and the base table
base table - emp_details employee_id first_name last_name email -------------------- ---------- ---------- ---------- 100 Steven King SKING 101 Neena Kochhar NKOCHHAR 102 Lex De Haan LDEHAAN 103 Alexander Hunold AHUNOLD 104 Bruce Ernst BERNST 105 David Austin DAUSTIN 106 Valli Pataballa VPATABAL 107 Diana Lorentz DLORENTZ 108 Nancy Greenberg NGREENBE 109 Daniel Faviet DFAVIET 110 John Chen JCHEN 111 Ismael Sciarra ISCIARRA 112 Jose Manue Urman JMURMAN 236 RABI CHANDRA RABI 250 Andrai Marku and_mar view - emp_details_view employee_id first_name last_name email -------------------- ---------- ---------- ---------- 103 Alexander Hunold AHUNOLD 250 Andrai Marku and_mar 104 Bruce Ernst BERNST 109 Daniel Faviet DFAVIET 105 David Austin DAUSTIN 107 Diana Lorentz DLORENTZ 111 Ismael Sciarra ISCIARRA 110 John Chen JCHEN 112 Jose Manue Urman JMURMAN 102 Lex De Haan LDEHAAN 108 Nancy Greenberg NGREENBE 101 Neena Kochhar NKOCHHAR 236 RABI CHANDRA RABI 100 Steven King SKING 106 Valli Pataballa VPATABAL
UPDATE TRIGGER using INSTEAD OF
Here is the example
CREATE TRIGGER view_update_trig
INSTEAD OF UPDATE
ON emp_details_view
BEGIN
UPDATE emp_details
SET employee_id = new.employee_id, first_name = new.first_name, last_name = new.last_name
WHERE employee_id = old.employee_id;
END;
Now update the rows in the emp_details_view.
UPDATE emp_details_view SET first_name = 'Andrai'
WHERE first_name = 'RABI' AND last_name= 'CHANDRA';
Now look the view and the base table
base table - emp_details employee_id first_name last_name email -------------------- ---------- ---------- ---------- 100 Steven King SKING 101 Neena Kochhar NKOCHHAR 102 Lex De Haan LDEHAAN 103 Alexander Hunold AHUNOLD 104 Bruce Ernst BERNST 105 David Austin DAUSTIN 106 Valli Pataballa VPATABAL 107 Diana Lorentz DLORENTZ 108 Nancy Greenberg NGREENBE 109 Daniel Faviet DFAVIET 110 John Chen JCHEN 111 Ismael Sciarra ISCIARRA 112 Jose Manue Urman JMURMAN 236 Andrai CHANDRA RABI view - emp_details_view employee_id first_name last_name email -------------------- ---------- ---------- ---------- 103 Alexander Hunold AHUNOLD 236 Andrai CHANDRA RABI 104 Bruce Ernst BERNST 109 Daniel Faviet DFAVIET 105 David Austin DAUSTIN 107 Diana Lorentz DLORENTZ 111 Ismael Sciarra ISCIARRA 110 John Chen JCHEN 112 Jose Manue Urman JMURMAN 102 Lex De Haan LDEHAAN 108 Nancy Greenberg NGREENBE 101 Neena Kochhar NKOCHHAR 100 Steven King SKING 106 Valli Pataballa VPATABAL
DELETE TRIGGER using INSTEAD OF
Here is the example
CREATE TRIGGER view_delete_trig
INSTEAD OF delete
ON emp_details_view
BEGIN
DELETE FROM emp_details
WHERE employee_id = old.employee_id;
END;
Now delete the row from the emp_details_view which employee_id is 106, and look the result.
DELETE FROM emp_details_view
WHERE employee_id = 106;
Now look the view and the base table
base table - emp_details employee_id first_name last_name email -------------------- ---------- ---------- ---------- 100 Steven King SKING 101 Neena Kochhar NKOCHHAR 102 Lex De Haan LDEHAAN 103 Alexander Hunold AHUNOLD 104 Bruce Ernst BERNST 105 David Austin DAUSTIN 107 Diana Lorentz DLORENTZ 108 Nancy Greenberg NGREENBE 109 Daniel Faviet DFAVIET 110 John Chen JCHEN 111 Ismael Sciarra ISCIARRA 112 Jose Manue Urman JMURMAN 236 Andrai CHANDRA RABI view - emp_details_view employee_id first_name last_name email -------------------- ---------- ---------- ---------- 103 Alexander Hunold AHUNOLD 236 Andrai CHANDRA RABI 104 Bruce Ernst BERNST 109 Daniel Faviet DFAVIET 105 David Austin DAUSTIN 107 Diana Lorentz DLORENTZ 111 Ismael Sciarra ISCIARRA 110 John Chen JCHEN 112 Jose Manue Urman JMURMAN 102 Lex De Haan LDEHAAN 108 Nancy Greenberg NGREENBE 101 Neena Kochhar NKOCHHAR 100 Steven King SKING
The row has been deleted which contain the employee_id 106.
DROP an SQLite trigger
To delete or destroy a trigger, use a DROP TRIGGER statement. To execute this command, the current user must be the owner of the table for which the trigger is defined.
Syntax:
DROP TRIGGER trigger_name
Example:
If you delete or drop the just created trigger delete_stu the following statement can be used:
DROP TRIGGER delete_stu on student_mast;
The trigger delete_stu will be deleted.
Previous:
Subqueries
Next:
SQLite Exercises Introduction
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics