Apologies if I'm posting in the wrong place - but this seemed the closest.
What I've done.....
When a loading process is finished, it updates the final stage status of a record to Completed.
I've put in a place a trigger which then inserts a row into another database on this status change
This then fires off 3 triggers to perform certain subsequent activity - in effect 3 procedures.
The issue I have it that this is all part of the original transaction. (In this case, if the two databases were out of sync, I'm not bothered, I have a recovery mechanism.) My concern, is that whilst the 3 triggered procedures are running on Database B, I could be holding locks on the original database.
Ideally, if using Ingres. I would write the three procedures to register for a Database event 'File Completed'. The trigger on the Database Insert table would raise the event 'File Completed' with the associated ID, and the original transaction would commit.
I think a similar thing could be done in Oracle wih Advanced Queueing.
I thought I might be able to use this solution with SQL Server - but it seems to be only DDL. Does anyone have any elegant solutions?