**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Views, Triggers, and Advanced Features **Topic:** Using triggers to automate actions on data changes ### Introduction to Triggers In SQLite, triggers are a powerful feature that allows you to automate actions in response to changes in your data. A trigger is essentially a stored procedure that is executed automatically whenever a specific event occurs, such as an INSERT, UPDATE, or DELETE operation. In this topic, we will explore how to use triggers to automate actions on data changes, including creating, managing, and optimizing triggers for optimal performance. ### Creating Triggers To create a trigger in SQLite, you use the CREATE TRIGGER statement. The basic syntax for creating a trigger is as follows: ```sql CREATE TRIGGER trigger_name [BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE] ON table_name FOR EACH [ROW | STATEMENT] BEGIN -- Trigger code here END; ``` Here's a breakdown of the options: * `BEFORE`, `AFTER`, or `INSTEAD OF` specifies when the trigger should be executed. `BEFORE` triggers are executed before the operation, `AFTER` triggers are executed after the operation, and `INSTEAD OF` triggers replace the original operation. * `INSERT`, `UPDATE`, or `DELETE` specifies the type of operation that triggers the trigger. * `ON table_name` specifies the table on which the trigger should be executed. * `FOR EACH [ROW | STATEMENT]` specifies whether the trigger should be executed for each row affected by the operation or once for the entire statement. * The `BEGIN` and `END` keywords enclose the trigger code. ### Example: Creating a Simple Trigger Suppose we have a table called `employees` with the following structure: ```sql CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT, salary REAL ); ``` We want to create a trigger that prevents employees from having a salary below the minimum wage. We can create a `BEFORE` trigger on the `UPDATE` operation as follows: ```sql CREATE TRIGGER prevent_low_salary BEFORE UPDATE OF salary ON employees BEGIN SELECT CASE WHEN NEW.salary < 25.0 THEN RAISE (ABORT, 'Salary cannot be less than the minimum wage') END; END; ``` In this example, the trigger `prevent_low_salary` is executed before the `UPDATE` operation on the `salary` column. If the new salary is less than the minimum wage, the trigger raises an error and aborts the operation. ### Managing Triggers To view the triggers defined on a table, you can use the `.schema` command in the SQLite shell: ```sql .schema employees ``` This will display the schema of the `employees` table, including any triggers defined on it. To drop a trigger, you can use the `DROP TRIGGER` statement: ```sql DROP TRIGGER prevent_low_salary; ``` ### Optimizing Triggers Triggers can have a performance impact on your database, especially if they are complex or execute frequently. Here are some tips for optimizing triggers: * Keep trigger code simple and concise. * Use `BEFORE` triggers instead of `AFTER` triggers whenever possible. * Use `FOR EACH ROW` instead of `FOR EACH STATEMENT` whenever possible. * Avoid using triggers that execute frequently, such as on every insert or update. ### Conclusion Triggers are a powerful feature in SQLite that allows you to automate actions in response to changes in your data. By understanding how to create, manage, and optimize triggers, you can improve the performance and integrity of your database. **Practical Exercise:** Create a trigger that prevents employees from having a salary above the maximum wage. **Additional Resources:** * SQLite documentation: [CREATE TRIGGER](https://sqlite.org/lang_createtrigger.html) * SQLite documentation: [DROP TRIGGER](https://sqlite.org/lang_droptable.html) * SQLite documentation: [TRIGGER](https://sqlite.org/lang_trigger.html) **Leave a Comment or Ask for Help:** If you have any questions or need help with creating triggers, please leave a comment below. We'll be happy to assist you.