Your task is to create a database trigger in your database schema to maintain the data integrity in "PARTS" and "ITEMS" tables. Specifically, the following business rules need to be applied:

Language is SQL(Oracle 12c)

1. If any row is inserted into "ITEMS" table, the ordered "QUANTITY" in "ITEMS" table needs to be subtracted from the corresponding "ONHAND" column in "PARTS" table.

2. The trigger is named as "onhand_to_items". Use the exact spelling of the name. (entire trigger definition )

3. Test the trigger by inserting a record into "ITEMS" table. The "PARTS" table should be updated accordingly by the trigger.

Tips/Hints: 1. There is more than one solution to reinforce the business rules.

2. A "BEFORE" or "AFTER" trigger is required for this type of application. I personally prefer "BEFORE" trigger. Either way should work.

3. The trigger will be executed when inserting a row into the "ITEMS" table

. 4. Test the trigger after creation. Use rollback if you insert multiple times when testing your script.

5. To insure the trigger is fired, you may put a print statement in the trigger, such as "DBMS_OUTPUT.PUT_LINE ('Trigger was fired.');". This approach is useful in learning or debugging process .

Im giving the necessary data. Please check below CREATE TABLE ITEMS (O_ID NUMBER(38) ,ID NUMBER(38) ,P_ID NUMBER(38) NOT NULL ,QUANTITY NUMBER(38) DEFAULT 1 NOT NULL );

CREATE TABLE PARTS (ID NUMBER(38) ,DESCRIPTION VARCHAR2(250) NOT NULL ,UNITPRICE NUMBER NOT NULL ,ONHAND NUMBER(38) NOT NULL ,REORDER VARCHAR2(40) NOT NULL );

CREATE TABLE CUSTOMERS (ID NUMBER(38) ,LASTNAME VARCHAR2(100) CONSTRAINT LASTNAME NOT NULL ,FIRSTNAME VARCHAR2(50) CONSTRAINT FIRSTNAME NOT NULL ,COMPANYNAME VARCHAR2(100) ,STREET VARCHAR2(100) ,CITY VARCHAR2(100) ,STATE VARCHAR2(50) ,ZIPCODE VARCHAR2(50) ,PHONE VARCHAR2(30) ,FAX VARCHAR2(30) ,EMAIL VARCHAR2(100) ,S_ID NUMBER(38) CONSTRAINT SALESREP NOT NULL );

CREATE TABLE SALESREPS (ID NUMBER(38) ,LASTNAME VARCHAR2(100) NOT NULL ,FIRSTNAME VARCHAR2(50) NOT NULL ,COMMISSION NUMBER(38) NOT NULL );

CREATE TABLE ORDERS (ID NUMBER(38) ,C_ID NUMBER(38) NOT NULL ,ORDERDATE DATE DEFAULT SYSDATE NOT NULL ,SHIPDATE DATE ,PAIDDATE DATE ,STATUS CHAR(1) DEFAULT 'F' );

CREATE TABLE PARTSLOG (CHANGEDATE DATE ,CHANGETYPE CHAR(1) ,USERID VARCHAR2(50) );

CREATE TABLE DETAILEDPARTSLOG (CHANGEDATE DATE ,USERID VARCHAR2(50) ,NEWID NUMBER(38) ,NEWDESCRIPTION VARCHAR2(250) ,NEWUNITPRICE NUMBER ,NEWONHAND NUMBER(38) ,NEWREORDER VARCHAR2(40) ,OLDID NUMBER(38) ,OLDDESCRIPTION VARCHAR2(250) ,OLDUNITPRICE NUMBER ,OLDONHAND NUMBER(38) ,OLDREORDER VARCHAR2(40) );

ALTER TABLE ITEMS ADD CONSTRAINT O_I_ID PRIMARY KEY (O_ID, ID) ;

ALTER TABLE PARTS ADD CONSTRAINT P_ID PRIMARY KEY (ID) ;

ALTER TABLE CUSTOMERS ADD CONSTRAINT C_ID PRIMARY KEY (ID) ;

ALTER TABLE SALESREPS ADD CONSTRAINT S_ID PRIMARY KEY (ID) ;

ALTER TABLE ORDERS ADD CONSTRAINT O_ID PRIMARY KEY (ID) ;

ALTER TABLE PARTS ADD CONSTRAINT PAR_DESCRIPTION UNIQUE (DESCRIPTION) ;

ALTER TABLE CUSTOMERS ADD CONSTRAINT CUS_NAME UNIQUE (LASTNAME ,FIRSTNAME) ; ALTER TABLE ITEMS ADD CONSTRAINT PARTS_FK FOREIGN KEY (P_ID) REFERENCES PARTS (ID) ;

ALTER TABLE ITEMS ADD CONSTRAINT ORDERS_FK FOREIGN KEY (O_ID) REFERENCES ORDERS (ID) ;

ALTER TABLE CUSTOMERS ADD CONSTRAINT SALESREPS_FK FOREIGN KEY (S_ID) REFERENCES SALESREPS (ID) ;

ALTER TABLE ORDERS ADD CONSTRAINT CUSTOMERS_FK FOREIGN KEY (C_ID) REFERENCES CUSTOMERS (ID) ;

-- PARTS

INSERT INTO parts VALUES (1,'Fax Machine',299,277,50); INSERT INTO parts VALUES (2,'Copy Machine',4895,143,25); INSERT INTO parts VALUES (3,'Laptop PC',2100,7631,1000); INSERT INTO parts VALUES (4,'Desktop PC',1200,5903,1000); INSERT INTO parts VALUES (5,'Scanner',99,490,200); COMMIT;

