I have a page that performs an update to a table in our ERP system. We are trying to come up with a process where certain people have certain fields they may update without giving them access to the entire table. I got that working correctly. Now I've been tasked with creating and updating an "audit table" that updates anytime someone updates the main table. The fields they want are the current fields' values as well as the new fields' values with a userid and time stamp as to when it was updated. I have a stored procedure created for the initial update (included below). Then I have the stored procedure written to update the audit table (also included below).
I need to fire those both off with a single button click in an asp.net page. I have a formview that displays the current values and a table outside the formview that is automatically populated with the current values shown in the formview. I can't update from there though because of limitations in our ERP system.
What is the best way to accomplish this goal? I'd hoped there was a way to fire them both off from the single click on the asp.net page, but so far have not been able to make that work. I can post the asp.net code if needed.
ALTER PROCEDURE [dbo].[AER_UPDATE_PRODUCT_MASTER]
ALTER PROCEDURE [dbo].[AER_PRODUCT_MASTER_LTD_AUDIT]
SET NOCOUNT ON;
SET [EXISTING_PART_CODE] = @EXISTING_PART_CODE,
[OLD_PART_DESC_1] = @OLD_PART_DESC_1,
[NEW_PART_DESC_1] = @NEW_PART_DESC_1,
[OLD_PART_DESC_2] = @OLD_PART_DESC_2,
[NEW_PART_DESC_2] = @NEW_PART_DESC_2,
[OLD_UNIT_SALES] = @OLD_UNIT_SALES,
[NEW_UNIT_SALES] = @NEW_UNIT_SALES,
[OLD_PRODUCT_GROUP] = @OLD_PRODUCT_GROUP,
[NEW_PRODUCT_GROUP] = @NEW_PRODUCT_GROUP,
[OLD_PRODUCT_CLASS] = @OLD_PRODUCT_CLASS,
[NEW_PRODUCT_CLASS] = @NEW_PRODUCT_CLASS,
[OLD_PRODUCT_TYPE] = @OLD_PRODUCT_TYPE,
[NEW_PRODUCT_TYPE] = @NEW_PRODUCT_TYPE,
[OLD_SYS_PART_DETAIL] = @OLD_SYS_PART_DETAIL,
[NEW_SYS_PART_DETAIL] = @NEW_SYS_PART_DETAIL,
[NEW_SYS_MODIFY_USER] = @NEW_SYS_MODIFY_USER,
[NEW_SYS_MODIFIED_DATE] = @NEW_SYS_MODIFIED_DATE