Forum Stats

  • 3,826,581 Users
  • 2,260,667 Discussions
  • 7,897,008 Comments

Discussions

create trigger if insert, delete and update for the following code.

2944337
2944337 Member Posts: 2
edited May 6, 2015 4:01AM in SQL & PL/SQL

create table CUSTOMER

(

CUST_ID number(10) primary key,

LAST varchar2(20),

FIRST varchar2(20),

MI varchar2(20),

ADDRESS varchar2(50),

CITY varchar2(20),

STATE varchar2(20),

ZIP VARCHAR2(20),

D_PHONE VARCHAR2(20),

E_PHONE VARCHAR2(20)

);

create table CUST_ORDER

(

ORDER_ID number(10) primary key,

ORDER_DATE date,

PAY_METHOD varchar2(20),

CUST_ID number(10),

ORDER_SOURCE_ID number(10)

);

create table ORDER_SOURCE

(

ORDER_SOURCE_ID number(10) primary key,

SOURCE_DESC varchar2(30)

);

create table ITEM

(

ITEM_ID number(10) primary key,

ITEM_DESC varchar2(50),

CATEGORY_ID  number(10)

);

create table CATEGORY

(

CATEGORY_ID number(10) primary key,

CATEGORY_DESC varchar2(50)

);

create table INVENTORY

(

INV_ID number(10) primary key,

ITEM_ID number(10),

ITEM_SIZE varchar2(10),

COLOR varchar2(20),

PRICE decimal(5,2),

QOH number(10)

);

create table SHIPMENT

(

SHIP_ID number(10) primary key ,

DATE_EXPECTED date

);

create table SHIP_LINE

(

SHIP_ID number(10) ,

INV_ID number(10),

SHIP_QUANTITY number(10),

DATE_RECD date

);

create table ORDER_LINE

(

ORDER_ID number(10),

INV_ID number(10),

ORDER_QUANTITY number(10)

);

create table COLOR

(

COLOR varchar2(20) primary key

);

ALTER TABLE CUST_ORDER

ADD CONSTRAINT customer_pk

FOREIGN KEY(CUST_ID)

REFERENCES CUSTOMER(CUST_ID);

ALTER TABLE INVENTORY

ADD CONSTRAINT item_pk

FOREIGN KEY (ITEM_ID)

REFERENCES ITEM(ITEM_ID);

ALTER TABLE SHIP_LINE

ADD CONSTRAINT inventory_pk

FOREIGN KEY (INV_ID)

REFERENCES INVENTORY(INV_ID);

ALTER TABLE SHIP_LINE

ADD CONSTRAINT ship_id_pk

FOREIGN KEY (SHIP_ID)

REFERENCES SHIPMENT(SHIP_ID);

ALTER TABLE ORDER_LINE

ADD CONSTRAINT order_id_pk

FOREIGN KEY (ORDER_ID)

REFERENCES CUST_ORDER(ORDER_ID);

ALTER TABLE ORDER_LINE

ADD CONSTRAINT inventory1_pk

FOREIGN KEY (INV_ID)

REFERENCES INVENTORY(INV_ID);

INSERT INTO CUSTOMER

VALUES (107,'Harris','Puala','E','1156 Jefferson Ave','Charleston','IL','61920','2175812223','2173480022');

INSERT INTO CUSTOMER

VALUES (232,'Edward','Mich','M','4204 Grant Street','Mattoon','IL','61938','2172355537','2173482322');

INSERT INTO CUSTOMER

VALUES (133,'Carcia','Maria','H','2211 Oak Lane','Champaign','IL','61821','2173334589','2172448973');

INSERT INTO CUSTOMER

VALUES (154,'Miller','Reggie','NULL','699 Pruto St','Urbana','IL','61801','2175816645','2172337654');

INSERT INTO CUSTOMER

VALUES (179,'Woods','Tigerlet','NULL','987 Durham Road','Tilton','IL','61833','2173350045','2175452376');

INSERT INTO CUST_ORDER VALUES (1057,TO_DATE('5/29/2001','MM/DD/YYYY'),'CC',107,2);

INSERT INTO CUST_ORDER VALUES (1058,TO_DATE('5/29/2001','MM/DD/YYYY'),'CC',232,6);

