· 7 years ago · Oct 04, 2018, 11:48 AM
1USE eshop;
2
3DROP TABLE IF EXISTS produktregister;
4
5CREATE TABLE produktregister
6(
7 id INT PRIMARY KEY NOT NULL,
8 produktkod VARCHAR(100) NOT NULL,
9 namn VARCHAR(100) NOT NULL,
10 beskrivning VARCHAR(100) NOT NULL,
11 pris INT NOT NULL
12);
13
14DROP TABLE IF EXISTS kundregister;
15
16CREATE TABLE kundregister
17(
18 id INT PRIMARY KEY NOT NULL,
19 namn VARCHAR(100) NOT NULL,
20 adress VARCHAR(100) NOT NULL,
21 telefonnummer VARCHAR(15),
22 email VARCHAR(100)
23);
24
25DROP TABLE IF EXISTS lager;
26
27CREATE TABLE lager
28(
29 id INT PRIMARY KEY NOT NULL,
30 hylla VARCHAR(10) NOT NULL,
31 antal INT NOT NULL
32);
33
34DROP PROCEDURE IF EXISTS showProducts;
35DELIMITER //
36CREATE PROCEDURE showProducts()
37BEGIN
38 SELECT * FROM produktregister;
39END
40//
41DELIMITER ;
42
43DROP PROCEDURE IF EXISTS showCustomers;
44DELIMITER //
45CREATE PROCEDURE showCustomers()
46BEGIN
47 SELECT * FROM kundregister;
48END
49//
50DELIMITER ;
51
52DROP PROCEDURE IF EXISTS showLog;
53DELIMITER //
54CREATE PROCEDURE showLog()
55BEGIN
56 SELECT * FROM CdOffLog;
57END
58//
59DELIMITER ;
60
61DROP PROCEDURE IF EXISTS createProduct;
62DELIMITER //
63CREATE PROCEDURE createProduct(
64 pId INT,
65 pProduktkod VARCHAR(100),
66 pNamn VARCHAR(100),
67 pBeskrivning VARCHAR(100),
68 pPris INT
69)
70BEGIN
71 INSERT INTO produktregister VALUES (pId, pProduktkod, pNamn, pBeskrivning, pPris);
72END
73//
74DELIMITER ;
75
76DROP PROCEDURE IF EXISTS createCustomer;
77DELIMITER //
78CREATE PROCEDURE createCustomer(
79 cId INT,
80 cNamn VARCHAR(100),
81 cAdress VARCHAR(100),
82 cTelefonnummer VARCHAR(15),
83 cEmail VARCHAR(100)
84)
85BEGIN
86 INSERT INTO kundregister VALUES (cId, cNamn, cAdress, cTelefonnummer, cEmail);
87END
88//
89DELIMITER ;
90
91DROP PROCEDURE IF EXISTS showProduct;
92DELIMITER //
93CREATE PROCEDURE showProduct(
94 pId INT
95)
96BEGIN
97 SELECT * FROM produktregister WHERE id = pId;
98END
99//
100DELIMITER ;
101
102DROP PROCEDURE IF EXISTS showCustomer;
103DELIMITER //
104CREATE PROCEDURE showCustomer(
105 cId INT
106)
107BEGIN
108 SELECT * FROM kundregister WHERE id = cId;
109END
110//
111DELIMITER ;
112
113DROP PROCEDURE IF EXISTS editProduct;
114DELIMITER //
115CREATE PROCEDURE editProduct(
116 pId INT,
117 pProduktkod VARCHAR(100),
118 pNamn VARCHAR(100),
119 pBeskrivning VARCHAR(100),
120 pPris INT
121)
122BEGIN
123 UPDATE produktregister SET
124 `produktkod` = pProduktkod,
125 `namn` = pNamn,
126 `beskrivning` = pBeskrivning,
127 `pris` = pPris
128 WHERE
129 `id` = pId;
130END
131//
132DELIMITER ;
133
134DROP PROCEDURE IF EXISTS editCustomer;
135DELIMITER //
136CREATE PROCEDURE editCustomer(
137 cId INT,
138 cNamn VARCHAR(100),
139 cAdress VARCHAR(100),
140 cTelefonnummer VARCHAR(15),
141 cEmail VARCHAR(100)
142)
143BEGIN
144 UPDATE kundregister SET
145 `namn` = cNamn,
146 `adress` = cAdress,
147 `telefonnummer` = cTelefonnummer,
148 `email` = cEmail
149 WHERE
150 `id` = cId;
151END
152//
153DELIMITER ;
154
155DROP PROCEDURE IF EXISTS deleteProduct;
156DELIMITER //
157CREATE PROCEDURE deleteProduct(
158 pId INT
159)
160BEGIN
161 DELETE FROM produktregister WHERE id = pId;
162END
163//
164DELIMITER ;
165
166DROP PROCEDURE IF EXISTS deleteCustomer;
167DELIMITER //
168CREATE PROCEDURE deleteCustomer(
169 cId INT
170)
171BEGIN
172 DELETE FROM kundregister WHERE id = cId;
173END
174//
175DELIMITER ;
176
177DROP TABLE IF EXISTS CdOffLog;
178CREATE TABLE CdOffLog
179(
180 `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
181 `when` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
182 `what` VARCHAR(20),
183 `reason` VARCHAR(30),
184 `namn` VARCHAR(100)
185);
186
187DROP TRIGGER IF EXISTS productCreateTrigger;
188
189CREATE TRIGGER productCreateTrigger
190BEFORE INSERT
191ON produktregister FOR EACH ROW
192 INSERT INTO CdOffLog(`what`, `reason`, `namn`)
193 VALUES ('trigger', 'product create', NEW.namn);
194
195DROP TRIGGER IF EXISTS customerCreateTrigger;
196
197CREATE TRIGGER customerCreateTrigger
198BEFORE INSERT
199ON kundregister FOR EACH ROW
200 INSERT INTO CdOffLog(`what`, `reason`, `namn`)
201 VALUES ('trigger', 'customer create', NEW.namn);
202
203DROP TRIGGER IF EXISTS productDeleteTrigger;
204
205CREATE TRIGGER productDeleteTrigger
206BEFORE DELETE
207ON produktregister FOR EACH ROW
208 INSERT INTO CdOffLog(`what`, `reason`, `namn`)
209 VALUES ('trigger', 'product delete', OLD.namn);
210
211DROP TRIGGER IF EXISTS customerDeleteTrigger;
212
213CREATE TRIGGER customerDeleteTrigger
214BEFORE DELETE
215ON kundregister FOR EACH ROW
216 INSERT INTO CdOffLog(`what`, `reason`, `namn`)
217 VALUES ('trigger', 'customer delete', OLD.namn);
218
219
220SELECT * FROM CdOffLog;