w3resource

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

Uses for triggers

Benefits of using triggers in business

Create SQLite triggers

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

Drop/Delete a SQLite trigger

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.

Here is the trigger befo_insert:
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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sqlite/sqlite-triggers.php