it2051229 Sales Co Triggers and Procedures

1. Create a Trigger, for the SaleCo2 database, that will support:

1. Upon adding (Inserting) a new Invoice into the Invoice table (disregard the need to update the Line table with invoice line item information, as the FKs structure is not in place.

2. Your trigger should automatically Update the Customer Balance in the Customer table by adding the new invoice amount onto the Customer Balance attribute.

3. Clarification: No hard coding of the inserted invoice amount in the trigger. SQL should access the inserted amount in the Invoice table and add it to the customer balance attribute in the Customer table. The use of NEW. and OLD. will be of great use. Also, you must only insert an invoice for an existing customer, otherwise it will not work.

HINT: May be done in one SQL query with sub-queries, OR using separate queries and maybe a variable.

2. Create a Stored Procedure, for the SaleCo2 database, that will:

1. Delete an Invoice from the Invoice table by accepting the Invoice # as a procedure parameter when you CALL the procedure to be executed in the SQL window/tab within PhP MySQL..

2. Delete all associated rows for this Invoice in the Line table.

3. Reduce the Customer balance in the Customer table by the amount of the deleted Invoice.

3. Submit in a Word document:

1. The Trigger (#1) and Procedure (#2) script/code.

2. SQL Print View screen evidence that the trigger/procedure exists.

3. SQL Print View screen evidence of what was expected to happen, did in fact happen. (i.e. use the select command to show before/after raw data as appropriate based on the respective situations).

Trigger and Procedure creation/editing reminders:

When initially creating a trigger or procedure in the SQL window the slash / must be placed in the delimiter textbox replacing the semi colon ';
When editing an existing trigger or procedure the edit window interface does not require you to add the slash '/', as it does this in the background for us.

SQL Script to Recreate Database

/* CH08_SaleCo2.SQL */
/* Script file for ORACLE RDBMS */
/* This script file creates the following tables: */
/* VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE */
/* and loads the default data rows */

Create Database SalesCo2;
Use SalesCo2;

CREATE TABLE VENDOR (
V_CODE INTEGER,
V_NAME VARCHAR(35) NOT NULL,
V_CONTACT VARCHAR(15) NOT NULL,
V_AREACODE CHAR(3) NOT NULL,
V_PHONE CHAR(8) NOT NULL,
V_STATE CHAR(2) NOT NULL,
V_ORDER CHAR(1) NOT NULL,
PRIMARY KEY (V_CODE));

CREATE TABLE PRODUCT (
P_CODE VARCHAR(10) PRIMARY KEY,
P_DESCRIPT VARCHAR(35) NOT NULL,
P_INDATE DATE NOT NULL,
P_QOH Decimal NOT NULL,
P_MIN Decimal NOT NULL,
P_PRICE Decimal(8,2) NOT NULL,
P_DISCOUNT Decimal(4,2) NOT NULL,
V_CODE Decimal);

CREATE TABLE CUSTOMER (
CUS_CODE Decimal PRIMARY KEY,
CUS_LNAME VARCHAR(15) NOT NULL,
CUS_FNAME VARCHAR(15) NOT NULL,
CUS_INITIAL CHAR(1),
CUS_AREACODE CHAR(3),
CUS_PHONE CHAR(8) NOT NULL,
CUS_BALANCE Decimal(9,2));

CREATE TABLE INVOICE (
INV_NUMBER Decimal PRIMARY KEY,
CUS_CODE Decimal NOT NULL,
INV_DATE DATE,
INV_SUBTOTAL Decimal(9,2),
INV_TAX Decimal(9,2),
INV_TOTAL Decimal(9,2));

CREATE TABLE LINE (
INV_NUMBER Decimal NOT NULL,
LINE_NUMBER Decimal(2,0) NOT NULL,
P_CODE VARCHAR(10) NOT NULL,
LINE_UNITS Decimal(9,2) NOT NULL,
LINE_PRICE Decimal(9,2) NOT NULL,
LINE_TOTAL Decimal(9,2));

/* VENDOR rows */
INSERT INTO VENDOR VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y');
INSERT INTO VENDOR VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES(21231,'D&E Supply' ,'Singh' ,'615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY','N');
INSERT INTO VENDOR VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA','N');
INSERT INTO VENDOR VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES(24288,'ORDVA, Inc.' ,'Hakford' ,'615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES(25443,'B&K, Inc.' ,'Smith' ,'904','227-0093','FL','N');
INSERT INTO VENDOR VALUES(25501,'Damal Supplies' ,'Smythe' ,'615','890-3529','TN','N');
INSERT INTO VENDOR VALUES(25595,'Rubicon Systems' ,'Orton' ,'904','456-0092','FL','Y');

/* PRODUCT rows */
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'03-NOV-2011', 8, 5,109.99,0.00,25595);
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'13-DEC-2011', 32, 15, 14.99,0.05,21344);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'13-NOV-2011', 18, 12, 17.49,0.00,21344);
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'15-JAN-2012', 15, 8, 39.95,0.00,23119);
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'15-JAN-2012', 23, 5, 43.99,0.00,23119);
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade' ,'30-DEC-2011', 8, 5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade' ,'24-DEC-2011', 6, 5, 99.87,0.05,24288);
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.' ,'20-JAN-2012', 12, 5, 38.95,0.05,25595);
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer' ,'20-JAN-2012', 23, 10, 9.95,0.10,21225);
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.' ,'02-JAN-2012', 8, 5, 14.40,0.05,NULL );
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'15-DEC-2011', 43, 20, 4.99,0.00,21344);
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'07-FEB-2012', 11, 5,256.99,0.05,24288);
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'20-FEB-2011',188, 75, 5.87,0.00,NULL );
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25' ,'01-MAR-2012',172, 75, 6.99,0.00,21225);
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50' ,'24-FEB-2012',237,100, 8.45,0.00,21231);
INSERT INTO PRODUCT VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2012', 18, 5,119.95,0.10,25595);

