> Yes, it's a web app, though, and only one piece of code will access
> the table. I can synchronize that code so that it avoids the
> problem. That creates a huge bottleneck but it's a very lightly
> loaded app for a small office.

If you want to avoid bottleneck, get a pool of IDs once and use them
one by one until you're out of IDs. Then you get new portion of IDs.
Generator incrementing used to be out of transaction control, so
you're on the safe side an no application will use the same ID.
Generators are 32-bit integers, and suprisingly, that's a big number.
Somebody have counted that incrementing it each second would require
more than 100 years to run out of free IDs.

> But in the code below wouldn't I want the trigger active AFTER insert
> so that if I insert the key I get from the code above, the trigger
> generated number won't be overwritten by the number being manually
> inserted?

No, when you're in AFTER INSERT trigger, data are already in database.
So, if you want to modify them you must use UPDATE statement. Simple
assigning to a NEW.field_name will not work. So, only BEFORE INSERT
trigger can be used to solve your task.

Best regards,
Roman Rokytskyy