Sharon,
> I think the best idea for this scenario is a trigger, I had a go at
> writing one for this but since a trigger runs for each row my
> function code did not work.
OK. When I get back to my office, I'll send some sample code; I have a
trigger I use all the time for this purpose.
-Josh