"Could someone elaborate on the staging method and can this be done real-time rather than batch?"
It can, but it would not be something from within SQL 2K. As far as I know there is nothing in SQL that works similar to C# "delegates" or java "events".
And, technically, "real-time" is impossible. There is always latency, even if it is milliseconds.
For simplicities sake you can still do that all in SQL.
First, create a proc that:
check if record exist in the email staging table
I. sends an email
II. archive the record
III. goto 1)
I. Do nothing
Then you have a few figure out the maximum allowable latency (3 seconds, say), and schedule the proc to run once every 3 seconds (in SQL Jobs). Make sure the proc is highly optimized, and you'll be fine. Connection overhead could be a problem for this, but I believe SQL Jobs does some connection pooling.
Another option is to set up a controller proc that does a "wait for" step and executes the actual proc every 3 seconds. However, this is prone to failure if the proc dies or it's connection is killed, so you need a monitor for the monitor, which is rather messy.