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

16.1. Procedural Solution

The obvious way to do this is to write a procedure that scans the batch numbers and throws them into the Account when they are a multiple of 100.

New SQL programmers do not think of TRIGGERS as procedural code, but they are. Unlike declarative code, they also do nothing for the optimizer.

CREATE TRIGGER AccountBatchTally
AFTER UPDATE ON Accounts
REFERENCING NEW AS N
MERGE INTO Accounts_100
USING LATERAL(VALUES(N.account_id, N.batch_nbr
              - (N.batch_nbr/100)))
      AS X (account_id, batch_nbr)
ON X.account id = Accounts 100.account id
WHEN MATCHED
THEN UPDATE SET Accounts_100.batch_nbr = X.batch_nbr
WHEN NOT MATCHED
THEN INSERT VALUES (X.account_id, X.batch_nbr);

The use of the LATERAL table construct is to get to the NEW table, ...