I have a bit of an odd situation, and I'm struggling with populating a field in a table. I should also mention I'm pretty new to SQL Server.
What is in use: I'm working in a SQL Server 2008 database. There are two tables of concern, PO_APPROVAL_LOG, and PO_HDR.
The process that is being performed here is the creation of a new purchase order. In some cases it requires a manager approval to create the PO. I'm using a winform (C#) that authenticates the manager against Active Directory, if the manager is a member of a certain group, PO is approved.
My situation: there is a 3rd party application in use, and I'm writing some validation code (mentioned above) which executes before the save event can occur in the application. If certain criteria is met, my validation code returns back TRUE and allows the application to proceed with the save. When the save is kicked off, a handful of tables in the database are inserted into, including PO_HDR. I cannot change the order which events occur, I can only write these validation rules. The application will not continue until it receives a specific object back from my code which has a SUCCESS property set to TRUE or FALSE.
The problem I'm having is related to logging the PO approval into the PO_APPROVAL_LOG table. Right now, the PO_APPROVAL_LOG table is inserted into when the manager would submit their credentials. This occurs before PO_HDR is populated. I need to include the PO_HDR.PO_NUMBER, in order to do that, I need to wait until after PO_HDR populates, and grab the PO_NUMBER value, and update the corresponding PO_APPROVAL_LOG entry. Very few POs actually need approval, the PO_APPROVAL_LOG table shouldn't see a lot of action.
I cannot create a trigger on the PO_HDR table.
For the sake of experimentation, I played with creating an AFTER INSERT trigger on PO_APPROVA_LOG that used a waitfor delay to see if I could fetch the PO_NUMBER after PO_HDR had a chance to populate. (Yes, I do know waitfor delay in a trigger is a terrible idea, it was just to experiment in a play database). This didn't work, as the application didn't get a response from my validation code of TRUE until after the insert and subsequent trigger execution finished.
My question: Could a SQL Agent job be a possible solution? It would need to run every minute or two. One of my concerns is, would this be very taxing on the database? If the job ran and determined if a procedure should be called to update the log table (found one or more NULL PO_NUMBER values in PO_APPROVAL_LOG), perhaps that could work.
Any other advice? This has been giving me trouble for a while.
If any of that is unclear, please ask! Thanks!
There is no way to do it in sql, try to perform this from your code.