INSERT INTO CUST_ORDER VALUES (1059,TO_DATE('6/10/2001','MM/DD/YYYY'),'CHECK',133,2);

INSERT INTO CUST_ORDER VALUES (1060,TO_DATE('6/21/2001','MM/DD/YYYY'),'CC',154,3);

INSERT INTO CUST_ORDER VALUES (1061,TO_DATE('6/30/2001','MM/DD/YYYY'),'CC',179,6);

INSERT INTO CUST_ORDER VALUES (1062,TO_DATE('7/1/2001','MM/DD/YYYY'),'CC',179,3);

INSERT INTO ORDER_SOURCE VALUES (1,'Winter 2011’);

INSERT INTO ORDER_SOURCE VALUES (2,'Spring 2012’);

INSERT INTO ORDER_SOURCE VALUES (3,'Summer 2012’);

INSERT INTO ORDER_SOURCE VALUES (4,'Outdoor 2012’);

INSERT INTO ORDER_SOURCE VALUES (5,'Children 2012’);

INSERT INTO ORDER_SOURCE VALUES (6,'Web site’);

INSERT INTO ITEM VALUES (894, 'Women''s hiking shorts', 1);

INSERT INTO ITEM VALUES (897, 'Women''s fleece pullover', 1);

INSERT INTO ITEM VALUES (995, 'Children''s beachcomber sandals', 2);

INSERT INTO ITEM VALUES (559,'Men''s expedition parka',3);

INSERT INTO ITEM VALUES (786,'3-season tent',4);

INSERT INTO CATEGORY VALUES(1, 'Women''s clothing’);

INSERT INTO CATEGORY VALUES(2, 'Children''s clothing’);

INSERT INTO CATEGORY VALUES(3, 'Men''s clothing’);

INSERT INTO CATEGORY VALUES(4, 'Outdoor gear’);

INSERT INTO INVENTORY VALUES (11668,786, NULL, 'SKY BLUE’, 259.99,16);

INSERT INTO INVENTORY VALUES (11669,786, NULL, 'LIGHT GREY’,259.99,12);

INSERT INTO INVENTORY VALUES (11775, 894, ’S’,'KHAKI’,29.95,150);

INSERT INTO INVENTORY VALUES (11776,894,’M’,'KHAKI’, 29,147);

INSERT INTO INVENTORY VALUES (11777, 894,’L’,'KHAKI’,29.95,0);

INSERT INTO INVENTORY VALUES (11778, 894,’S’,'NAVY’, 29.95, 139);

INSERT INTO INVENTORY VALUES (11779, 894,’M’, 'NAVY’, 29.95, 137);

INSERT INTO INVENTORY VALUES (11780, 894,’L’,'NAVY’, 29.95, 115);

INSERT INTO INVENTORY VALUES (11795, 897,’S’,'EGGPLANT’, 59.95, 135);

INSERT INTO INVENTORY VALUES (11796, 897,’M’,'EGGPLANT’,’59.95’, 168);

INSERT INTO INVENTORY VALUES (11797, 897,’L’, 'EGGPLANT’, 59.95, 187);

INSERT INTO INVENTORY VALUES (11798, 897, ’S’,'ROYAL’, 59,0);

INSERT INTO INVENTORY VALUES (11799, 897, 'M’, 'ROYAL’, 59.95,124);

INSERT INTO INVENTORY VALUES (11800, 897,’L’,'ROYAL’, 59.95, 112);

INSERT INTO INVENTORY VALUES (11820, 995, 10,'TURQUOISE’, 15.99, 121);

INSERT INTO INVENTORY VALUES (11821, 995,11, 'TURQUOISE’, 15.99, 111);

INSERT INTO INVENTORY VALUES (11822, 995, 12,'TURQUOISE’, 15.99, 113);

INSERT INTO INVENTORY VALUES (11823, 995,1, 'TURQUOISE’, 15.99, 121);

INSERT INTO INVENTORY VALUES (11824, 995,10,'BRIGHT PINK’,15.99,148);

INSERT INTO INVENTORY VALUES (11825, 995,11,'BRIGHT PINK’,15.99,137);

INSERT INTO INVENTORY VALUES (11826, 995, 12,'BRIGHT PINK’, 15.99, 134);

INSERT INTO INVENTORY VALUES (11827, 995,1,'BRIGHT PINK', 15.99,123);

INSERT INTO INVENTORY VALUES (11845, 559,’S’,'SPRUCE’, 199.95, 114);

INSERT INTO INVENTORY VALUES (11846, 559,’M’, 'SPRUCE’, 199.95,17);

INSERT INTO INVENTORY VALUES (11847, 559,’L’,'SPRUCE’, 209.95,0);

INSERT INTO INVENTORY VALUES (11848, 559,’XL’,'SPRUCE’,209.95,12);

INSERT INTO SHIPMENT VALUES (211,TO_DATE(’09/15/2011’,’MM/DD/YYYY’));

INSERT INTO SHIPMENT VALUES (212,TO_DATE(’11/15/2011’,’MM/DD/YYYY’));

INSERT INTO SHIPMENT VALUES (213,TO_DATE('06/25/2011','MM/DD/YYYY’));

INSERT INTO SHIPMENT VALUES (214,TO_DATE('06/25/2011','MM/DD/YYYY’));

INSERT INTO SHIPMENT VALUES (215,TO_DATE('08/15/2011','MM/DD/YYYY’));

INSERT INTO SHIP_LINE VALUES (211, 11668,25,TO_DATE(’09/10/2011’,’MM/DD/YYYY’));

INSERT INTO SHIP_LINE VALUES (211, 11669,25,TO_DATE(’09/10/2011’,’MM/DD/YYYY’));

INSERT INTO SHIP_LINE VALUES (212, 11669,25,NULL);

INSERT INTO SHIP_LINE VALUES (213, 11777,200,NULL);

INSERT INTO SHIP_LINE VALUES (213, 11778,200,NULL);

INSERT INTO SHIP_LINE VALUES (213, 11779,200,NULL);

INSERT INTO SHIP_LINE VALUES (214, 11798,100,TO_DATE(’08/15/2011’,’MM/DD/YYYY’));

INSERT INTO SHIP_LINE VALUES (214, 11799,100,TO_DATE(’08/25/2011’,’MM/DD/YYYY’));

INSERT INTO SHIP_LINE VALUES (215, 11845,50,TO_DATE(’08/15/2011’,’MM/DD/YYYY’));

INSERT INTO SHIP_LINE VALUES (215, 11846,100,TO_DATE(’08/15/2011’,’MM/DD/YYYY’));

INSERT INTO SHIP_LINE VALUES (215, 11847,100,TO_DATE(’08/15/2011’,’MM/DD/YYYY’));

INSERT INTO ORDER_LINE VALUES (1057,11668,1);

INSERT INTO ORDER_LINE VALUES (1057,11800,2);

INSERT INTO ORDER_LINE VALUES (1058,11824,1);

INSERT INTO ORDER_LINE VALUES (1059,11846,1);

INSERT INTO ORDER_LINE VALUES (1060,11798,2);

INSERT INTO ORDER_LINE VALUES (1061,11779,1);

INSERT INTO ORDER_LINE VALUES (1061,11780,1);

INSERT INTO ORDER_LINE VALUES (1062,11799,1);

INSERT INTO ORDER_LINE VALUES (1062,11669,3);

INSERT INTO COLOR VALUES ('SKY BLUE’);

INSERT INTO COLOR VALUES ('LIGHT GREY’);

INSERT INTO COLOR VALUES ('KHAKI’);

INSERT INTO COLOR VALUES ('NAVY’);

INSERT INTO COLOR VALUES ('ROYAL’);

INSERT INTO COLOR VALUES ('EGGPLANT’);

INSERT INTO COLOR VALUES ('BLUE’);

INSERT INTO COLOR VALUES ('RED’);

INSERT INTO COLOR VALUES ('SPRUCE’);

INSERT INTO COLOR VALUES ('TURQUOISE’);

INSERT INTO COLOR VALUES ('BRIGHT PINK’);

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    edited May 6, 2015 4:01AM

    So what have you tried and what problem are you having?  Or are you just asking people to do your (home)work for you? (Which people will not do here)

    Please read the FAQ:

    and post details about the issue you are having, and show the code you have tried to write yourself to solve the issue.

This discussion has been closed.