The accuracy of product table data is critical and the Brewbean's owner wants to have an audit
file that contains information regarding all DML activity on the BB_PRODUCT table. This information should include the user id of the user issuing the DML action, the date, the original values
of the changed row, and the new values. This audit table needs to track specific columns of
concern, including PRODUCTNAME, PRICE, SALESTART, SALEEND, and SALEPRICE. Create a table named BB_PRODCHG_AUDIT that can hold the relevant data. Then create a trigger named BB_AUDIT_TRG that fires an update to this table whenever one of the specified columns in the BB_PRODUCT table is changed.
Be sure to issue the following command. If you created the SALES_DATE_TRG trigger in the chapter, it will conflict with this assignment.
ALTER TRIGGER sales_date_trg DISABLE;
Use the following UPDATE statement to test your trigger. Then, complete a rollback and disable the trigger when completed so that it does not affect other assignments.
UPDATE bb_product
Set salestart = '05-MAY-03',
SALEPRICE = 9
Where idProduct = 10;
*tip: Multiple columns can be listed in an OF clause of a trigger by simply listing them separated by commas.
*tip: Hint: You will need to create the following audit table first, then create a trigger on the update of productname, price, salestart, saleend, and saleprice columns of bb_product.
CREATE TABLE bb_prodchg_audit
(user_id VARCHAR2(10),
chg_date DATE,
name_old VARCHAR2(25),
name_new VARCHAR2(25),
price_old NUMBER(5,2),
price_new NUMBER(5,2),
start_old DATE,
start_new DATE,
end_old DATE,
end_new DATE,
sale_old NUMBER(5,2),
sale_new NUMBER(5,2) );