How to use SQLite triggers?

SQLite's Trigger mechanism allows automatic execution of operations when specific database events occur:

1. Trigger Types

   • BEFORE Trigger: Triggers before operation execution
   • AFTER Trigger: Triggers after operation execution
   • INSTEAD OF Trigger: Replaces original operation execution (mainly for views)

2. Trigger Events

   • INSERT: Triggers when data is inserted
   • UPDATE: Triggers when data is updated
   • DELETE: Triggers when data is deleted

3. Creating Triggers

   sql
   -- BEFORE INSERT trigger
   CREATE TRIGGER update_timestamp
   BEFORE INSERT ON users
   FOR EACH ROW
   BEGIN
       SET NEW.created_at = CURRENT_TIMESTAMP;
   END;
   
   -- AFTER UPDATE trigger
   CREATE TRIGGER log_update
   AFTER UPDATE ON users
   FOR EACH ROW
   BEGIN
       INSERT INTO audit_log (table_name, operation, user_id)
       VALUES ('users', 'UPDATE', NEW.id);
   END;

4. Special References in Triggers

   • NEW.column: Reference new values after INSERT or UPDATE operation
   • OLD.column: Reference old values before UPDATE or DELETE operation
   • NEW is only available in INSERT and UPDATE
   • OLD is only available in UPDATE and DELETE

5. Trigger Use Cases

   • Automatic maintenance of timestamp fields
   • Implement audit logging
   • Data validation and constraints
   • Cascading operations
   • Data synchronization

6. Managing Triggers

   sql
   -- View triggers
   SELECT * FROM sqlite_master WHERE type = 'trigger';
   
   -- Drop trigger
   DROP TRIGGER trigger_name;
   
   -- List triggers for a table
   PRAGMA trigger_list(table_name);

7. Limitations and Considerations

   • Triggers cannot directly call other triggers (recursion)
   • Trigger execution affects performance
   • Complex trigger logic is difficult to debug
   • Avoid executing time-consuming operations in triggers

Triggers are an important tool for SQLite to implement automated data maintenance and business logic.