Question: Please help with oracle pl/sql What is wrong with my create trigger code, it is not working and how do i test: CREATE OR REPLACE

Please help with oracle pl/sql

What is wrong with my create trigger code, it is not working and how do i test:

CREATE OR REPLACE TRIGGER eventLog

BEFORE UPDATE or delete ON ProductTable

FOR EACH ROW

BEGIN

CASE

WHEN UPDATING THEN

insert into ProductHistory values (history_seq.nextval,:old.ProductID,:old.ProductName,:old.ListPrice,:old.Category,sysdate,'update');

WHEN DELETING THEN

insert into ProductHistory values (history_seq.nextval,:old.ProductID,:old.ProductName,:old.ListPrice,:old.Category,sysdate,'delete');

END CASE;

END;

 Please help with oracle pl/sql What is wrong with my create

here is the code to create product table:

CREATE TABLE ProductTable(

ProductID INTEGER NOT NULL,

ProductName VARCHAR2(50) NOT NULL,

ListPrice NUMBER(10,2),

Category INTEGER NOT NULL

);

/

INSERT INTO ProductTable VALUES(299,'Chest',99.99,10);

INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11);

INSERT INTO ProductTable VALUES(301,'Megaland Play Tent',59.99,11);

INSERT INTO ProductTable VALUES(302,'Wind-Up Water Swimmers',2.00,11);

INSERT INTO ProductTable VALUES(303,'Garmin Pocket or Vehicle GPS Navigator',609.99,12);

Use the product table from this module (do not modify table or column names) and modify the EventLog trigger that it will store all changed (deleted or updated) values from the Product table in the ProductHistory table. 1. Provide the code of your working trigger (2 points), code you used to create the ProductHistorytable (3 points). The code will be copied from your report and tested. Test your code and include your test case in the report (code and screenshots of the results) (1 point for each successful test case). To receive points for testing, the test cases must test different aspects of your code. Sample output Original data in the table SELECT*from ProductTable; PRODUCTID LISTPRICE CATEGORY 10 299 Small Chest 300 Wave Cruiser 301 Megaland Play Tent 302 Wind-Up Water Swimners 303 Garmin Pocket or Vehicle GPS Tavigator 99.99 49.99 59.99 609.99 12 SELECT* from ProductHistorTable PRODU EI PRODU E LISTPRICECATEGO. MODIFD. Test update for multiple rows: Update to S55 prices for item 299 to $55 UPDATE SET 55 where IN (299, 302 SELECT* from ExsntLogTable to show that two rows showing old prices were inserted. UPDATE ProductTable set ListPrice-55 where productid in (299,302) elect w fron producthistorytable: Script Output x Query Result x 1SQL Al Rows Fetched: 2n 0.004 seconds PRODUCTID LISTPRICECATEGORYMODIFDATE 299 Small Chest 99.99 10 06.01.2018:16:36:59 302 Wind-Up Water Swimmers 11 06.01.2018:16:36:59

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!