Triggers

For those programmers who pride themselves on their laziness, triggers are a wonderful thing. By creating a trigger you can get maximum effect with minimum effort.

Triggers are activated by an INSERT, DELETE, or UPDATE SQL statement. They are often used to maintain referential integrity and avoid orphaned records—for example, deleting an invoice might well trigger deletion of all related invoice items. We're going to create three triggers for our application: one to mimic a timestamp field, another to show the advantages of laziness, and finally a trigger to demonstrate how a view can be "updated."

The timestamp triggers are shown in Listing 15-3. They are activated whenever a record in the tblresources table is added or updated.

Listing 15-3. Triggers

CREATE TRIGGER insert_resources AFTER INSERT ON tblresources
BEGIN
    UPDATE tblresources SET whenaltered = DATETIME('NOW','LOCALTIME')
    WHERE id = new.id;
END;
CREATE TRIGGER update_resources AFTER UPDATE ON tblresources
BEGIN
    UPDATE tblresources SET whenaltered = DATETIME('NOW','LOCALTIME')
    WHERE id = new.id;
END;
CREATE TRIGGER add_date AFTER INSERT ON tblresources
BEGIN
    UPDATE tblresources SET whenadded = DATE('NOW','LOCALTIME')
    WHERE id = new.id;
END;
CREATE TRIGGER delete_link INSTEAD OF DELETE ON specific_link
FOR EACH ROW
BEGIN
    DELETE FROM tblresources
    WHERE id = old.id;
END;

There is no need to remember to update the whenaltered field each time a change is made to a record—the insert_resources and update_resources triggers will do this for you. The current date and time will be added in the background. Effectively, this field will now function like a MYSQL TIMESTAMP field.

Likewise with the add_date trigger, also shown in Listing 15-3. We want to highlight new links. This trigger makes it possible to capture the date a link is added. By using a trigger we don't have to worry about forgetting to maintain this field, and we don't have to write additional code each time a record is added.

Creating a trigger on a view is a convenient way of performing an "update" against a view. By themselves, views are not updatable. If you attempt to delete from a view that has no associated trigger, you'll get a warning like the following:

Warning: SQLiteDatabase::query() [function.query]: cannot
modify specific_link because it is a view...

We solved this problem in the trigger we created on the view specific_link shown in Listing 15-3. Because we used an INSTEAD OF clause, any attempt to delete from this view instead removes the appropriate record from the table, tblresources.

In this trigger we have specified FOR EACH ROW. Doing so is optional. A FOR EACH STATEMENT clause also exists but is not yet supported.

The WHERE clause of a trigger is somewhat intuitive but may cause some confusion. Using new.id to specify a newly inserted record and old.id for a deleted record clearly makes sense. Either old or new may be used when a record is updated.

Using triggers is very convenient, although the same effect could be achieved programmatically. But because triggers are embedded in the database, they are activated even when you make changes from the command line. Triggers help maintain the integrity of your database when it is modified outside of your application. Laziness has its rewards.