Hack 95. Issue Automatic Updates

When a table is altered, you may want other data to be updated. You could run a separate update query to do this, but then you would have to update application code and inform your users that whenever they run a certain query, they have to run another query. There is another way to make sure that whenever one table is updated, other data gets updated.

Suppose you have a banking-type problem involving two tables: TRansactions and accBalance, as shown in Table 11-4 and Table 11-5.

Table 11-4. The transactions table

accNo	Amount
00000001	20.00
00000001	50.00
00000001	30.00
00000002	20.00

Table 11-5. The accBalance table

accNo	Balance
00000001	40.00
00000002	20.00

The accBalance table needs to accurately reflect the sum of all amounts for each account number stored in transactions. You could use a VIEW to represent this:

CREATE VIEW accBalance AS
 SELECT accNo,SUM(amount) FROM transactions
 GROUP BY accno
;

This is the right approach in most cases; however, if you specifically want to introduce redundancy or if you have a particularly expensive query to run, you can create a realized version of the balance view. If you needed to determine which account has the highest balance, accBalance, implemented as a table, may perform better than a view.

This is how you create the table:

CREATE TABLE accBalance (
 accNo CHAR(8),
 balance DECIMAL(8,2),
 PRIMARY KEY (accNo)
);

The accBalance table must be kept up-to-date as new account transactions are inserted. You can achieve this with a trigger. A trigger instructs the database system to always execute a particular set of instructions whenever an INSERT, DELETE, or UPDATE is performed on a table. In this example, you could create a trigger so that whenever an INSERT happens to transactions, the accBalance information is updated.

Triggers are available in MySQL, Oracle, PostgreSQL, and SQL Server. The implementation of triggers is slightly different across the different platforms. You are concerned here with INSERT queries on TRansactions, so you need to create an INSERT trIGGER.

You can create DELETE triggers and UPDATE triggers to cope with changes to existing transactions rows. In this example, rows in the transactions table are never modified after the INSERT. If an incorrect transaction gets into the system you do not delete it; you insert a "reversal" to undo the original. This maintains a strong audit trail.

This is what you would like the trigger to accomplish:

SQL> SELECT * FROM accBalance WHERE accNo = '00000001';
ACCNO BALANCE
------------------------ ----------
00000001 40

SQL> INSERT INTO transactions VALUES ('00000001',-10.00);
1 row created.

SQL> SELECT * FROM accBalance WHERE accNo = '00000001';
ACCNO BALANCE
------------------------ ----------
00000001 30

Your trigger definition needs to work out which rows were altered so that your trigger query updates only the minimum number of rows possible. Different database systems have different ways of handling multiple row changes, and for passing on the information concerning what has actually changed.

11.6.1. Oracle

Oracle has a FOR EACH ROW construct, which means that the trigger is called for each row affected and not once per query, as would normally be the case. The data being inserted is held in a special table, :new, until the trigger is finished, and then Oracle moves the data from the :new table to the table that is being inserted into. The :new table has only one row:

CREATE TRIGGER transaction_after_insert
AFTER INSERT ON transactions
 FOR EACH ROW
 UPDATE accbalance set balance = balance + :new.amount
 where accbalance.accno = :new.accno
/

Note that the standard delimiter, ;, is not used in this query. Instead, the query must end with a slash (/).

11.6.2. MySQL

MySQL has a similar trigger definition to that used in Oracle, including the FOR EACH ROW construct. The significant differences are that ; is needed at the end of the query, and the data concerning the row being inserted is stored in NEW rather than :new:

CREATE TRIGGER transaction_after_insert
AFTER INSERT ON transactions
 FOR EACH ROW 
 UPDATE accbalance set balance = balance + NEW.amount
 where accbalance.accno = NEW.accno
;

 

11.6.3. SQL Server

SQL Server does not use the FOR EACH ROW loop. Instead, it uses an INSERTED table, which contains each row inserted. As a result, the UPDATE query needs to be run on each row inserted, which makes it a little more complex:

CREATE TRIGGER transaction_after_insert
ON transactions AFTER INSERT AS
BEGIN
 UPDATE accbalance set balance = balance +
 (SELECT SUM(amount) FROM INSERTED WHERE accbalance.accno=INSERTED.accno)
 WHERE accbalance.accno IN (SELECT DISTINCT accno FROM INSERTED)
 ;
END;
GO

 

11.6.4. PostgreSQL

In PostgreSQL, you need to use the plpgsql language (see "Deploy Applications" [Hack #92] on how to activate the language for your database):

CREATE OR REPLACE FUNCTION transaction_update( )
RETURNS trigger
AS '
BEGIN
 UPDATE accbalance set balance = balance + new.amount
 WHERE accbalance.accno = new.accno
 ;
 return new;
END
'LANGUAGE plpgsql;

CREATE TRIGGER transaction_after_insert
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE PROCEDURE transaction_update( );