-- SALESREPS INSERT INTO salesreps VALUES (1,'Pratt','Nick',5); INSERT INTO salesreps VALUES (2,'Jonah','Suzanne',5); INSERT INTO salesreps VALUES (3,'Greenberg','Bara',5); COMMIT;

-- CUSTOMERS INSERT INTO customers VALUES (1,'Joy','Harold','McDonald Co.','4458 Stafford St.','Baltimore','MD','21209','410-983-5789',NULL,'harold_joy@mcdonald.com',3); INSERT INTO customers VALUES (2,'Musial','Bill','Car Audio Center','12 Donna Lane','Reno','NV','89501','775-859-2121','775-859-2121','musial@car-audio.net',1); INSERT INTO customers VALUES (3,'Sams','Danielle','Wise Trucking','489 Main St., #11','Hartford','CT','06103','203-955-4263','203-955-9532','danielle@wise.com',1); INSERT INTO customers VALUES (4,'Elias','Juan','Rose Garden Inn','55 Condor Dr.','Dallas','TX','75252','214-907-3344','214-907-3188','jelias@rosegardeninnn.com',2); INSERT INTO customers VALUES (5,'Foss','Betty','Foss Photography','446 Lincoln Ave.','Philadelphia','PA','19144','215-367-7746','215-543-9800','bfoss15@yahoo.com',3); INSERT INTO customers VALUES (6,'Schaub','Greg','Pampered Pets','716 Heritage Ave., #4A','Phoenix','AZ','85023','602-617-7321','602-617-7321','pamperedpets@msn.com',3); INSERT INTO customers VALUES (7,'Wiersbicki','Joseph','Key Locksmith','122 83rd Ave.','Brooklyn','NY','11220','718-445-8452','718-445-8799','joe@keylocksmith.com',1); INSERT INTO customers VALUES (8,'Ayers','Jack','Park View Insurance','2 Curtis Lane','Topeka','KS','66604','785-707-4120',NULL,'jayers@parkview.com',3); INSERT INTO customers VALUES (9,'Clay','Dorothy','Kenser Corp.','57623 A St.','Sacramento','CA','95821','916-672-8700','916-672-8753','dorothy.clay@kenser.com',1); INSERT INTO customers VALUES (10,'Haagensen','Dave','Dave''s Tree Service','874 Lafayette Rd.','Cleveland','OH','44124','216-578-2347',NULL,'chopchop@excite.com',1); COMMIT;

-- ORDERS and ITEMS INSERT INTO orders VALUES (1,1,'18-JUN-99','18-JUN-99','30-JUN-99','F'); INSERT INTO items VALUES (1,1,3,1); INSERT INTO items VALUES (1,2,2,1); INSERT INTO items VALUES (1,3,5,1); COMMIT;

INSERT INTO orders VALUES (2,2,'18-JUN-99',NULL,NULL,'B'); INSERT INTO items VALUES (2,1,1,2); INSERT INTO items VALUES (2,2,4,2); INSERT INTO items VALUES (2,3,5,2); COMMIT;

INSERT INTO orders VALUES (3,3,'18-JUN-99','18-JUN-99','21-JUN-99','F'); INSERT INTO items VALUES (3,1,3,1); COMMIT;

INSERT INTO orders VALUES (4,4,'19-JUN-99','21-JUN-99','21-JUN-99','F'); INSERT INTO items VALUES (4,1,5,1); COMMIT;

INSERT INTO orders VALUES (5,5,'19-JUN-99','19-JUN-99','28-JUN-99','F'); INSERT INTO items VALUES (5,1,2,1); INSERT INTO items VALUES (5,2,3,1); COMMIT;

INSERT INTO orders VALUES (6,6,'19-JUN-99','19-JUN-99',NULL,'F'); INSERT INTO items VALUES (6,1,4,1); COMMIT;

INSERT INTO orders VALUES (7,7,'19-JUN-99',NULL,NULL,'B'); INSERT INTO items VALUES (7,1,1,1); COMMIT;

INSERT INTO orders VALUES (8,8,'20-JUN-99','20-JUN-99','20-JUN-99','F'); INSERT INTO items VALUES (8,1,5,10); COMMIT;

INSERT INTO orders VALUES (9,9,'21-JUN-99',NULL,NULL,'B'); INSERT INTO items VALUES (9,1,1,2); INSERT INTO items VALUES (9,2,4,2); INSERT INTO items VALUES (9,3,5,2); COMMIT;

INSERT INTO orders VALUES (10,2,'21-JUN-99','22-JUN-99','22-JUN-99','F'); INSERT INTO items VALUES (10,1,2,1); INSERT INTO items VALUES (10,2,3,1); COMMIT;

INSERT INTO orders VALUES (11,4,'22-JUN-99','22-JUN-99',NULL,'F'); INSERT INTO items VALUES (11,1,3,2); INSERT INTO items VALUES (11,2,2,2); INSERT INTO items VALUES (11,3,5,2); COMMIT;

INSERT INTO orders VALUES (12,7,'22-JUN-99','23-JUN-99','30-JUN-99','F'); INSERT INTO items VALUES (12,1,4,1); COMMIT;

INSERT INTO orders VALUES (13,4,'22-JUN-99',NULL,NULL,'B'); INSERT INTO items VALUES (13,1,5,1); COMMIT;

INSERT INTO orders VALUES (14,1,'23-JUN-99','25-JUN-99',NULL,'F'); INSERT INTO items VALUES (14,1,2,1); COMMIT;