Triggers

A trigger is an event-driven rule on a database, where an operation is initiated when some other transaction (event) takes place. Triggers may be set to fire on any DELETE, INSERT, or UPDATE on a particular table, or on an UPDATE OF particular columns within a table.

Creating and Dropping Triggers

The syntax to create a trigger on a table is as follows:

 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [BEFORE | AFTER] database-event ON [database-name .]table-name trigger-action 

The TRigger-name is user-specified and must be unique across all objects in the databaseit cannot share the same name as a table, view, or index.

The trigger can be set to fire either BEFORE or AFTER database-event; that is, either to pre-empt the transaction and perform its action just before the UPDATE, INSERT, or DELETE takes place, or to wait until the operation has completed and then immediately carry out the required action.

If the database-event is specified as UPDATE OF column-list, it will create a trigger that will fire only when particular columns are affected. The trigger will ignore changes that do not affect one of the listed columns.

The TRigger-action is further defined as

 [FOR EACH ROW | FOR EACH STATEMENT] [WHEN expression] BEGIN  trigger-step; [trigger-step;] * END 

At present only FOR EACH ROW TRiggers are supported, so each trigger stepwhich may be an INSERT, UPDATE, or DELETE statement or SELECT with a function expressionis performed once for every affected row in the transaction that causes the trigger to fire. The WHEN clause can be used to cause a trigger to fire only for rows for which the WHEN clause is true. The WHEN clause is formed in the same way as the WHERE clause in a SELECT statement.

The WHEN clause and any trigger-steps may reference elements of the affected row, both before and after the trigger action is carried out, as OLD.column-name and NEW.column-name respectively. For an UPDATE action both OLD and NEW are valid. An INSERT event can only provide a reference to the NEW value, whereas only OLD is valid for a DELETE event.

An ON CONFLICT clause can be specified in a trigger-step; however, any conflict resolution algorithm specified in the statement that causes the trigger to fire will override it.

As you might expect, the syntax to drop a trigger is simply

 DROP TRIGGER [database-name .] table-name 

If you forget the name of a trigger, you can query sqlite_master using type = 'trigger' to find all the triggers on the current database.

Using Triggers

In the last chapter we mentioned that triggers could be used to implement a cascading delete, so that rows from a table that referenced a foreign key would also be deleted if the foreign key were deleted from its own table. The trigger in the following example shows how this could be implemented on the demo database to delete entries from the timesheets table if the foreign key project_code is deleted from the projects table.

 sqlite> CREATE TRIGGER projcode_cascade    ...> AFTER DELETE ON projects    ...> BEGIN    ...>   DELETE FROM timesheets WHERE project_code = OLD.code;    ...> END; 

Similarly, we could create a trigger that maintains data integrityif the project code changes in the projects table, the child records in timesheets will be updated to reflect the new foreign key value.

 sqlite> CREATE TRIGGER projcode_update    ...> AFTER UPDATE OF code ON projects    ...> BEGIN    ...>   UPDATE timesheets    ...>   SET project_code = NEW.code    ...>   WHERE project_code = OLD.code;    ...> END; 

A quick test verifies that this trigger is working as we want it to:

 sqlite> UPDATE projects    ...> SET code = 'NEWCODE'    ...> WHERE code = 'ABCCONS'; sqlite> SELECT count(*)    ...> FROM timesheets    ...> WHERE project_code = 'NEWCODE'; count(*) ---------- 3 

Interrupting a Trigger

Within the trigger-steps it is possible to interrupt the command that caused the trigger to fire and execute one of the conflict resolution algorithms available in an ON CONFLICT clause. This is done using the RAISE() function, which can be invoked using a SELECT statement as one of the following:

 RAISE (ABORT, error-message) | RAISE (FAIL, error-message) | RAISE (ROLLBACK, error-message) | RAISE (IGNORE) 

Issuing an ABORT, FAIL, or ROLLBACK within a trigger will cause the transaction to exit and take the relevant action, and the error-message parameter is returned to the user.

We could use this behavior to prevent a project code from being deleted from the projects table while rows exist in timesheets that use it as a foreign key, rather than the rather destructive cascading delete.

 sqlite> CREATE TRIGGER projcode_rollback    ...> BEFORE DELETE ON projects    ...> WHEN OLD.code IN (    ...>   SELECT project_code FROM timesheets    ...> )    ...> BEGIN    ...> SELECT RAISE(ROLLBACK, 'Timesheets exist for that project code');    ...> END; 

An attempted DELETE will produce an error:

 sqlite> DELETE FROM projects WHERE code = 'NEWCODE'; SQL error: Timesheets exist for that project code 

We can also verify that the DELETE transaction was rolled back.

 sqlite> SELECT * FROM projects    ...> WHERE code = 'NEWCODE'; code        client_id   title                start_date  due_date ----------  ----------  -------------------  ----------  ---------- NEWCODE     502         Ongoing consultancy  20030601 

Using RAISE(IGNORE) causes the current trigger to be abandoned; however, any changes made up to that point will be saved and if the trigger was fired as the result of another trigger, that outer trigger's execution will continue.

Creating a Trigger on a View

The syntax for using triggers on views is slightly different than with tables. This functionality is provided as a way of intercepting INSERT, UPDATE, and DELETE operations on a view, usually to simulate that action by executing the actual steps necessary to make the requested data change appear in the view. Because a view may join two or more tables, a number of steps may be required.

The syntax for creating a trigger on a view is

 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF database-event ON [database-name .] view-name trigger-action 

As before, database-event may be DELETE, INSERT, UPDATE, or UPDATE OF column-list, and the trigger-action is one or more SQL operations contained between the keywords BEGIN and END.