Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

Chapter 16. Keeping Computed Data

THIS IS BASED on a posting in a DB2 newsgroup. Whenever a row is inserted into an Accounts table, the poster wants to either update an existing row in a separate tally table or insert a row into that tally table with the new account and batch number for every 100th batch within an account. Here is a skeleton schema:

CREATE TABLE Accounts
(account_id INTEGER NOT NULL,
 batch_nbr INTEGER NOT NULL,
 PRIMARY KEY (account_id, batch_nbr));

and put the last qualifying batch number into another table

CREATE TABLE Accounts_100
(account_id INTEGER NOT NULL,
 batch_nbr INTEGER NOT NULL PRIMARY KEY,
 PRIMARY KEY (account_id, batch_nbr));