Maintaining audit trail of product table modifications

Question

The accuracy of product table data is crucial and the Brwebean’s. owner prefers to have an audit file which contains information regarding all DML activity on the BB_PRODUCT table. This information must point out the user id of the user running a DML statement, the date, the original values of the modified rows and the new values. The audit table requires to track specific columns of concern, comprising PRODUCTNAME, PRICE, SALESTART, SALEEND, and SALEPRICE. Build a table named BB_PRODCHG_AUDIT which can hold the relevant data. Then create a trigger named BB_AUDIT_TRG which fires on update to this table whenever one of the particular columns in the BB_PRODUCT table is modified.
 
Utilize the following update statement to test your trigger. Then, complete a rollback and disable the trigger whenever finished so that it does not affect other assignment questions.

Update bb_product set salestart = ’05-MAY-03’, saleend = ’12-MAY-03’, saleprice = 9 where idproduct = 10;

Answer 1

CREATE TABLE  "BB_PRODCHG_AUDIT"
   (    "PRODUCTNAME" VARCHAR2(25),
    "PRICE" NUMBER(6,2),
    "SALESTART" DATE,
    "SALEEND" DATE,
    "SALEPRICE" NUMBER(6,2)
   )

CREATE OR REPLACE TRIGGER  "BB_AUDIT_TRG"
BEFORE
update on "BB_PRODUCT"
for each row
begin
INSERT INTO BB_PRODCHG_AUDIT VALUES (:NEW.IDPRODUCT,:NEW.PRICE,:NEW.SALESTART,:NEW.SALEEND,:NEW.SALEPRICE);
end;
/
ALTER TRIGGER  "BB_AUDIT_TRG" DISABLE
/