We have two tables product and product_unapproved :
Product Table
| Product_code | Product_price | Created_Date | Created_By | Updated_Date | Updated_By |
|---|---|---|---|---|---|
| 1 | 25 | 25/08/2022 | Admin1 | 30/08/2022 | Admin2 |
| 2 | 40 | 26/08/2022 | Admin1 | 31/08/2022 | Admin1 |
| 3 | 50 | 27/08/2022 | Admin2 | 01/09/2022 | Admin3 |
Product Unapproved Table
| Product_code | Product_price | Created_Date | Created_By | Updated_Date | Updated_By |
|---|---|---|---|---|---|
| 2 | 30 | 26/09/2022 | Manu1 |
WHenever manufacturer updates price it goes in product_unapproved table and admin needs to approve this record. On admin approval record from product_unapproved is deleted and values are updated in product table. Admin can also directly update price value which will update record in product table directly
Now I need to maintain audit table
Product_Audit
| Product_code | Product_price_old_value | Product_price_new_value | Manufacturer_Name | Admin_Name | Approval-Time |
|---|
In which Manufacturer_name will be updated if values are copied from product_unapproved table to product_table else it should be null.
I tried implementing this by triggers but created_by value in product_unapproved table is deleted before my trigger in product table is called
