SQLite Triggers

SQLite Trigger is an event-driven action or database callback function which is invoked automatically when an INSERT, UPDATE, and DELETE statement is performed on a specified table.

The main tasks of triggers are like enforcing business rules, validating input data, and keeping an audit trail.

Usage of Triggers:

• Triggers are used for enforcing business rules.
• Validating input data.
• Generating 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.
• Used to access system functions.
• Replicate data to different files to achieve data consistency.

Advantages of using triggers:

• Triggers make the application development faster. Because the database stores triggers, you do not have to code the trigger actions into each database application.
• Define a trigger once and you can reuse it for many applications that use the database.
• Maintenance is easy. If the business policy changes, you have to change only the corresponding trigger program instead of each application program.

How to create trigger

The CREATE TRIGGER statement is used to create a new trigger in SQLite. This statement is also used to add triggers to the database schema.

Syntax:

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

Here, trigger_name is the name of trigger which you want to create.

event_name could be INSERT, DELETE, and UPDATE database operation.

table_name is the table on which you do the operation.