[SQL Question] Creating a computed field on a table on insert

[David Burstin]

Say I have a table A which has 2 relevant columns (others columns include
key and other data):

SerialNumber, TimesOnTableB

Table B has a SerialNumber column.

A.TimesOnTableB = (SELECT COUNT(*) FROM TableB WHERE B.SerialNumber =
A.SerialNumber)

How can I update A.TimesOnTableB every time a new record is inserted to
TableA (NOT TableB)?

I tried an update trigger on TableA, but an update trigger can't modify the
data that is being updated (or am I wrong about this).

Any suggestions welcome.

Cheers
Dave