Question 2) Maintaining an Audit Trail of Product Table Changes The accuracy of product table data is critical, and the Brewbean’s owner wants to have an audit file containing information on all DML activity on the BB_PRODUCT table. This information should include the ID of the user performing 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 to hold the relevant data, and then create a trigger named BB_AUDIT_TRG that fires an update to this table whenever a specified column in the BB_PRODUCT table changes. Preparation : Step1: ALTER TRIGGER sales_date_trg DISABLE; Step2: 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) ); Step3: Your Trigger code comes here. Make sure you compile your trigger without any syntax error Step4: test the process . Run below command to see if it will generate any activity Audit log or not. UPDATE bb_product SET salestart = '05-MAY-07', Saleend = '12-MAY-07', saleprice = 9 WHERE idProduct = 10; Step5: Test with SELECT statement to see any record goes into Audit table SELECT * FROM bb_prodchg_audit;
Question 2) Maintaining an Audit Trail of Product Table Changes
The accuracy of product table data is critical, and the Brewbean’s owner wants to have an audit
file containing information on all DML activity on the BB_PRODUCT table. This information
should include the ID of the user performing 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 to hold the relevant data, and then create a trigger named
BB_AUDIT_TRG that fires an update to this table whenever a specified column in the
BB_PRODUCT table changes.
Preparation :
Step1: ALTER TRIGGER sales_date_trg DISABLE;
Step2:
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) );
Step3: Your Trigger code comes here. Make sure you compile your trigger without any syntax error
Step4: test the process . Run below command to see if it will generate any activity Audit log or not.
UPDATE bb_product
SET salestart = '05-MAY-07',
Saleend = '12-MAY-07',
saleprice = 9
WHERE idProduct = 10;
Step5: Test with SELECT statement to see any record goes into Audit table
SELECT * FROM bb_prodchg_audit;
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
