· 8 years ago · May 24, 2018, 11:26 AM
1--BAZA: zakupy w sklepie
2--Dane s¹ tabele
3CREATE TABLE produkt (
4 id_produkt INTEGER NOT NULL,
5 nazwa VARCHAR(20) NULL,
6 opis VARCHAR(20) NULL,
7 cena_netto INTEGER NULL,
8 podatek INTEGER NULL,
9 ilosc_sztuk INTEGER NULL,
10 PRIMARY KEY(id_produkt)
11);
12CREATE TABLE klient (
13 id_klient INTEGER NOT NULL,
14 imie VARCHAR(20) NULL,
15 nazwisko VARCHAR(20) NULL,
16 pesel CHAR(11) NULL,
17 telefon VARCHAR(20) NULL,
18 haslo VARCHAR(20) NULL,
19 rabat INTEGER NOT NULL,
20 PRIMARY KEY(id_klient)
21);
22CREATE TABLE zakup (
23 id_zakup INTEGER NOT NULL,
24 id_klient INTEGER NOT NULL,
25 data_zamowienia DATE NULL,
26 PRIMARY KEY(id_zakup)
27);
28CREATE TABLE koszyk (
29 id_produkt INTEGER NOT NULL,
30 id_zakup INTEGER NOT NULL,
31 cena_netto INTEGER NULL,
32 podatek INTEGER NULL,
33 ilosc_sztuk INTEGER NULL,
34 PRIMARY KEY(id_produkt, id_zakup)
35);
36
37SET DATEFORMAT ymd;
38
39--DAWID BIÑKUŒ 246793
40
41
42--W tabeli koszyk kolumna:
43--id_zakup ma byæ kluczem obcym powi¹zanym z kolumn¹ zakup(id_zakup)
44--id_produkt ma byæ kluczem obcym powi¹zanym z kolumn¹ produkt(id_produkt)
45--W tabeli zakup kolumna id_klient ma byæ kluczem obcym powi¹zanym z kolumn¹ klient(id_klient)
46--Utwórz opisane klucze obce. U¿yj polecenia ALTER TABLE.
47--Rozwi¹zanie [3 pkt]
48
49ALTER TABLE koszyk ADD CONSTRAINT fk_koszyk_zakup FOREIGN KEY(id_zakup) REFERENCES zakup(id_zakup);
50GO
51ALTER TABLE koszyk ADD CONSTRAINT fk_koszyk_produkt FOREIGN KEY(id_produkt) REFERENCES produkt(id_produkt);
52GO
53ALTER TABLE zakup ADD CONSTRAINT fk_zakup_klient FOREIGN KEY(id_klient) REFERENCES klient(id_klient);
54GO
55
56--test
57SELECT * FROM koszyk
58GO
59SELECT * FROM zakup
60GO
61
62--Napisaæ wyzwalacz uniemo¿liwiaj¹cy zmiany nazwiska klienta.
63--Wszystkie inne dane klienta mo¿na modyfikowaæ.
64--Rozwi¹zanie [3 pkt]
65
66IF EXISTS(SELECT * FROM sys.triggers WHERE name='zabron_zmiany_nazwiska')
67DROP TRIGGER zabron_zmiany_nazwiska
68GO
69
70CREATE TRIGGER zabron_zmiany_nazwiska ON klient
71AFTER UPDATE
72AS
73BEGIN
74 DECLARE kursor CURSOR LOCAL FOR SELECT nazwisko, id_klient FROM inserted
75 OPEN kursor
76 DECLARE @id_klient INT, @nazwisko VARCHAR(20)
77 FETCH NEXT FROM kursor INTO @nazwisko, @id_klient
78 WHILE @@FETCH_STATUS=0
79 BEGIN
80 IF UPDATE (nazwisko)
81 BEGIN
82 RAISERROR('Zmiana nazwiska jest zabroniona!',1,2)
83 ROLLBACK
84 END
85 FETCH NEXT FROM kursor INTO @nazwisko, @id_klient
86 END
87 CLOSE kursor
88 DEALLOCATE kursor
89END;
90GO
91
92--test, dodajemy nowego klienta
93INSERT INTO klient (id_klient, imie, nazwisko, pesel, telefon, haslo, rabat) VALUES
94(1,'Jan','Abacki','12345678901','512345123','superhaslo',20);
95INSERT INTO klient (id_klient, imie, nazwisko, pesel, telefon, haslo, rabat) VALUES
96(2, 'Marek','Babacki','23456789012','224423125','superhaslo1',0);
97--probujemy zmienic jego nazwisko
98UPDATE klient SET nazwisko='Babacki' WHERE id_klient=1;
99--probujemy zmienic inna wartosc
100UPDATE klient SET imie='Marcin' WHERE id_klient=1;
101
102
103--Napisaæ wyzwalacz, który podczas robienia zakupów bêdzie aktualizowa³
104--stan magazynu (kolumna ilosc_stuk w tabeli produkt ma byæ uaktualniona, nie przejmujemy siê
105--sytuacj¹, ¿e w magazynie jest za ma³o sztuk)
106--Rozwi¹zanie [3 pkt]
107
108IF EXISTS(SELECT * FROM sys.triggers WHERE name='aktualizuj_stan')
109DROP TRIGGER aktualizuj_stan
110GO
111
112CREATE TRIGGER aktualizuj_stan ON koszyk
113AFTER INSERT
114AS
115BEGIN
116 DECLARE kursor CURSOR LOCAL FOR SELECT ilosc_sztuk, id_produkt FROM inserted
117 OPEN kursor
118 DECLARE @ilosc_sztuk INT, @id_produkt INT
119 FETCH NEXT FROM kursor INTO @ilosc_sztuk, @id_produkt
120 WHILE @@FETCH_STATUS=0
121 BEGIN
122 IF (@ilosc_sztuk>0)
123 BEGIN
124 UPDATE produkt SET ilosc_sztuk=ilosc_sztuk - @ilosc_sztuk WHERE id_produkt=@id_produkt
125 END
126 FETCH NEXT FROM kursor INTO @ilosc_sztuk, @id_produkt
127 END
128 CLOSE kursor
129 DEALLOCATE kursor
130END
131GO
132
133--test, dodamy produkt
134INSERT INTO produkt (id_produkt, nazwa, opis, cena_netto, podatek, ilosc_sztuk) VALUES
135(1,'pierwszy', 'test', 250, 23, 200),
136(2,'ciastko', 'smaczne', 1, 23, 1000);
137GO
138--sprawdzamy ilosc sztuk w tabeli produkt
139SELECT * FROM produkt
140GO
141--dodajemy zamowienie
142INSERT INTO zakup (id_zakup,id_klient,data_zamowienia) VALUES
143(1,1,'2018-01-01'),
144(2,1,'2018-02-02');
145--dodajemy do koszyka zarowno oba produkty
146INSERT INTO koszyk (id_produkt,id_zakup,cena_netto,podatek,ilosc_sztuk) VALUES
147(1,1,250,23,1),
148(1,2,250,23,4),
149(2,1,1,23,51);
150--teraz wartosci w tabeli produkt powinny ulec zmianie
151SELECT * FROM produkt
152GO
153
154--Napisaæ funkcjê spr_pesel(sprawdzany_pesel) sprawdzaj¹c¹ poprawnoœæ numeru pesel i zwracaj¹c¹
155--wartoœæ logiczn¹ true dla poprawnego i false dla niepoprawnego numeru pesel
156--Napisaæ wyzwalacz uniemo¿liwiaj¹cy zmodyfikowanie danych klienta na takie dane, które zawieraj¹
157--niepoprawny numer pesel
158/* Na wszelki wypadek przypomnienie:
159Rozwa¿my PESEL osoby urodzonej 8 lipca 1902 roku, p³ci ¿eñskiej (parzysta koñcówka numeru z serii – 0362). Czyli mamy wówczas 0207080362. Teraz kolejne cyfry nale¿y przemno¿yæ przez odpowiednie wagi i dodaæ do siebie.
1600*1 + 2*3 + 0*7 + 7*9 + 0*1 + 8*3 + 0*7 + 3*9 + 6*1 + 2*3 = 0 + 6 + 0 + 63 + 0 + 24 + 0 + 27 + 6 + 6 = 132
161Wynik dzielimy modulo przez 10.
162132 mod 10 = 2
163A nastêpnie odejmujemy od 10
16410 - 2 = 8.
165I wynik dzielimy znów modulo 10
1668 mod 10 = 8
167Cyfra kontrolna to 8, zatem nasz prawid³owy numer PESEL to: 02070803628
168*/
169--Rozwi¹zanie [3 pkt]
170
171IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='spr_pesel' AND ROUTINE_TYPE='FUNCTION')
172DROP FUNCTION spr_pesel
173GO
174
175CREATE FUNCTION spr_pesel (@pesel VARCHAR(11))
176RETURNS BIT
177AS
178BEGIN
179 IF (ISNUMERIC(@pesel)=0)
180 BEGIN
181 RETURN 0
182 END
183
184 DECLARE @wagi AS TABLE (pozycja TINYINT IDENTITY(1,1), waga TINYINT)
185 INSERT INTO @wagi VALUES (1),(3),(7),(9),(1),(3),(7),(9),(1),(3),(1)
186 IF (SELECT SUM(CONVERT(TINYINT,SUBSTRING(@pesel,pozycja,1)*waga))%10 FROM @wagi) = 0
187 RETURN 1
188 RETURN 0
189END
190GO
191
192--testujemy zgodnosc funkcji
193--powinno zwrocic 1
194SELECT dbo.spr_pesel('02070803628')
195--tutaj 0
196SELECT dbo.spr_pesel('02070803624')
197--tutaj tez 0
198SELECT dbo.spr_pesel('pesel')
199
200IF EXISTS(SELECT * FROM sys.triggers WHERE name='sprawdz_pesel')
201DROP TRIGGER sprawdz_pesel
202GO
203
204CREATE TRIGGER sprawdz_pesel ON klient
205FOR UPDATE
206AS
207BEGIN
208 DECLARE kursor CURSOR LOCAL FOR SELECT pesel FROM inserted
209 OPEN kursor
210 DECLARE @pesel VARCHAR(11)
211 FETCH NEXT FROM kursor INTO @pesel
212 BEGIN
213 IF dbo.spr_pesel(@pesel) = 0
214 BEGIN
215 RAISERROR('nie prawid³owy numer pesel!', 1,2)
216 ROLLBACK
217 END
218 FETCH NEXT FROM kursor INTO @pesel
219 END
220 CLOSE kursor
221 DEALLOCATE kursor
222END
223GO
224
225--test
226--to powinno dzialac
227UPDATE klient SET pesel='02070803628' WHERE id_klient=1;
228--a to nie
229UPDATE klient SET pesel='pesel' WHERE id_klient=1;
230
231SELECT * FROM klient;
232
233--Do tabeli klient dodaj kolumnê usuniety przyjmuj¹c¹ 1 gdy klient jest usuniêty, a
234--0 gdy nie jest
235--Napisaæ wyzwalacz zabraniaj¹cy usuwaniu klientów, a w zamian ustawiaj¹cy wartoœæ
236--kolumny usuniety na 1 dla usuwanego klienta
237--Rozwi¹zanie [3 pkt]
238
239ALTER TABLE klient ADD czy_usuniety BIT DEFAULT 0
240UPDATE klient SET czy_usuniety = 0;
241GO
242SELECT * FROM klient
243GO
244
245IF EXISTS(SELECT * FROM sys.triggers where name='usun_klienta')
246DROP TRIGGER usun_klienta
247GO
248
249CREATE TRIGGER usun_klienta ON klient
250INSTEAD OF DELETE
251AS
252BEGIN
253 DECLARE kursor CURSOR LOCAL FOR SELECT id_klient FROM deleted
254 OPEN kursor
255 DECLARE @id_klient INT
256 FETCH NEXT FROM kursor INTO @id_klient
257 WHILE @@FETCH_STATUS=0
258 BEGIN
259 UPDATE klient SET czy_usuniety=1 WHERE id_klient = @id_klient
260 FETCH NEXT FROM kursor INTO @id_klient
261 END
262 CLOSE kursor
263 DEALLOCATE kursor
264END
265GO
266
267--prostsza wersja, niewykorzystujaca kursora :)
268CREATE TRIGGER usun_klienta ON klient
269INSTEAD OF DELETE
270AS
271BEGIN
272 UPDATE klient SET czy_usuniety=1 WHERE id_klient IN (SELECT id_klient FROM deleted)
273END
274GO
275
276
277--sprawdzenie, usuwamy klienta o id 1
278DELETE FROM klient
279WHERE id_klient BETWEEN 1 AND 2;
280GO
281
282SELECT * FROM klient;
283
284--------------------------
285
286IF EXISTS(SELECT * from sys.objects where name='dodawanie_osoby' and type='TR') DROP TRIGGER dodawanie_osoby
287IF EXISTS(SELECT * from sys.objects where name='pesel_checksum' and type='FN') DROP FUNCTION pesel_checksum
288IF EXISTS(SELECT * from sys.objects where name='name_fix' and type='FN') DROP FUNCTION name_fix
289IF EXISTS(SELECT * from sys.objects where name='osoba_historia' and type='U') DROP TABLE osoba_historia
290IF EXISTS(SELECT * from sys.objects where name='osoba' and type='U') DROP TABLE osoba
291GO
292CREATE TABLE osoba (
293 id_osoba INT IDENTITY PRIMARY KEY,
294 imie VARCHAR(50) NOT NULL,
295 nazwisko VARCHAR(50) NOT NULL,
296 pesel CHAR(11) NOT NULL CHECK(len(pesel)=11) UNIQUE ,
297 data_ur DATE NOT NULL CHECK(data_ur<getdate()),
298 pensja DECIMAL(15,4) NOT NULL
299 )
300GO
301
302
303CREATE TABLE osoba_historia (
304 id_osoba INT IDENTITY PRIMARY KEY,
305 imie VARCHAR(50) NOT NULL,
306 nazwisko VARCHAR(50) NOT NULL,
307 pesel CHAR(11) NOT NULL CHECK(len(pesel)=11),
308 data_ur DATE NOT NULL CHECK(data_ur<getdate()),
309 pensja DECIMAL(15,4) NOT NULL,
310 data DATE NOT NULL DEFAULT getdate(),
311 operacja CHAR(1) NOT NULL CHECK(operacja='D' OR operacja='U')
312 )
313GO
314
315
316CREATE FUNCTION name_fix (
317 @name VARCHAR(30)
318 ) RETURNS VARCHAR(30)
319 BEGIN
320 DECLARE @index INTEGER
321 DECLARE @fixed VARCHAR(50)
322 SET @index = charindex('-',@name,0)
323 if(@index>0)
324 SET @fixed = (upper(substring(@name,1,1)) + lower(substring(@name,2,@index-1)) + upper(substring(@name,@index+1,1)) + lower(substring(@name,@index+2,50)))
325 ELSE
326 SET @fixed = (upper(substring(@name,1,1)) + lower(substring(@name,2,len(@name)-1)))
327 RETURN @fixed
328 END
329GO
330
331
332CREATE FUNCTION pesel_checksum (
333 @pesel CHAR(11)
334 ) RETURNS integer
335 BEGIN
336 DECLARE @valid INTEGER
337 DECLARE @x1 integer,@x2 integer,@x3 integer,@x4 integer,@x5 integer,@x6 integer,@x7 integer,@x8 integer,@x9 integer,@x10 integer,@x11 integer
338 SET @x1 = CAST(substring(@pesel,1,1) AS INTEGER)
339 SET @x2 = CAST(substring(@pesel,2,1) AS INTEGER)
340 SET @x3 = CAST(substring(@pesel,3,1) AS INTEGER)
341 SET @x4 = CAST(substring(@pesel,4,1) AS INTEGER)
342 SET @x5 = CAST(substring(@pesel,5,1) AS INTEGER)
343 SET @x6 = CAST(substring(@pesel,6,1) AS INTEGER)
344 SET @x7 = CAST(substring(@pesel,7,1) AS INTEGER)
345 SET @x8 = CAST(substring(@pesel,8,1) AS INTEGER)
346 SET @x9 = CAST(substring(@pesel,9,1) AS INTEGER)
347 SET @x10 = CAST(substring(@pesel,10,1) AS INTEGER)
348 SET @x11 = CAST(substring(@pesel,11,1) AS INTEGER)
349
350 DECLARE @sum INTEGER
351 SET @sum = 1*@x1 + 3*@x2 + 7*@x3 + 9*@x4 + 1*@x5 + 3*@x6 + 7*@x7 + 9*@x8 + 1*@x9 + 3*@x10 + 1*@x11
352
353 if(@sum%10=0)
354 RETURN 0
355 ELSE
356 RETURN 1
357 RETURN 1
358 END
359GO
360
361CREATE TRIGGER dodawanie_osoby ON osoba
362 AFTER INSERT,UPDATE
363 AS
364 BEGIN
365 IF EXISTS(SELECT * from inserted where datediff(YY,data_ur,getdate())<18)
366 BEGIN
367 RAISERROR('Osoby w bazie muszą być pełnoletnie',15,0)
368 ROLLBACK
369 END
370 IF EXISTS(SELECT * from inserted where pesel LIKE '%[^0-9]%')
371 BEGIN
372 RAISERROR('Pesel musi składać się z samych cyfr',15,0)
373 ROLLBACK
374 END
375 IF EXISTS(SELECT * from inserted where dbo.pesel_checksum(pesel)=1)
376 BEGIN
377 RAISERROR('Nieprawidłowa suma kontrolna peselu',15,0)
378 ROLLBACK
379 END
380 IF EXISTS(SELECT * from inserted where pensja<1111)
381 BEGIN
382 RAISERROR('Pensja musi być niemniejsza niż 1111zl',15,0)
383 ROLLBACK
384 END
385 UPDATE osoba
386 SET imie=dbo.name_fix(imie), nazwisko=dbo.name_fix(nazwisko)
387 WHERE (id_osoba in (SELECT id_osoba from inserted))
388 IF EXISTS(SELECT * from inserted)
389 INSERT INTO osoba_historia(imie, nazwisko, pesel, data_ur, pensja, operacja) SELECT imie,nazwisko,pesel,data_ur,pensja,'U' from deleted
390 END
391go
392
393IF EXISTS(SELECT * from sys.objects where name='usuwanie_osoby' and type='TR') DROP TRIGGER usuwanie_osoby
394GO
395CREATE TRIGGER usuwanie_osoby ON osoba
396 AFTER DELETE
397 AS
398 BEGIN
399 INSERT INTO osoba_historia(imie, nazwisko, pesel, data_ur, pensja, operacja) SELECT imie,nazwisko,pesel,data_ur,pensja,'D' from deleted
400 END
401GO
402
403-- testowanie
404
405INSERT INTO osoba(imie, nazwisko, pesel, data_ur, pensja) VALUES
406 ('kamil','MalInowaski','96071808331','1990-01-01',1340),
407 ('kamIlzzzzzz','MalInowaaaaaaaskio','86072808331','1969-04-20',1340),
408 ('KAMIL','AAAAAAAMalInowaskio','95071908331','1410-07-06',15000),
409 ('kamilh','malInowaskio','98071608331','1994-05-09',5555),
410 ('kamildfrsefSEsef','awda-MalInowaskio','99071508331','1914-05-11',11000)
411go
412
413INSERT INTO osoba(imie, nazwisko, pesel, data_ur, pensja) VALUES
414 ('kamil','MalInowaski','35465256823','1990-01-01',130)
415
416SELECT * FROM osoba
417SELECT * FROM osoba_historia
418
419UPDATE osoba
420set pensja=100000
421where id_osoba=2
422
423DELETE FROM osoba
424DELETE FROM osoba_historia
425
426------------------
427
428IF EXISTS(SELECT * from sys.objects where name='brakujacy_towar' and type='U') DROP TABLE brakujacy_towar
429IF EXISTS(SELECT * from sys.objects where name='koszyk' and type='U') DROP TABLE koszyk
430IF EXISTS(SELECT * from sys.objects where name='zakup' and type='U') DROP TABLE zakup
431IF EXISTS(SELECT * from sys.objects where name='towar' and type='U') DROP TABLE towar
432IF EXISTS(SELECT * from sys.objects where name='klient' and type='U') DROP TABLE klient
433
434CREATE TABLE klient (
435 id_klient INT IDENTITY PRIMARY KEY,
436 imie VARCHAR(50) NOT NULL CHECK(len(imie)>2),
437 nazwisko VARCHAR(50) NOT NULL CHECK(len(nazwisko)>2),
438 )
439GO
440
441CREATE TABLE towar (
442 id_towar INT IDENTITY PRIMARY KEY,
443 nazwa VARCHAR(50) NOT NULL CHECK(len(nazwa)>2),
444 opis VARCHAR(250) NOT NULL CHECK(len(opis)>2),
445 ilosc_sztuk INT NOT NULL CHECK(ilosc_sztuk>=0),
446 cena_netto DECIMAL(15,4) NOT NULL CHECK(cena_netto>0),
447 podatek DECIMAL(15,4) NOT NULL CHECK(podatek>=0)
448 )
449GO
450
451CREATE TABLE zakup (
452 id_zakup INT IDENTITY PRIMARY KEY,
453 id_klient INT NOT NULL FOREIGN KEY REFERENCES klient(id_klient) ON DELETE CASCADE,
454 data_zakupu DATE NOT NULL CHECK(data_zakupu<getdate())
455 )
456GO
457
458CREATE TABLE koszyk (
459 id_zakup INT NOT NULL FOREIGN KEY REFERENCES zakup(id_zakup) ON DELETE CASCADE,
460 id_towar INT NOT NULL FOREIGN KEY REFERENCES towar(id_towar) ON DELETE CASCADE,
461 ilosc INT NOT NULL CHECK(ilosc>0),
462 cena_netto DECIMAL(15,4) NOT NULL CHECK(cena_netto>0),
463 podatek DECIMAL(15,4) NOT NULL CHECK(podatek>=0)
464 CONSTRAINT PK_koszyk PRIMARY KEY (id_zakup,id_towar)
465 )
466GO
467
468INSERT INTO klient(imie,nazwisko) VALUES ('Jan','Nowak'),('Marian','Kowalski'),('Gra¿yna','Adamczyk')
469GO
470
471INSERT INTO towar(nazwa,opis,ilosc_sztuk,cena_netto,podatek) VALUES
472 ('towar 1','to jest towar 1',1000,25.5,0.22),
473 ('towar 2','a to jest towar 1',250,600,0.22),
474 ('towar 3','to jest drogi towar',50,10500.50,0.3),
475 ('towar 4','to jest tani towar',10000,5.60,0.1),
476 ('towar 5','to jest towar zwolniony z podatku',1000,10,0)
477GO
478
479INSERT INTO zakup(id_klient,data_zakupu) VALUES (1,'2017-04-17'),(1,'2016-12-20'),(2,'2017-05-26'),(3,'2016-11-01')
480GO
481
482INSERT INTO koszyk(id_zakup,id_towar,ilosc,cena_netto,podatek) VALUES
483 (1,2,3,3*600,3*600*0.22),
484 (1,4,20,20*5.6,20*5.6*0.1),
485 (2,5,100,100*10,100*10*0),
486 (3,1,50,50*25.5,50*25.5*0.22),
487 (3,2,5,5*600,5*600*0.22),
488 (3,3,1,1*10500.5,1*10500.5*0.3),
489 (4,3,5,5*10500.5,5*10500.5*0.3)
490GO
491
492CREATE TABLE brakujacy_towar (
493 id_brakujacy_towar INT IDENTITY PRIMARY KEY,
494 ilosc_sztuk INT NOT NULL CHECK(ilosc_sztuk>0),
495 id_towar INT NOT NULL FOREIGN KEY REFERENCES towar(id_towar),
496 id_zakup INT NOT NULL FOREIGN KEY REFERENCES zakup(id_zakup)
497 )
498GO
499
500IF EXISTS(SELECT * from sys.objects where name='aktualizowanie_magazynu' and type='TR') DROP TRIGGER aktualizowanie_magazynu
501GO
502CREATE TRIGGER aktualizowanie_magazynu ON koszyk
503 AFTER INSERT
504 AS
505 BEGIN
506 declare kursor_aktualizacja CURSOR
507 FOR SELECT id_zakup,id_towar,ilosc,cena_netto,podatek from inserted
508 declare @id_zakup INT,@id_towar INT,@ilosc INT,@cena_netto DECIMAL(15,4),@podatek DECIMAL(15,4)
509 OPEN kursor_aktualizacja
510 FETCH NEXT FROM kursor_aktualizacja INTO @id_zakup,@id_towar,@ilosc,@cena_netto,@podatek
511 WHILE @@FETCH_STATUS=0
512 BEGIN
513 IF((select ilosc_sztuk from towar where id_towar=@id_towar)>=@ilosc)
514 UPDATE towar
515 set ilosc_sztuk=ilosc_sztuk-@ilosc
516 where id_towar=@id_towar
517 ELSE
518 BEGIN
519 INSERT INTO brakujacy_towar(ilosc_sztuk,id_towar,id_zakup) values(@ilosc-(select ilosc_sztuk from towar where id_towar=@id_towar),@id_towar,@id_zakup)
520 UPDATE towar
521 set ilosc_sztuk=0
522 where id_towar=@id_towar
523 END
524 FETCH NEXT FROM kursor_aktualizacja INTO @id_zakup,@id_towar,@ilosc,@cena_netto,@podatek
525 END
526 CLOSE kursor_aktualizacja
527 DEALLOCATE kursor_aktualizacja
528 END
529GO
530
531insert into koszyk(id_zakup,id_towar,ilosc,cena_netto,podatek) values
532 (2,1,50,50*25.5,50*25.5*0.22),
533 (4,1,50,50*25.5,50*25.5*0.22)
534GO
535
536insert into koszyk(id_zakup,id_towar,ilosc,cena_netto,podatek) values
537 (1,1,1000,1000*25.5,1000*25.5*0.22)
538GO
539
540select * from towar
541select * from koszyk
542select * from brakujacy_towar
543select * from zakup
544GO
545
546IF EXISTS(SELECT * from sys.objects where name='klient_statystyki' and type='V') DROP VIEW klient_statystyki
547GO
548CREATE VIEW klient_statystyki
549AS
550 SELECT id_klient,imie,nazwisko,ilosc_zakupow = (SELECT COUNT(id_zakup) FROM zakup z where z.id_klient=k.id_klient),
551 suma_wydanej_kasy_brutto = COALESCE((SELECT SUM(ko.cena_netto+ko.podatek) from koszyk ko inner join zakup z ON ko.id_zakup=z.id_zakup where z.id_klient=k.id_klient),0)
552 FROM klient k
553GO
554
555select * from klient
556select * from klient_statystyki
557select * from towar
558select * from koszyk
559select * from brakujacy_towar
560select * from zakup
561GO
562
563IF EXISTS(SELECT * from sys.objects where name='klient_statystyki_do_klient' and type='TR') DROP TRIGGER klient_statystyki_do_klient
564GO
565CREATE TRIGGER klient_statystyki_do_klient ON klient_statystyki
566 INSTEAD OF INSERT,UPDATE,DELETE
567 AS
568 BEGIN
569 IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT id_klient FROM inserted)
570 delete from klient
571 where id_klient IN (SELECT id_klient FROM deleted)
572 ELSE IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
573 insert into klient(imie,nazwisko) (SELECT imie,nazwisko FROM inserted)
574 ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
575 BEGIN
576 declare kursor_klient CURSOR
577 FOR SELECT id_klient,imie,nazwisko from inserted
578 declare @id_klient INT,@imie VARCHAR(50),@nazwisko VARCHAR(50)
579 OPEN kursor_klient
580 FETCH NEXT FROM kursor_klient INTO @id_klient,@imie,@nazwisko
581 WHILE @@FETCH_STATUS=0
582 BEGIN
583 UPDATE klient
584 set imie=@imie,nazwisko=@nazwisko
585 where id_klient=@id_klient
586 FETCH NEXT FROM kursor_klient INTO @id_klient,@imie,@nazwisko
587 END
588 END
589 END
590GO
591
592INSERT INTO klient_statystyki(imie,nazwisko) VALUES ('Kamil','Malinowski'),('_IMIE','_NAZWISKO')
593
594DELETE FROM klient_statystyki
595WHERE id_klient>=4
596
597UPDATE klient_statystyki
598SET imie='NoweImie'
599WHERE id_klient=2
600
601GO
602
603select * from klient
604go