Apache Derby: Developing Python Applications

Here we introduce another general Apache Derby feature: triggers.

A trigger is an SQL object that defines a set of actions to be performed when specific delete, update, or insert events occur in a specified table in the database.

In the following example, we create a new table menu.counter that contains a single row to track the number of items within the menu.food table.

CREATE TABLE menu.counter (id INTEGER, num INTEGER);
INSERT INTO menu.counter(id, num) values (1, 1);
CREATE TRIGGER incrementCount
    AFTER INSERT
    ON menu.food FOR EACH ROW MODE DB2SQL
    UPDATE menu.counter SET num = num + 1 WHERE id = 1;
CREATE TRIGGER decrementCount
    AFTER DELETE
    ON menu.food FOR EACH ROW MODE DB2SQL
    UPDATE menu.counter SET num = num - 1 WHERE id = 1;

Now, as you insert and delete rows, you can check the value of menu.counter for the given ID to see how many instances of that food item exist in the menu.food table.