AS v_product_count INTEGER; BEGIN -- count the number of products with the -- supplied product_id (will be 1 if the product exists) SELECT COUNT(*) INTO v_product_count FROM products WHERE product_id = p_product_id; -- if the product exists (v_product_count = 1) then -- update that product's price IF v_product_count = 1 THEN UPDATE products SET price = price * p_factor WHERE product_id = p_product_id; COMMIT; END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; END update_product_price;END product_package;/CREATE TRIGGER before_product_price_updateBEFORE UPDATE OF priceON productsFOR EACH ROW WHEN (new.price < old.price * 0.75)BEGIN dbms_output.put_line('product_id = ' || :old.product_id); dbms_output.put_line('Old price = ' || :old.price); dbms_output.put_line('New price = ' || :new.price); dbms_output.put_line('The price reduction is more than 25%'); -- insert row into the product_price_audit table INSERT INTO product_price_audit ( product_id, old_price, new_price ) VALUES ( :old.product_id, :old.price, :new.price );END before_product_price_update;/-- insert sample data into customers tableINSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES ( 1, 'John', 'Brown', '01-JAN-1965', '800-555-1211');INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES ( 2, 'Cynthia', 'Green', '05-FEB-1968', '800-555-1212');INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES ( 3, 'Steve', 'White', '16-MAR-1971', '800-555-1213');INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES ( 4, 'Gail', 'Black', NULL, '800-555-1214');INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES ( 5, 'Doreen', 'Blue', '20-MAY-1970', NULL);-- commit the transactionCOMMIT;-- insert sample data into product_types tableINSERT INTO product_types ( product_type_id, name) VALUES ( 1, 'Book');INSERT INTO product_types ( product_type_id, name) VALUES ( 2, 'Video');INSERT INTO product_types ( product_type_id, name) VALUES ( 3, 'DVD');INSERT INTO product_types ( product_type_id, name) VALUES ( 4, 'CD');INSERT INTO product_types ( product_type_id, name) VALUES ( 5, 'Magazine');-- commit the transactionCOMMIT;-- insert sample data into products tableINSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 1, 1, 'Modern Science', 'A description of modern science', 19.95);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 2, 1, 'Chemistry', 'Introduction to Chemistry', 30.00);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 3, 2, 'Supernova', 'A star explodes', 25.99);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 4, 2, 'Tank War', 'Action movie about a future war', 13.95);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 5, 2, 'Z Files', 'Series on mysterious activities', 49.99);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 6, 2, '2412: The Return', 'Aliens return', 14.95);INSERT INTO products ( product_id, product_type_id, name, description, price)VALUES ( 7, 3, 'Space Force 9', 'Adventures of heroes', 13.49);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 8, 3, 'From Another Planet', 'Alien from another planet lands on Earth', 12.99);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 9, 4, 'Classical Music', 'The best classical music', 10.99);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 10, 4, 'Pop 3', 'The best popular music', 15.99);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 11, 4, 'Creative Yell', 'Debut album', 14.99);INSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 12, NULL, 'My Front Line', 'Their greatest hits', 13.49);-- commit the transactionCOMMIT;-- insert sample data into