CHAPTER 6 – SQLite – Triggers

SQLite has some advanced features--for example, it sup- ports triggers. Triggers can be set to data-modifying queries, and consist of a small SQL script that runs whenever the specified action is "triggered." Our example will use triggers to automatically update our search index whenever a new document is added. To define the trigger, we extend our create.php script and add the following code to the file: ... $trigger_query = " CREATE TRIGGER index_new AFTER INSERT ON document BEGIN SELECT php_index(new.id, new.title, new.intro, new.body); END;"; $db->query($trigger_query); ?> This creates a trigger named index_new to be run after an insert query on the document table. The SQL script that runs when the trigger fires is a simple select query, but that query is not that simple as it appears. You can see that there is no FROM clause, nor is the php_index() function a function defined in the SQL standard. This brings us to the next cool feature of SQLite: User Defined Functions.