Forum Stats

  • 3,837,795 Users
  • 2,262,300 Discussions
  • 7,900,394 Comments

Discussions

create trigger if insert, delete and update in following code?

2944337
2944337 Member Posts: 2
edited May 6, 2015 4:38AM 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

This discussion has been closed.