/* CUSTOMER rows */
INSERT INTO CUSTOMER VALUES(10010,'Ramas' ,'Alfred','A' ,'615','844-2573',0);
INSERT INTO CUSTOMER VALUES(10011,'Dunne' ,'Leona' ,'K' ,'713','894-1238',0);
INSERT INTO CUSTOMER VALUES(10012,'Smith' ,'Kathy' ,'W' ,'615','894-2285',345.86);
INSERT INTO CUSTOMER VALUES(10013,'Olowski' ,'Paul' ,'F' ,'615','894-2180',536.75);
INSERT INTO CUSTOMER VALUES(10014,'Orlando' ,'Myron' ,NULL,'615','222-1672',0);
INSERT INTO CUSTOMER VALUES(10015,'O''Brian','Amy' ,'B' ,'713','442-3381',0);
INSERT INTO CUSTOMER VALUES(10016,'Brown' ,'James' ,'G' ,'615','297-1228',221.19);
INSERT INTO CUSTOMER VALUES(10017,'Williams','George',NULL,'615','290-2556',768.93);
INSERT INTO CUSTOMER VALUES(10018,'Farriss' ,'Anne' ,'G' ,'713','382-7185',216.55);
INSERT INTO CUSTOMER VALUES(10019,'Smith' ,'Olette','K' ,'615','297-3809',0);

/* INVOICE rows */
INSERT INTO INVOICE VALUES(1001,10014,'16-JAN-2012', 24.90, 1.99, 26.89);
INSERT INTO INVOICE VALUES(1002,10011,'16-JAN-2012', 9.98, 0.80, 10.78);
INSERT INTO INVOICE VALUES(1003,10012,'16-JAN-2012', 153.85, 12.31, 166.16);
INSERT INTO INVOICE VALUES(1004,10011,'17-JAN-2012', 34.97, 2.80, 37.77);
INSERT INTO INVOICE VALUES(1005,10018,'17-JAN-2012', 70.44, 5.64, 76.08);
INSERT INTO INVOICE VALUES(1006,10014,'17-JAN-2012', 397.83, 31.83, 429.66);
INSERT INTO INVOICE VALUES(1007,10015,'17-JAN-2012', 34.97, 2.80, 37.77);
INSERT INTO INVOICE VALUES(1008,10011,'17-JAN-2012', 399.15, 31.93, 431.08);

/* LINE rows */
INSERT INTO LINE VALUES(1001,1,'13-Q2/P2',1, 14.99, 14.99);
INSERT INTO LINE VALUES(1001,2,'23109-HB',1, 9.95, 9.95);
INSERT INTO LINE VALUES(1002,1,'54778-2T',2, 4.99, 9.98);
INSERT INTO LINE VALUES(1003,1,'2238/QPD',1, 38.95, 38.95);
INSERT INTO LINE VALUES(1003,2,'1546-QQ2',1, 39.95, 39.95);
INSERT INTO LINE VALUES(1003,3,'13-Q2/P2',5, 14.99, 74.95);
INSERT INTO LINE VALUES(1004,1,'54778-2T',3, 4.99, 14.97);
INSERT INTO LINE VALUES(1004,2,'23109-HB',2, 9.95, 19.90);
INSERT INTO LINE VALUES(1005,1,'PVC23DRT',12, 5.87, 70.44);
INSERT INTO LINE VALUES(1006,1,'SM-18277',3, 6.99, 20.97);
INSERT INTO LINE VALUES(1006,2,'2232/QTY',1, 109.92, 109.92);
INSERT INTO LINE VALUES(1006,3,'23109-HB',1, 9.95, 9.95);
INSERT INTO LINE VALUES(1006,4,'89-WRE-Q',1, 256.99, 256.99);
INSERT INTO LINE VALUES(1007,1,'13-Q2/P2',2, 14.99, 29.98);
INSERT INTO LINE VALUES(1007,2,'54778-2T',1, 4.99, 4.99);
INSERT INTO LINE VALUES(1008,1,'PVC23DRT',5, 5.87, 29.35);
INSERT INTO LINE VALUES(1008,2,'WR3/TT3' ,3, 119.95, 359.85);
INSERT INTO LINE VALUES(1008,3,'23109-HB',1, 9.95, 9.95);

Academic Honesty!

It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.

Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.