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:

1. CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name   
2. ON table_name  
3. BEGIN  
4.  -- Trigger logic goes here....  
5. 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.