OK, vazno je ne uzbudjivati se :-)
Evo na primer ovakva tabela, u kojoj vodimo stanje na lageru za neke artikl.
Code:
IF Object_ID('StanjeNaLageru') IS NOT NULL DROP TABLE StanjeNaLageru
GO
CREATE TABLE StanjeNaLageru
(
ArtiklID int NOT NULL
, Promena int NOT NULL -- kolicina koja se dodaje ili uzima, uzimanje je negativno, dodavanje pozitivno
, DatumPromene datetime NOT NULL
, StanjeNaLageru int NOT NULL -- stanje na kraju ove transakcije
, PrethodnoStanje int NULL
, DatumPrethodnePromene datetime NULL
, CONSTRAINT PK_StanjeNaLageru PRIMARY KEY (ArtiklID, DatumPromene)
, CONSTRAINT CHK_StanjeNaLageru CHECK (StanjeNaLageru >= 0)
, CONSTRAINT CHK_StanjeNaLageru_ValidChange CHECK (StanjeNaLageru = COALESCE(PrethodnoStanje,0) + Promena)
, CONSTRAINT CHK_StanjeNaLageru_ValidPrevDate CHECK (DatumPromene > DatumPrethodnePromene OR DatumPrethodnePromene IS NULL)
, CONSTRAINT UNQ_StanjeNaLageru_WithQuantity UNIQUE (ArtiklID, DatumPromene, StanjeNaLageru)
, CONSTRAINT FK_StanjeNaLageru_AutoRef FOREIGN KEY (ArtiklID, DatumPrethodnePromene, PrethodnoStanje)
REFERENCES StanjeNaLageru (ArtiklID, DatumPromene , StanjeNaLageru)
)
GO
Necemo obajsanjavati svaki constraint, prihvatite ovo za sada zdravo za gotovo. Primer je prakticno prepisan iz knajige
https://sqlwithmanoj.wordpress.com/tag/alex-kuznetsov/ samo su nazivi prevedeni na srpski.
Elem, pokazacemo da su promene tipa UPDATE i DELETE prilicno teske za izvodjenje i mogu se odraditi samo ako tazco znamo sta radimo. Ovo je veoma vazno, jer ne moze neko nehotice jednim DELETE da nam unisti lager listu. Ili jednim UPDATE da nam pokvari sve zapise.
Probajmo da unesemo nekoliko redova:
Code:
-- Prvi unos za ArtiklID = 1
INSERT INTO StanjeNaLageru (ArtiklID , Promena , DatumPromene , StanjeNaLageru, PrethodnoStanje, DatumPrethodnePromene )
VALUES ( 1, 3, '20110101', 3, NULL, NULL)
;
SELECT * FROM StanjeNaLageru;
ArtiklID Promena DatumPromene StanjeNaLageru PrethodnoStanje DatumPrethodnePromene
----------- ----------- ----------------------- ----------- ----------- -----------------------
1 3 2011-01-01 00:00:00.000 3 NULL NULL
-- Prva promena za ArtiklID = 1, prepisujemo vrednosti (StanjeNaLageru, DatumPromene) u (PrethodnoStanje, DatumPrethodnePromene)
INSERT INTO StanjeNaLageru (ArtiklID , Promena , DatumPromene , StanjeNaLageru, PrethodnoStanje, DatumPrethodnePromene )
VALUES ( 1, 2, '20110115', 5, 3, '20110101')
;
SELECT * FROM StanjeNaLageru;
ArtiklID Promena DatumPromene StanjeNaLageru PrethodnoStanje DatumPrethodnePromene
----------- ----------- ----------------------- ----------- ----------- -----------------------
1 3 2011-01-01 00:00:00.000 3 NULL NULL
1 2 2011-01-15 00:00:00.000 5 3 2011-01-01 00:00:00.000
(2 row(s) affected)
-- Jos jedna promena za ArtiklID = 1, prepisujemo vrednosti (StanjeNaLageru, DatumPromene) u (PrethodnoStanje, DatumPrethodnePromene)
INSERT INTO StanjeNaLageru (ArtiklID , Promena , DatumPromene , StanjeNaLageru, PrethodnoStanje, DatumPrethodnePromene )
VALUES ( 1, -1, '20110125', 4, 5, '20110115' )
;
SELECT * FROM StanjeNaLageru;
ArtiklID Promena DatumPromene StanjeNaLageru PrethodnoStanje DatumPrethodnePromene
----------- ----------- ----------------------- ----------- ----------- -----------------------
1 3 2011-01-01 00:00:00.000 3 NULL NULL
1 2 2011-01-15 00:00:00.000 5 3 2011-01-01 00:00:00.000
1 -1 2011-01-25 00:00:00.000 4 5 2011-01-15 00:00:00.000
(3 row(s) affected)
Da pokusamo da uradimo neke nedozvoljene stvari. Prvo, da pokusamo da skinem sa lagera vise nego sto imamo.
Code:
-- Pokusaj da skinemo vise nego sto ima na lageru:
INSERT INTO StanjeNaLageru (ArtiklID , Promena , DatumPromene , StanjeNaLageru, PrethodnoStanje, DatumPrethodnePromene )
VALUES ( 1, -5, '20110131', -1, 4, '20110115' )
;
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_StanjeNaLageru". The conflict occurred in database "Test", table "dbo.StanjeNaLageru", column 'StanjeNaLageru'.
The statement has been terminated.
--ne moze negativno stanje :-)
-- Da se pretvaramo da ima vise nego sto ima:
INSERT INTO StanjeNaLageru (ArtiklID , Promena , DatumPromene , StanjeNaLageru, PrethodnoStanje, DatumPrethodnePromene )
VALUES ( 1, -5, '20110131', 3, 8, '20110115' )
;
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_StanjeNaLageru_AutoRef". The conflict occurred in database "Test", table "dbo.StanjeNaLageru".
The statement has been terminated.
-- ne mozemo da unesemo PrethodnoStanje ako vrednost nije tacna. To nam brani FK koji gleda u prethodni red
Pokusajmo da obrisemo neki red, koji nije poslednji:
Code:
DELETE StanjeNaLageru
WHERE ArtiklID = 1
AND DatumPromene = '2011-01-01 00:00:00.000'
;
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_StanjeNaLageru_AutoRef". The conflict occurred in database "Test", table "dbo.StanjeNaLageru".
The statement has been terminated.
-- ne moze, jer je taj red roditelj za red posle njega. Brisanje ne bi proslo ni za jedan red osim poslednjeg
Da pokusamo masovni UPDATE, da svedemo sve na 0:
Code:
UPDATE StanjeNaLageru
SET StanjeNaLageru = 0
;
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CHK_StanjeNaLageru_ValidChange". The conflict occurred in database "Test", table "dbo.StanjeNaLageru".
The statement has been terminated.
-- ne moze :-)
Rekli smo da se poslednji red moze obrisati. Moze se i azurirati poslednji red, ali nije bas lako. Pokusajmo da promenimo kolicinu u poslednjoj promeni
Code:
-- Pokusajmo da promenimo kolicinu u poslednjem redu:
UPDATE StanjeNaLageru
SET Promena = -3
WHERE ArtiklID = 1 AND DatumPromene = '2011-01-25 00:00:00.000'
;
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CHK_StanjeNaLageru_ValidChange". The conflict occurred in database "Test", table "dbo.StanjeNaLageru".
The statement has been terminated.
Nije proslo smo pod UPDATE stavili samo kolicinu. Nasa tabela je konstruisana tako da se mora uneti i novo stanje, koje naravno mora da se slaze sa prethodnim stanjem (novo stanje = staro stanje plus poslednja promena). Pokazimo da tako moze.
Code:
-- moglo bi ovako: prethodno stanje je bilo 5, ako zelimo da oduzmemo 3 onda ce novo stanjue biti 2
-- pa moramo i taj podatak da azuriramo
UPDATE StanjeNaLageru
SET Promena = -3, StanjeNaLageru = 2
WHERE ArtiklID = 1 AND DatumPromene = '2011-01-25 00:00:00.000'
;
(1 row(s) affected)
--Sada je proslo.
Znaci, UPDATE poslednjeg reda je moguc samo ako zaista znamo sta radimo. Na ostalim redovima UPDATE nije moguce uraditi, tacka.
To nam daje vremena da svaku transakciju prokontrolisemo i onda nekim mehanizmom i poslednju promenun nekako 'zakljucamo'
Posto ja nisma knjigovodja dozvolicvu sebi da unosim fiktivne promene, gede je Promena = 0 i datum je za sekundu veci nego poslednji datum.
Code:
-- Ovo je stanje pre dodavanja neutralne promene:
SELECT * FROM StanjeNaLageru
ArtiklID Promena DatumPromene StanjeNaLageru PrethodnoStanje DatumPrethodnePromene
----------- ----------- ----------------------- -------------- --------------- -----------------------
1 3 2011-01-01 00:00:00.000 3 NULL NULL
1 2 2011-01-15 00:00:00.000 5 3 2011-01-01 00:00:00.000
1 -3 2011-01-25 00:00:00.000 2 5 2011-01-15 00:00:00.000
(3 row(s) affected)
-- dodajmo neutralnu promenu
INSERT INTO StanjeNaLageru (ArtiklID , Promena , DatumPromene , StanjeNaLageru, PrethodnoStanje, DatumPrethodnePromene )
VALUES ( 1, 0, '20110125 00:00:01', 2, 2, '2011-01-25 00:00:00.000' )
;
--Evo sta smo dobili
SELECT * FROM StanjeNaLageru;
ArtiklID Promena DatumPromene StanjeNaLageru PrethodnoStanje DatumPrethodnePromene
----------- ----------- ----------------------- -------------- --------------- -----------------------
1 3 2011-01-01 00:00:00.000 3 NULL NULL
1 2 2011-01-15 00:00:00.000 5 3 2011-01-01 00:00:00.000
1 -3 2011-01-25 00:00:00.000 2 5 2011-01-15 00:00:00.000
1 0 2011-01-25 00:00:01.000 2 2 2011-01-25 00:00:00.000
(4 row(s) affected)
Sada je stvarna poslednja promena zakljucana. Niti je mozemo obrisati niti je mozemo promeniti. Nasa tabela se ponasa veoma priblizno rucno vodjenom inventaru na kartici artikla. na rucno vodjenoj kartici ne mozete ubacivati nove redove imadju postojecih. Ne mozete ni ovde. Ne mozete tek tako menjati ranije redove, u stvari nikako ih ne mozete menjati. Ne mozete ni ovde. Korekcije su moguce na poslednjem redu, sve dok ga ne zakljucamo. To omogucuje klasican knjigovodstveni proces:
unos promene => kontrola => zakljucivanje transakcije (zakljucivanje, ne zakljucavanje ;-)
Ako mi bas treba promena na nekom od unesenih redova, onda moram da obrisem sve redove koji su dosli posle toga (za taj artikl samo, naravno) i da se rekonstruise cela istorija. To nije lako i tesko ce se desiti slucajno ili nehotice.
Ovo sto imamo mnogo bolje nego prosta tabela oblika (ArtiklID, DatumPromene, Kolicina). Marko svakako zna da napise kveri koja izracunava stanje i jos jednu funkciju koja ne dozvoljava da stanje ide ispod nule. Da onemoguci unos redova izmedju vec postojecih, trebace mu triger. A trigeri onbicno ne rade kad se radi bulk insert, iliti DTS kako se to danas kaze. Znaci, i pored trigera, moguce su greske kao lokomotiva velika kada podaci ulaze putem DTS-a, sto se desava u praksi. A DTS obicno upumpava poveliki broj redova.
Posto se ovde pri svakom unosu tarzi da korisnik sam izracuna novo stanje i prepise prethodno, drasticno je smanjena sansa da se unese pogresan podatak, pa makar bio i logican. Sta mislite koliko je tesko da se unese 24382 umesto 51655? Polozaj cifara na numerickoj tastauri je idealan za ovakve greske. 1 se nalazi ispod 4 i malo pomeranje prsta ce otkucati 4 umesto 1 pa 24382 postaje 21382. 5 i 2 se mogu lako zameniti u brzom kucanju, 5 i 8 takodje, 6 i 3 ili 6 i 9.
Proslog meseca moj jedan poznanik je platio naknadno 3,115 dolara porez od prosle godine. Knjigovodja koji radi porez je otkucao 24753 umesto 21753. Rezultat - uplaceno je 3000 dolara manje za porez nego sto je trebalo. Kad su oni napravili promenu posle 10 meseci, trazili su svojih 3000 nazad. I jos 115 za kamatu. A sve zato sto je prst pogodio 4 umesto 1. I zato sto imamo program za obracun poreza, niko vise ne kontrolise unos, trazi se sto pre to bolje. Sto je brzo to je i kuso. A i kosta.
Da se gresi, to je ljudski, da se stvarno zabrlja potreban je kompjuter - iz Marfijevog zakona
:-)