[ nadavesela @ 25.03.2011. 08:45 ] @
Ako imam tabelu u kojoj pratim istoriju promena nad datim pocetnim zapisom (pr. PocetnoId=1151). Pri samom upisu dovoljno mi je da upisem DatumOd, kao datum od kad vazi promena.

Id DatumOd StatusId PrethId PocetnoId computed DatumDo
1151 06/11/2009 0 ----- 1151 15/12/2010
1745 15/12/2010 0 1151 1151 16/12/2010
1759 16/12/2010 1 1745 1151 -----------

Ako bih zelela na nivou reda da imam DatumDo, dali nekako preko Computed Columns mogu dobiti DatumDo, ili bih i to trebala ciniti u momentu promene, i raditi fizicki zapis DatumDo u redu koji se updetuje, pre nego sto insertujem novi red.



[Ovu poruku je menjao nadavesela dana 25.03.2011. u 16:31 GMT+1]
[ Zidar @ 25.03.2011. 14:40 ] @
Na dobrom si putu otprilike, pomoci cemo ti da se ne zapetljas. Ovako.

Imas recimo neke entitete koji kroz vreme menjaju status. Recimo zbog jednostavnosti, da je dozvoljen prelazak iz bilo kog statusa u bilo koji status. jedino sto je bitno je da zabelezimo da se status promenio u oderdjenom trenutku za odredjeni entitet.

Intuitivno resenje bi bilo ovo:
Code:

GO
CREATE TABLE Status_History
(
ID int NOT NULL    -- ID je id nekog entiteta za koga pratimo promenu statusa
, StatusID int NOT NULL
, DatumOd DateTime NOT NULL
, PRIMARY KEY (ID, statusID, DatumOd)
)
GO

-- unesimo po nekoliko promena statuso
-- za entitete ciji su ID = 1 i ID = 2
INSERT INTO Status_History (ID, StatusID, DatumOD)
SELECT 1, 0, '20110117' UNION
SELECT 1, 1, '20110118' UNION
SELECT 1, 2, '20110119' UNION
SELECT 1, 0, '20110120' UNION
SELECT 2, 1, '20110117' UNION
SELECT 2, 0, '20110118' UNION
SELECT 2, 2, '20110119' UNION
SELECT 2, 1, '20110120' 
;

-- da vidimo sta smo dobili:
SELECT * FROm Status_History
ORDER BY Id, DatumOD
;

         ID    StatusID DatumOd
----------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000
          1           1 2011-01-18 00:00:00.000
          1           2 2011-01-19 00:00:00.000
          1           0 2011-01-20 00:00:00.000
          2           1 2011-01-17 00:00:00.000
          2           0 2011-01-18 00:00:00.000
          2           2 2011-01-19 00:00:00.000
          2           1 2011-01-20 00:00:00.000

(8 row(s) affected
/* 
entitet ciji je ID = 1 je poceo u statusu 0 pa isao u 1, pa 2 pa se opet vratio u 0
entitet ciji je ID = 2 je poceo u statusu 1 pa isao u 0, pa 2 pa se opet vratio u 1
*/

(8 row(s) affected)


Ideja je zdrava, ovako nekako se ocekuje da se prati istorija - svaka promena statusa se upise sa datumom kad se desila i to je to. Bar tako bi bilo na papiru ili u Excelu.

"Lako" je napisati kveri koji nam pokazuje DatumOd-datumdo, pa nema potrebe za calculated kolonama:
Code:

SELECT 
    H1.ID, H1.StatusID, H1.DatumOd
    , DatumDo = (
                 SELECt MAX(DatumOd) 
                                FROM Status_History AS H3
                                WHERE H3.ID = H1.ID
                                AND H3.DatumOD > H1.DatumOd
                )                            
FROM Status_History AS H1
ORDER BY H1.ID, H1.datumOD
;

         ID    StatusID DatumOd                 DatumDo
----------- ----------- ----------------------- -----------------------
          1           0 2011-01-17 00:00:00.000 2011-01-20 00:00:00.000
          1           1 2011-01-18 00:00:00.000 2011-01-20 00:00:00.000
          1           2 2011-01-19 00:00:00.000 2011-01-20 00:00:00.000
          1           0 2011-01-20 00:00:00.000 NULL
          2           1 2011-01-17 00:00:00.000 2011-01-20 00:00:00.000
          2           0 2011-01-18 00:00:00.000 2011-01-20 00:00:00.000
          2           2 2011-01-19 00:00:00.000 2011-01-20 00:00:00.000
          2           1 2011-01-20 00:00:00.000 NULL

(8 row(s) affected)


U sustini, prikazano resenje je naivno. Zasto je ovo naivno resenje? Deluje privlacno. Pamti se minimalan broj podataka, DatumDo mozemo da izracunamo, sve izgleda OK. Medjutim, nema nicega sto nam garantuje da ce se podaci uneti korektno. Sve ce lepo da radi dok mi pedantno i bez greske unosimo podatke. Ali ako pogresimo - zlo i naopako.

Uneo sam po nekoliko promena za ID=1 i ID = 2. Ko mi brani da sad upisem nesto ovako:

Code:

-- Try to insert incorrect data
INSERT INTO Status_History (ID, StatusID, DatumOD)
SELECT 1, 3, '20110117' 
-- no problem, INSERT went through => (1 row(s) affected)


Dodao sam jos jednu promenu za ID=1 na dan 17 Januar 2011. Vec imam jednu promenu za taj dan, mozda i mogu dve promene da budu u jednom danu, ali zasto bih tu promenu upisao tek sada, nekoliko nedelja kasnije? Sta ce da mi vrati upit datumOd - DatumDo:

Code:

         ID    StatusID DatumOd                 DatumDo
----------- ----------- ----------------------- -----------------------
          1           0 2011-01-17 00:00:00.000 2011-01-20 00:00:00.000
          1           3 2011-01-17 00:00:00.000 2011-01-20 00:00:00.000
          1           1 2011-01-18 00:00:00.000 2011-01-20 00:00:00.000
          1           2 2011-01-19 00:00:00.000 2011-01-20 00:00:00.000
          1           0 2011-01-20 00:00:00.000 NULL
          2           1 2011-01-17 00:00:00.000 2011-01-20 00:00:00.000
          2           0 2011-01-18 00:00:00.000 2011-01-20 00:00:00.000
          2           2 2011-01-19 00:00:00.000 2011-01-20 00:00:00.000
          2           1 2011-01-20 00:00:00.000 NULL

(9 row(s) affected)


Ooops, ima dve status na dan 17 januar za ID = 1. Ajde brzo da ispravimo ako moze, daj UPDATE. E tu je kvaka. Ako bi evidenciju vodili na papiru, mnogo je manja verovatnoca da bi napravili ovakvu gresku. Kao prvo, nemoguce je udenuti (INSERT) novi red na pozicju ID=1 17 Januar. Drugo, kad pisemo rukom, manja je verovatnoca da cemo pogresno upisati datum, nego kad kucamo po tastaturi ili kliknemo na kalendar. Koliko sam samo puta uneo pogresan mesec ili pogresnu godinu.... :-( Verovatno je ova situacija dovela do vaznog stava iz Marfijevog Zakona "Gresiti je ljudski, ali da se stvarno zabrlja, potreban je kompjuter".

Kako bi zgledalo resenje koje nije naivno, i zahteva jednostavniji kveri za OdDatuma - DoDatuma? Resenje je u konstrukciji tabele. Nije intuitivno i nije jednostavno, ni za razumavanje, ni za implementaciju, ali je moguce.

Da to uradimo u novom postu.


[ nadavesela @ 25.03.2011. 15:18 ] @
Zidar, samo bih napomenula da je PrethodnoId broj reda (Id) onog koji se menja i ciji status postaje 0 kad se promeni, trebalo bi te neaktivne redove ne moci menjati. PocetniId je da bi se pri menjanju sacuvalo i pomocu querija dobilo istorijat promene po redolsedu promene (odnosno po Id koloni), cak nezavisno od datuma, jer potencijalno mogu postojati izmene a da nije bitan datum kad su izvrsene, i do kad vaze; ali kad je bitno, pod uslovom da je izvrsen korektan unos datuma kako dobiti i datumdo sledece izmene.
Id je identity u funkciji Surrogat key i ne moze se ponavljati, odnosno Id je Primery Key

Nisam bas totalni pocetnik kom treba pomoc, vishe mi je cilj diskusija i razmena misljenja bas zbog drugacijeg pogleda na stvari, konkretno vezano za upotrebu Vestackih kljuceva.

[Ovu poruku je menjao nadavesela dana 25.03.2011. u 16:35 GMT+1]

[Ovu poruku je menjao nadavesela dana 25.03.2011. u 16:36 GMT+1]
[ Zidar @ 25.03.2011. 15:51 ] @
Za pocetak, dodajem dve kolone u tabelu:
Code:
DROP TABLE Status_History
;

CREATE TABLE Status_History
(
ID int NOT NULL  -- ID je id nekog entiteta za koga pratimo promenu statusa
, StatusID int NOT NULL
, DatumOd DateTime NOT NULL
-- Dodajem dve kolone, koje mogu imati NULL
, StariStatus int NULL
, StariDatumOD DateTime NULL
, PRIMARY KEY (ID, statusID, DatumOd)
)
GO


Cemu mi one sluze? Da povezem novi red za prethodni. To je za sada jedina promena u tabeli. Da unesemo pocetni status za oba entiteta.
Code:
-- Za prvi red, nemamo StariStatus niti StariDatumOD pa cemo ih ostaviti da budu NULL
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    1,        0,        '20110117' , NULL        , NULL        )
;

INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    2,        1,        '20110117' , NULL        , NULL        )
;

-- Sta smo dobili
SELECT * FROM Status_History
;
         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          2           1 2011-01-17 00:00:00.000        NULL NULL

(2 row(s) affected)


Unesimo prvu ptromenu statusa za oba entiteta:
Code:
-- Prve promene statusa.
-- Pazljivo unosim StariStatus i StariDatumOd
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    1,        1,        '20110118' ,    0        , '20110117' )
;

INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    2,        0,        '20110118' ,    1        , '20110117'        )

-- dobili smo
SELECT * FROM Status_History
ORDER BY ID, DatumOd
;

         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          1           1 2011-01-18 00:00:00.000           0 2011-01-17 00:00:00.000
          2           1 2011-01-17 00:00:00.000        NULL NULL
          2           0 2011-01-18 00:00:00.000           1 2011-01-17 00:00:00.000

(4 row(s) affected)

Ovde treba da zastanemo i dobro pogledamo sat smo dobili. Novi red za svaki entitet sadrzi podatke iz prethodnog reda. Tako znam tacno koji red dolazi iz koga, ko je kome prethodnik i sledbenik. Za sada, to sam postigao tako sto sam svesno i pazljivo uneo podatke da dobijem ono sto mi treba. Da dodamo jos po jean red i da pokusamo da napravimo onaj upit Oddatuma-Dodatuma.
Code:
 INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    1,        2,        '20110119' ,    1        , '20110118' )
;

INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    2,        2,        '20110119' ,    0        , '20110118'        )
;
-- dobili smo
SELECT * FROM Status_History
ORDER BY ID, DatumOd
;
         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          1           1 2011-01-18 00:00:00.000           0 2011-01-17 00:00:00.000
          1           2 2011-01-19 00:00:00.000           1 2011-01-18 00:00:00.000
          2           1 2011-01-17 00:00:00.000        NULL NULL
          2           2 2011-01-18 00:00:00.000           0 2011-01-18 00:00:00.000
          2           0 2011-01-18 00:00:00.000           1 2011-01-17 00:00:00.000

(6 row(s) affected)

Imamo po tri reda, dovoljno za upit. Pokusajmo upit:
Code:
 
-- Upit koji vraca OD - Do
SELECT 
    ID
    , OdDatuma = StariDatumOD
    , DoDatuma = DatumOD
    , [Status] = StariStatus
FROM Status_History
ORDER BY ID, DatumOD
;

         ID OdDatuma                DoDatuma                     Status
----------- ----------------------- ----------------------- -----------
          1 NULL                    2011-01-17 00:00:00.000        NULL
          1 2011-01-17 00:00:00.000 2011-01-18 00:00:00.000           0
          1 2011-01-18 00:00:00.000 2011-01-19 00:00:00.000           1
          2 NULL                    2011-01-17 00:00:00.000        NULL
          2 2011-01-17 00:00:00.000 2011-01-18 00:00:00.000           1
          2 2011-01-18 00:00:00.000 2011-01-19 00:00:00.000           0

(6 row(s) affected)

Primetite da smo upotrebili ORDER BY. Bez ORDER BY redovi ce se pojaviti u nekom proizvolnom, slucajnom i zbunjujucem redosledu.
Sta vidimo iz rezultata? Prvo, oni redovi gde kolne OdDatuma i Status imaju vrednost NULL nam ne govore nista pa ih treba eliminisati. Evo.
Code:

-- eliminisemo NULL redove
SELECT 
    ID
    , OdDatuma = StariDatumOD
    , DoDatuma = DatumOD
    , [Status] = StariStatus
FROM Status_History
WHERE StariDatumOD IS NOT NULL
ORDER BY ID, DatumOD
;
-- dobijemo:
         ID OdDatuma                DoDatuma                     Status
----------- ----------------------- ----------------------- -----------
          1 2011-01-17 00:00:00.000 2011-01-18 00:00:00.000           0
          1 2011-01-18 00:00:00.000 2011-01-19 00:00:00.000           1
          2 2011-01-17 00:00:00.000 2011-01-18 00:00:00.000           1
          2 2011-01-18 00:00:00.000 2011-01-19 00:00:00.000           0

(4 row(s) affected)  

Vrlo lepo, jedino sto nam se ne vidi poslednji status, onaj tekuci. Izracunajmo tekuci status. U svakom redu imam StaridatumOD i StariStatus, osim za one redove koji su poslednji dodati - koji imaju tekuci status. Dakle, red koji cuva tekuci status za konkretni entitet ID jeste onaj koji nema odgovarajuceg para u skupu parova (ID, StariDatumOD, StariStatus)

Code:

-- Ovo nam daje tekuci status 
SELECT     
    H1.ID
    , OdDatuma = H1.DatumOD
    , DoDatuma = NULL
    , [Status] = H1.StatusID
FROM Status_History AS H1
LEFT JOIN Status_History AS H2 ON H1.ID = H2.ID AND H1.DatumOD = H2.StariDatumOD
WHERE H2.StariDatumOD IS NULL
;

         ID OdDatuma                   DoDatuma      Status
----------- ----------------------- ----------- -----------
          1 2011-01-19 00:00:00.000        NULL           2
          2 2011-01-19 00:00:00.000        NULL           2

(2 row(s) affected)


Konacan kupit je unija od redova koji se dobiajju direktno zi tabele i redova koji cuvaju tekuci status. Evo:

Code:

-- Konacan iskaz:
SELECt X.* FROM
(
-- iz tabele direktno dobijamo
SELECT 
    ID
    , OdDatuma = StariDatumOD
    , DoDatuma = DatumOD
    , [Status] = StariStatus
FROM Status_History
WHERE StariDatumOD IS NOT NULL
UNION ALL
SELECT     
    H1.ID
    , OdDatuma = H1.DatumOD
    , DoDatuma = NULL
    , [Status] = H1.StatusID
FROM Status_History AS H1
LEFT JOIN Status_History AS H2 ON H1.ID = H2.ID 
                            AND H1.DatumOD = H2.StariDatumOD
WHERE H2.StariDatumOD IS NULL
) AS X
ORDER BY ID, OdDatuma, DoDatuma
;

         ID OdDatuma                DoDatuma                     Status
----------- ----------------------- ----------------------- -----------
          1 2011-01-17 00:00:00.000 2011-01-18 00:00:00.000           0
          1 2011-01-18 00:00:00.000 2011-01-19 00:00:00.000           1
          1 2011-01-19 00:00:00.000 NULL                              2
          2 2011-01-17 00:00:00.000 2011-01-18 00:00:00.000           1
          2 2011-01-18 00:00:00.000 2011-01-19 00:00:00.000           0
          2 2011-01-19 00:00:00.000 NULL                              2

(6 row(s) affected)


Znaci, ako bi ovako prenosili vrednosti iz reda u red, kveriji bi bili jednostavni i vladao bi red i mir u tabeli. Jedino, kako da nateramo korisnika da bude pazljiv? Ako probate sami da unesete nekoliko redova, videcete da nije lako.

U novom postu pokazacemo kako i to moze.

[ Zidar @ 25.03.2011. 17:07 ] @
Podaci vrede onoliko koliko su tacni i bezbedni. Pokusajmo da obezbedimo tacnost podataka. Videcemo kako iz tacnosti proizlazi i dobar deo bezbednosti.
Code:


DROP TABLE Status_History
GO

CREATE TABLE Status_History
(
ID int NOT NULL  -- ID je id nekog entiteta za koga pratimo promenu statusa
, StatusID int NOT NULL
, DatumOd DateTime NOT NULL
, StariStatus int NULL
, StariDatumOD DateTime NULL
, CONSTRAINT PK_Status_History PRIMARY KEY (ID, DatumOd, StatusID)
, CONSTRAINT ck_Status_History_StariDatumPreNovog CHECK (StariDatumOD < DatumOd)
)
GO


Ako hocemo da nam (ID, StariStatus, StariDatumOD) zavisi od prethodnog reda, mozemo da postavimo FOREIGN KEY, tako da StariStatus i StariDatumOD mora da postoji vec u tabeli da bismo ga dodelili novom redu. Ovo neodoljivo podseca na jedan od nacina prikazivanja hijerarhija u relacionim sistemima. I jeste, svaki red je roditelj prethodnom redu. U 'obicnoj' hijerrahiji jedan red moze imati vise dece, a pokazacemo da ovde jedan red ne treb da ima vise od jednog deteta.
Code:

ALTER TABLE Status_History
ADD CONSTRAINT Status_History_vea_Izmedju_Redova
FOREIGN KEY (ID, StariDatumOD, StariStatus )
REFERENCES Status_History (ID,DatumOd,  StatusID )




Sada tabela nece prihvatiti unos u StariStatus ili StariDatumOd koji ne postoje u nekom od prethodnih redova, a sve za posmatrani entitet.
Unesimo neke podatke, ispravne ili neispravne
Code:

-- Za prvi red, nemamo StariStatus niti StariDatumOD pa cemo ih ostaviti da budu NULL
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    1,        0,        '20110117' , NULL        , NULL        )
;
-- Sve je OK -- (1 row(s) affected)
SELECT * FROM Status_History;

         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL

(1 row(s) affected)



Sta ce se desiti ako pokusamo da unesemo nekakv StariStatus i/ili StariDatumOD?
Code:

-- Pocetni status za entitet ID = 2
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    2,        1,        '20110117' ,   0        , '20110112'        )
;
-- nece moci:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "Status_History_vea_Izmedju_Redova". The conflict occurred in database "DMA", table "dbo.Status_History".
The statement has been terminated.


FOREIGN KEY nas je spasao od loseg unosa. tek da se zna Medjutim , daleko od toga da smo bezbedni. Pogledajte ovo;
Code:

-- medjutim, nepotpuna kombinacija prolazi, a ne bi trebalo:
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    2,        1,        '20110117' ,   0        , NULL        )
;
-- proslo je, a ne bi trebalo (1 row(s) affected)
SELECT * FROM Status_History;

         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          2           1 2011-01-17 00:00:00.000           0 NULL

(2 row(s) affected)

-- eliminsacu red za Id = 2, da nam ne smeta u daljem testiranju. 
-- Treba nam nesto da sprecimo unos 'starih' podataka za prvi red entiteta.

DELETE Status_History
WHERE ID = 2

SELECT * FROM Status_History;

         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL

(1 row(s) affected)


Mozemo reci ovako pravilo: StariStatus i StariDatumOD su ili ona NULL ili oba su NOT NULL. To bi sprecilo anomalije koje smo upravo videli.
Code:

ALTER TABLE Status_History
ADD CONSTRAINT ck_Status_History_StariStatusDatumOD_NULLability
CHECK (
        (StariStatus IS NULL AND StariDatumOD IS NULL)
        OR
        (StariStatus IS NOT NULL AND StariDatumOD IS NOT NULL)
        )


-- Pokusajmo nekompletan unos za StariStatus StariDatumOD, 
-- Prokusajmo isti INSERT koji je bio prosao, a nije trebao
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    2,        1,        '20110117' ,   0        , NULL        )
;
-- Ovaj put nije proslo:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_Status_History_StariStatusDatumOD_NULLability". The conflict occurred in database "DMA", table "dbo.Status_History".
The statement has been terminated.


-- Ovo naravno prolazi 
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    2,        1,        '20110117' ,   NULL        , NULL        )
;
-- (1 row(s) affected)

SELECT * FROM Status_History;

         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          2           1 2011-01-17 00:00:00.000        NULL NULL

(2 row(s) affected)


Pokusajmo jos nekoliko redova:
Code:

-- DatumOd = Staridatum, ocekujem gresku
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD,    StariStatus, StariDatumOD)
VALUES    (    1,    1        ,    '2011-01-17',    0,        '2011-01-17')
;
-- Naravno da ne moze:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_Status_History_StariDatumPreNovog". The conflict occurred in database "DMA", table "dbo.Status_History".
The statement has been terminated.

-- DatumOd < Staridatum, StariStatus izpravan
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD        ,    StariStatus, StariDatumOD)
VALUES    (    1,    1        ,    '2011-01-18',        0,        '2011-01-17')
;
-- naravno da prolazi

SELECT * FROM Status_History ORDER BY ID, DatumOD
;

         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          1           1 2011-01-18 00:00:00.000           0 2011-01-17 00:00:00.000
          2           1 2011-01-17 00:00:00.000        NULL NULL

(3 row(s) affected)


FOREIGN KEY smo vec testirali, ne dozvoljava da se unese nepostojeca kombinacija (ID, StariStatus, StariDatumOD). Medjutim, jos uvek mogu da unesem pogresan podatak. Evo:

Code:

-- datumi su OK, Stari status postoji, ali nije dobar, 
-- trebalo bi da bude StariStatus = 1
-- jer  poslednji uneti red za ID=1 ima StatusID = 1
-- Ovo prolazi, a ne bi trebalo:
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD        ,    StariStatus, StariDatumOD)
VALUES    (    1,    3        ,    '2011-01-19',        0,        '2011-01-17')
;
-- proslo je, a ne bi trebalo -- (1 row(s) affected)

SELECT * FROM Status_History ORDER BY ID, DatumOD

         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          1           1 2011-01-18 00:00:00.000           0 2011-01-17 00:00:00.000
          1           3 2011-01-19 00:00:00.000           0 2011-01-17 00:00:00.000
          2           1 2011-01-17 00:00:00.000        NULL NULL

(4 row(s) affected)



Za ID =1, StaiStaus ima vrednost 0 za dva reda, a ne bi trebalo. Ako malo bolje razmislimo, kombinacija (ID, StariStatus, StariDatumOD) treba da je UNIQUE. U tom slucaju, svaki red moze da ima najvise jednog sledbenika.Ovo je srce resenja. Vec znamo da svaki red ima tacno jednog roditelja - FK se brine o tome, kao u hijerarhiji. Ako istovremeo svaki red moze da ima najvise jednog sledbenika i svaki red ima tacno jednog prethodnika, ond gar antujemo tacan redosled i neprekidnost istorijskog niza. A to je ono sto smo hteli da psotignemo na pocetku.

Code:

-- Obrisacu poslednji red, pa da postavimo jos jedno ogranicenje:
DELETE Status_History
WHERE ID=1 AND DatumOd = '2011-01-19'

-- Opet imamo ciste podatke:
SELECT * FROM Status_History ORDER BY ID, DatumOD


         ID    StatusID DatumOd                 StariStatus StariDatumOD
----------- ----------- ----------------------- ----------- -----------------------
          1           0 2011-01-17 00:00:00.000        NULL NULL
          1           1 2011-01-18 00:00:00.000           0 2011-01-17 00:00:00.000
          2           1 2011-01-17 00:00:00.000        NULL NULL

(3 row(s) affected)


Ovo je kljucno granicenje. Uz prethodno postavljene uslove ovim kompletiramo zadatak.
Code:

-- Dodajmo ogranicenje:
ALTER TABLE Status_History
ADD CONSTRAINT unique_StariDatumOD_StariStatus 
UNIQUE (ID, StariDatumOD, StariStatus )    -- iste kolone, isti redosled kao u FK

Ovo resenje sa UNIQUE ogranicenjem vazi samo za MS SQL. MS SQL dozvoljava NULL vrednosti u UNIQUE ogranicenju, ali najvise jednu NULL vrednost, sto nama i treba. SQL standard dozvoljava visestruke NULL vrednosti i UNIQUE ogranicenjima, MS SQL ovde odstupa od standarda, ali nam to olaksava zivot u ovom slucaju. U relacionim sistemima koji u ovom slucaju prate ANSI standard za SQL ovo se mora resiti nekako drugacije. Nije nemoguce, cak smo to u Accesu uspeli da napravimo. Ako moze u Accessu, moze sigurno i u ORACLE ili Postgress ili Firebird. To ce neko iz te ekipe da razradi.

Da testiramo sta imamo:
Code:

-- Pokusajmo sada da unesmo pogresan podatak:
-- datumi su OK, Stari status postoji, ali nije dobar, 
-- trebalo bi da bude StariStatus = 1
-- jer  poslednji uneti red za ID=1 ima StatusID = 1
-- Ovo  ne bi trebalo: da prodje ovog puta:
INSERT INTO Status_History 
        (    ID, StatusID,    DatumOD        ,    StariStatus, StariDatumOD)
VALUES    (    1,    3        ,    '2011-01-19',        0,        '2011-01-17')
;
-- i nije proslo:
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'unique_StariDatumOD_StariStatus'. Cannot insert duplicate key in object 'dbo.Status_History'.
The statement has been terminated.


Tako se prati istorija promene odabranog atributa za odabrani entitet. U ovom slucaju atribut je bio StatusID, entitet je bilo sta, predstavljeno kolonom ID. DatumOd i StariDatumOd pripadaju metodi i oni ce uvek biti priosutni, bez obzira na entitet i atribut. Entitet ne mora da bude predstavljen jednom kolonom, moze biti i slozeni kljuc.



[ Zidar @ 25.03.2011. 17:16 ] @
Na ovaj pricnip se mogu nadgraditi i druge korisne stvari. Na primer:

1) dodavanjem tabele VlaidacijaPromeneStausa mozemo definisati dozvoljene parove (StariStatus, NoviStatus). Ta tabela kontrolise sta mozemo uneti u istoriju promene statusa. Ako iznajmljujemo automobile, statusi mogu biti na primer 'nabavljen', 'iznajmljen','na raspolaganju'otpisan' a dozvoljeni parovi su onda

('NULL','Nabavljen')
('nabavljen', 'na raspolaganju') -- mozemo ga ponuditi na iznajmlibanje nekome tek posto smo ga nabavili
('na raspoaganju','iznajmljen') -- mozenmo ga iznajmiti samo ako je na raspolaganju
('iznajmljen', 'na raspoaganju') -- ko ga pozajmi, obicno ga i vrati, i onda je ponovo na raspolaganju
('iznajmljen', 'otpisan') -- ponekad se ajto iznajmi pa se polupa i onda ga otpisemo
('na raspoaganju', 'otpisan') -- iako nije polupan, kad zastari, onda se otpisuje

Nesto slicno smo odradili u Accesu u temi o pracenju osnovnih sredstava (kod koga je bager?)

2) ako je entitet BankovniRacun i atribut IznosTransakcije (+ za ulaz, - za izlaz) mozemo dodati kolonu Stanje u tabelu Status_History koja je jednaka Stanje = PrethodnoStanje + IznosTransakcije. Tada mozemo dodati ogranicenje da Stanje bude uvek vece ili jednako od nule. Ovim smo ispunili san svih onih koji zele da u tabeli cuvaju stanje racuana i azurairaju ga posle svake transakcije.

Slucaj 1) sa validacijom para (StariStatus, NoviStatus) je veom znacajan jer otvara vrata za stvari o kojima do sada nismo mogli ni da sanjamo. A sve bez i jedne linije proceduralnog koda.

Kad unesete gomilu redova u ovako koncipiranu istorijsku tabelu, ispostavlja se da UPDATE jednostavno ne radi vise, zbog FOREIGN KEY. A ni masovno brisanje nece ici, mozete da obristet za svaki netitet samo poslednji red. Znaci, u normalnim uslovima brisanje je veoma tesko. A sve to bez igranaj sa privilegijama i igranja GRANT komandama. Ovo sprecavanje UPDATE i masovnog brisanja i nije tako naivna stvar, ko se nije opekao na ovome bar jednom u zivotu? Najveca opasnost za podatke su programeri koji njima rukuju iza scene, Korisnika mzoete front endom da kontorlisete gotovo 100%, ali programera ne bas tako lako. E ovde se tabela sam stiti.

Dugo sam imao zelju da napisem ovakav clanak negde. Hvala Nadi na pametno poatavljenom pitanju. Prinajem da sam ocekivao nesto ovako.

Ima li pitanja?


[ nadavesela @ 28.03.2011. 08:51 ] @
'I jeste, svaki red je roditelj prethodnom redu. U 'obicnoj' hijerrahiji jedan red moze imati vise dece, a pokazacemo da ovde jedan red ne treb da ima vise od jednog deteta.'

Samo bih napomenula i da ovde postoje 'ogranicenja'...Sta ako se prati istorija promene organizacione strukture...kad dva (ili vishe) odeljenja se fuzioniraju u novo odeljenje....Moze se odluciti da je novo odeljenje novi entitet, a da se kao prethodnik izabere samo jedan od prethodnih.
Druga situacija je kad se jedno odeljenje deli na dva (ili vishe) i kad sva nova odeljenja za prethodnika imaju jedan isti red.
[ nadavesela @ 28.03.2011. 08:57 ] @
'Tako se prati istorija promene odabranog atributa za odabrani entitet. U ovom slucaju atribut je bio StatusID, entitet je bilo sta, predstavljeno kolonom ID. DatumOd i StariDatumOd pripadaju metodi i oni ce uvek biti priosutni, bez obzira na entitet i atribut. Entitet ne mora da bude predstavljen jednom kolonom, moze biti i slozeni kljuc.'
Ovde bih samo upitala, dali ako pratim dva (ili vishe) atributa kao promenu jednog zapisa trebam imati i StariAtribute1, StariAtribut2,....) Zato sam vishe za vestacke kljuceve, gde mi je PrethodniId zajednicki atribut.
[ nadavesela @ 28.03.2011. 09:03 ] @
'Najveca opasnost za podatke su programeri koji njima rukuju iza scene, Korisnika mzoete front endom da kontorlisete gotovo 100%, ali programera ne bas tako lako. E ovde se tabela sam stiti.'
E ovome treba teziti iz starta, jos iz pocetnog dizajna tabela ( za sta se cesto nema 'vremena')
[ Zidar @ 28.03.2011. 16:08 ] @
Citat:
Samo bih napomenula i da ovde postoje 'ogranicenja'...Sta ako se prati istorija promene organizacione strukture...kad dva (ili vishe) odeljenja se fuzioniraju u novo odeljenje....Moze se odluciti da je novo odeljenje novi entitet, a da se kao prethodnik izabere samo jedan od prethodnih.
Druga situacija je kad se jedno odeljenje deli na dva (ili vishe) i kad sva nova odeljenja za prethodnika imaju jedan isti red.

Jos jedno odlicno zapazanje. Ovaj metod, ovako kako je izlzen do sada, generalno govoreci, NE MOZE se upotrebiti za pravcenje promene same hijerarhijske strukture, bolje receno, nije do sada pokazano da moze da prati i promenu strukture same hijerarhije. Izlozeni metod prati promene jednog izbranog atributa kroz vreme. Pomenuo sam hijerarhiju da bih nekako ucuinio pricu shvatljivijom.

Citat:
Ovde bih samo upitala, dali ako pratim dva (ili vishe) atributa kao promenu jednog zapisa trebam imati i StariAtribute1, StariAtribut2,....) Zato sam vishe za vestacke kljuceve, gde mi je PrethodniId zajednicki atribut.

Verovatno moze da se prati vise atributa odjednom, nisam probao niti sam videop da je neko drugi probao, sto ne znaci da ne moze. Mzda nekkao ovako: Verovatno to treba StariAtribuy1 i StariAtribut2 i stariArtibutN i svi oni ulaze u PK ( to je kao neki super-key). Pratis na primer promenu licnih podataka (ime, prezime, telefon). Tabela bi izgledala ovako nekako, u pseudo kodu:

LicniPodaci_History
(
StudentID, VaziOdDatuma, Ime, Prezime, Telefon svi NOT NULL
, StariDatum, StaroIme, StaroPrezime, StariTelefon svi NULL
, PK (StudentID, VaziOdDatuma, , Ime, Prezime, Telefon)
, UNIQUE (StudentID, StariDatum, StaroIme, StaroPrezime, StariTelefon )
, FOREIGN KEY StudentID, StariDatum, StaroIme, StaroPrezime, StariTelefon
REFERENCES LicniPodaci_History (StudentID, VaziOdDatuma, , Ime, Prezime, Telefon)
, CHECK (sta oga sme da bude NULL i kako se odnosi prema drugim kolonama)
)

Ovde treba videti kako medjusobno stoje atributi koje pratimo, da li su NULL ili nisu NULL.

Opisana tehnika je prilicno nova. Prvi clanci o tome pojavili su se u Oktobru 2010, Joe Chelko ih je napisao. Zatim i Alex Kuznetsov, negde oko Nove godine 2011. Tu negde se pominje i knjiga "Applied Mathematics for Database professionals", autori Lex de Haan and Toon Koppelaars. Takodje se pominje i knjiga "Defensive Database programming with SQL Server, Alex Kuznetsov". Ja sam uspeo obe knjige da nadjem kao PDF na iternetu. Prve je kompletna, a iz Alexove nedostaju dva poslednja poglavlja (koaj se ne bave oviom temom). To je sve sto za sada postoji objavljeno o ovoj metodologiji. Ovo je vlajda jedan od prvih clanaka na SH jeziku, mislim da smo nesto slicno pisali na Access forumu pre mesec dana.

Ovo je sve novo i vrata su tek odskrinuta. Ima puno toga da se uradi i proba. Koga zanima, napred, pa kad zapnete, tu smo da pomognemo, ako umemo. Promena strukture hijerarhije - tezak zadatak, mozda se moze, a mozda i ne moze resiti postojecim alatima. Treba probati.

Teorijski znacaj ove 'metodologije' nije tako veliki kao recimo uvodjenej FOREIGN KEYS. Prakticno, medjutim, mozda je ovo i znacajnije jer znacajno smanjije potrebu za procedralnim kodom.

Ko pita prava pitanja, mozda ce i naci odgovor. Ko ne pita, sigurno nece.



[ nadavesela @ 28.03.2011. 21:02 ] @
Mozda mnogi od nas naterani praksom imamo potrebu primene te nove 'metodologije', i koliko god da je uspesno primenjena ili ne, mislim da je njena glavna ideja sustinska i ne uci se, jer cini mi se ko intiuitvno prihvati taj je i intuitivno bio prinudjen da je otkrije, ali ohrabruje kad ima clanaka i knjiga za 'propagiranje', 'objasnjenje' i 'perfekcioniranje' iste.
[ nadavesela @ 04.04.2011. 14:48 ] @
Zidar evo kako sam spojila dve metodologije (vestacke kljucevi, i istorija izmena). Za constrinte sam koristila UDF, a StatusId, i StariStatus su mi identifikatori novog i prethodnog reda. StariDatumOd mi je ipak ComputedColona pomocu UDF. Ovako mogu upisivati novi red ako mi je promenjen bar neki od atributa (cena, naziv,...), i mogu fleksibilno menjati uslove izmene. Postoji neki proceduralni kod, al je na nivou constrainta tabele (UDF) a ne u stored proceduri.
Mislim da je vecina zahteva iz 'tvog' clanka ispunjena, jedino ako nesto ne previdjam.

Tabela:
Code:

CREATE TABLE [dbo].[Status_HistoryES](
    [ID] [int] NOT NULL,
    [StatusID] [int] IDENTITY(1,1) NOT NULL,
    [DatumOd] [datetime] NOT NULL,
    [StariStatus] [int] NULL,
    [cena] [decimal](15, 2) NOT NULL,
    [naziv] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Status_HistoryES] PRIMARY KEY CLUSTERED 
(
    [StatusID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [unique_StariStatusES] UNIQUE NONCLUSTERED 
(
    [ID] ASC,
    [StariStatus] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


REFERENCA izmedju StatusID i StariStatus
Code:

ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK ADD  CONSTRAINT [FK_Status_HistoryES_Status_HistoryES] FOREIGN KEY([StariStatus])
REFERENCES [dbo].[Status_HistoryES] ([StatusID])



UDF:
Code:

CREATE FUNCTION [dbo].[fn_StariStatus](@Id AS int,@StatusId as int)
RETURNS int
AS
BEGIN
DECLARE @starstatus as int
SET @starstatus=(select max(StatusId) as starstatus from dbo.Status_HistoryES where Id=@Id and StatusId<@StatusId)
RETURN (@starstatus)
END


Code:

create FUNCTION [dbo].[fn_StariDatum](@Id AS int,@StatusId as int)
RETURNS DATETIME
AS
BEGIN
DECLARE @datumod as datetime
SET @datumod=(select max(Datumod) as datumod from dbo.Status_HistoryES where Id=@Id and StatusId<@StatusId)
RETURN (@datumod)
END


Code:

CREATE FUNCTION [dbo].[fn_StariDatumOd](@StariStatus as int,@Id AS int)
RETURNS datetime
AS
BEGIN
DECLARE @datumdo as datetime
SET @datumdo=(select min(DatumOd) as datumvreme from dbo.Status_History 
--where StariStatusComputed =(select min(StariStatus) as StariStatus from dbo.Status_HistoryES where Id=@Id AND StariStatus=@StariStatus))
where StariStatus =(select min(StariStatus) as StariStatus from dbo.Status_HistoryES where Id=@Id AND StariStatus=@StariStatus))

RETURN (@datumdo)
END

Code:

CREATE FUNCTION [dbo].[fn_StaraCena](@Id AS int,@StatusId as int)
RETURNS decimal(15,2)
BEGIN
DECLARE @cena as decimal(15,2)
SET @cena=(select cena from dbo.Status_HistoryES where Id=@Id and StatusId=dbo.fn_StariStatus(@Id,@StatusId))
RETURN (@cena)
END

Code:

CREATE FUNCTION [dbo].[fn_StariNaziv](@Id AS int,@StatusId as int)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @naziv as nvarchar(50)
SET @naziv=(select naziv from dbo.Status_HistoryES where Id=@Id and StatusId=dbo.fn_StariStatus(@Id,@StatusId))
RETURN (@naziv)
END



Constrainti:
Code:

ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK ADD  CONSTRAINT [CK_CenaNazivStatus_HistoryES] CHECK  ((isnull([dbo].[fn_StaraCena]([Id],[StatusId]),(0))<>[Cena] OR isnull([dbo].[fn_StariNaziv]([Id],[StatusId]),(0))<>[naziv]))
GO
ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK ADD  CONSTRAINT [CK_DatumOdStatus_HistoryES] CHECK  (([dbo].[fn_StariDatum]([Id],[StatusId])<=[DatumOd]))
GO
ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK ADD  CONSTRAINT [CK_StariStatusStatus_HistoryES] CHECK  ((isnull([dbo].[fn_StariStatus]([Id],[StatusId]),(0))=[StariStatus]))










[Ovu poruku je menjao nadavesela dana 05.04.2011. u 09:19 GMT+1]

[Ovu poruku je menjao nadavesela dana 05.04.2011. u 13:32 GMT+1]
[ Zidar @ 04.04.2011. 22:13 ] @
Bravo ! Deluje zanimljivo.

Nadam se da cu stici da detalljnije proanaliziram resenje do kraja nedelje. Deluje zanimljivo. U medjuvremenu, demonstriraj nam ne nekoliko primera sta se prati i kako radi. Bice nam svima lakse da shvatimo sta se desava. Daj neke test podatke, da imamo s cim da radimo.

[ nadavesela @ 05.04.2011. 08:40 ] @
CREATE PROCEDURE [dbo].[TestPodaci]
@Id as int,@datumod as datetime,@StariStatus as int,@cena as decimal(15,2),@naziv nvarchar(50)
AS
BEGIN
INSERT INTO dbo.Status_HistoryES(Id,datumod,StariStatus,cena,naziv)
VALUES (@Id,@datumod,@StariStatus,@cena,@naziv)
END

ako bi se unos izvrsavao bez greske
posto ne postoji prethodni zapis za entitet Id=1, input Parametar StariStatus ima vrednost NULL
1:
Code:

execute dbo.TestPodaci 1,'01/01/2011',NULL,100.00,'P1'

posle ovog unosa StatusId=1
Novim zapisom se vrsi izmena tog reda i input parametar StariStatus ima vrednost 1
2:
Code:

execute dbo.TestPodaci 1,'02/01/2011',1,200.00,'P1'

posle ovog unosa StatusId=2 i izmena tog reda podrazumeva novi Insert ciji je parametar StariStatus=2
3:
Code:

execute dbo.TestPodaci 1,'03/01/2011',2,200.00,'P2'

posle ovog unosa StatusId=3 i izmena tog reda podrazumeva novi Insert ciji je parametar StariStatus=3
4:
Code:

execute dbo.TestPodaci 1,'04/01/2011',3,300.00,'P3'

posle ovog unosa StatusId=4 i izmena tog reda podrazumeva novi Insert ciji je parametar StariStatus=4
5:
Code:

execute dbo.TestPodaci 1,'05/01/2011',4,200.00,'P1'

posle ovog unosa StatusId=5 i izmena tog reda podrazumeva novi Insert ciji je parametar StariStatus=5
ovaj insert ima ponavljanje vec prethodno unesenih vrednosti (u 2:) za cenu=200 i naziv='P1' ali to su nove vrednsti u odnosu na poslednji uneseni red (4:) koji se menja (sa 5:)
pri svim insertima treba biti zadovoljen uslov da datumod mora biti >= od Datuma od reda koji se menja
novi insert je ispravan i ako je bar jedan od atributa (cena ili naziv) se razlikuje od vrednosti atributa reda koji se menja

za novi entitet Id=2 evidencija pravilnog unosa bi bila, tu je moguce uneti istu cenu i naziv kao i prethodno za entitet Id=1, ali mozda je i to dobro, ako neko zeli da te izmene prati kao novi entitet.
Code:
execute dbo.TestPodaci 2,'01/01/2011',NULL,100.00,'P1'

Code:
execute dbo.TestPodaci 2,'02/01/2011',6,200.00,'P1'

Code:
execute dbo.TestPodaci 2,'03/01/2011',7,200.00,'P2'

Code:
execute dbo.TestPodaci 2,'04/01/2011',8,300.00,'P3'

ponovna izmena entiteta Id=1 znaci izmenu reda ciji je StatusId=5 i izmena tog reda podrazumeva novi Insert ciji je parametar StariStatus=5
Code:
execute dbo.TestPodaci 1,'06/01/2011',5,300.00,'P1'



Moji primeri su idealni...svako moze nesumice uneti sto on hoce, pa da vidi kako tabela sa constraintima reaguje.
ono sto mi se malo ne dopada je sto pri greskama StatusId (koji je Identity) se povecava, sto predpostavljam se moze resiti na nivou stored procedure.
dala sam samo neki pocetne primere....potrudicu se da jos detaljnije objasnim (ako neko uopste cita, da ne bude da objasnjavam samoj sebi)



[Ovu poruku je menjao nadavesela dana 05.04.2011. u 13:21 GMT+1]
[ nadavesela @ 05.04.2011. 12:45 ] @
Nakon unosa test podataka kreirana je computed kolona StariDatumOd sa formulom: ([dbo].[fn_StariDatumOd]([StatusId],[Id]))
Tako da je za svaki red moguce na nivou tabele odredjen DatumOd koga vazi do datuma do kog vazi (StariDatumDo)

[ Zidar @ 05.04.2011. 16:52 ] @
Pogledao sam resenje detaljnije i vide se prve pukotine. Pazi, nije bio cilj da se u tabeli pokaze interval vazenja para (Cena,Naziv). Cilj je bio da se grantuje da ce niz datuma biti neprekidan, da se garantuje da je rd izveden iz tacno jednog reda koji ima stariji datum.

Na osnovu tvojih skripti, uspeo sam da napravim tabelu i sva ogranicenja, i racunsku kolonu. Stored procedura mi ne treba za unos, obican INSERT INTO zavrsava posao.
Code:

IF Object_ID('Status_HistoryES') IS NOT NULL DROP TABLE Status_HistoryES
go
IF Object_ID('fn_StariStatus') IS NOT NULL DROP FUNCTION fn_StariStatus
go
IF Object_ID('fn_StariDatumOd') IS NOT NULL DROP FUNCTION fn_StariDatumOd
go
IF Object_ID('fn_StaraCena') IS NOT NULL DROP FUNCTION fn_StaraCena
go
IF Object_ID('fn_StariNaziv') IS NOT NULL DROP FUNCTION fn_StariNaziv
go

CREATE TABLE [dbo].[Status_HistoryES](
    [ID] int NOT NULL,
    [StatusID] int IDENTITY(1,1) NOT NULL,
    [DatumOd] smalldatetime NOT NULL,
    [StariStatus] int NULL,
    [cena] smallmoney  NOT NULL,
    [naziv] varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Status_HistoryES] PRIMARY KEY CLUSTERED 
(
    [StatusID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [unique_StariStatusES] UNIQUE NONCLUSTERED 
(
    [ID] ASC,
    [StariStatus] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK 
ADD  CONSTRAINT [FK_Status_HistoryES_Status_HistoryES] FOREIGN KEY([StariStatus])
REFERENCES [dbo].[Status_HistoryES] ([StatusID])
go
CREATE FUNCTION [dbo].[fn_StariStatus](@Id AS int,@StatusId as int)
RETURNS int
AS
BEGIN
DECLARE @starstatus as int
SET @starstatus=(select max(StatusId) as starstatus 
from dbo.Status_HistoryES
 where Id=@Id and StatusId<@StatusId)
RETURN (@starstatus)
END
go
--DROP FUNCTION fn_StariDatumOd

CREATE FUNCTION [dbo].[fn_StariDatumOd](@StariStatus as int,@Id AS int)
RETURNS datetime
AS
BEGIN
DECLARE @datumdo as datetime
SET @datumdo=(select min(DatumOd) as datumvreme from dbo.Status_HistoryES 
--where StariStatusComputed =(select min(StariStatus) as StariStatus from dbo.Status_HistoryES where Id=@Id AND StariStatus=@StariStatus))
where StariStatus =(select min(StariStatus) as StariStatus from dbo.Status_HistoryES where Id=@Id AND StariStatus=@StariStatus))
RETURN (@datumdo)
END
go
CREATE FUNCTION [dbo].[fn_StaraCena](@Id AS int,@StatusId as int)
RETURNS decimal(15,2)
BEGIN
DECLARE @cena as decimal(15,2)
SET @cena=(select cena from dbo.Status_HistoryES where Id=@Id and StatusId=dbo.fn_StariStatus(@Id,@StatusId))
RETURN (@cena)
END
go
CREATE FUNCTION [dbo].[fn_StariNaziv](@Id AS int,@StatusId as int)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @naziv as nvarchar(50)
SET @naziv=(select naziv from dbo.Status_HistoryES where Id=@Id and StatusId=dbo.fn_StariStatus(@Id,@StatusId))
RETURN (@naziv)
END
go
ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK 
ADD  CONSTRAINT [CK_CenaNazivStatus_HistoryES] 
CHECK  ((isnull([dbo].[fn_StaraCena]([Id],[StatusId]),(0))<>[Cena] OR isnull([dbo].[fn_StariNaziv]([Id],[StatusId]),(0))<>[naziv]))
GO
ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK 
ADD  CONSTRAINT [CK_DatumOdStatus_HistoryES] 
CHECK  (([dbo].[fn_StariDatumOD]([Id],[StatusId])<=[DatumOd]))    -- izmena, bilo je 'fn_StariDatum'
GO
ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK 
ADD  CONSTRAINT [CK_StariStatusStatus_HistoryES] 
CHECK  ((isnull([dbo].[fn_StariStatus]([Id],[StatusId]),(0))=[StariStatus]))
GO

ALTER TABLE Status_HistoryES
ADD StariDatumOd  AS ([dbo].[fn_StariDatumOd]([StatusId],[Id]))
go


Onda sam uneo prvi test podatak. za artkl ciji je ID=1 uneo sam cenu i naziv:
Code:
INSERT INTO dbo.Status_HistoryES(Id, datumod    ,StariStatus ,cena  ,naziv)
                        SELECT  1, '01/01/2011',NULL       ,100.00  ,'P1';

SELECT * FROM Status_HistoryES
;
(1 row(s) affected)
         ID    StatusID DatumOd                 StariStatus                  cena naziv StariDatumOd
----------- ----------- ----------------------- ----------- --------------------- ----- -----------------------
          1           1 2011-01-01 00:00:00            NULL                100.00 P1    NULL

(1 row(s) affected)

Zatim sam pokusao da unesem nekoliko redova odjednom. Uspeva samo ako znas koji je status poslednji za posmatrani artikl. U visekorisnckom okruzenju ovo moze da predstavlja problem, ako dva korisnika menaju podatke za isti artikl istovremeno. Ne bi trebalo tako da bude, ali se ne mzoemo oslanjati na 'ne bi trebalo...'. Ovako je islo:
Code:

INSERT INTO dbo.Status_HistoryES(Id, datumod    ,StariStatus ,cena  ,naziv)
SELECT ID = 1, datumod = '2011/01/16', StariStatus = 1, Cena = 100.00, Naziv = 'P2'
UNION    -- ovde moram da pogadjam StariStatus
SELECT ID = 1, datumod = '2011/01/18', StariStatus = 2, Cena = 102.00, Naziv = 'P2'
UNION    -- ovde moram da pogadjam StariStatus
SELECT ID = 1, datumod = '2011/01/18', StariStatus = 3, Cena = 103.00, Naziv = 'P3'
;
SELECT * FROM Status_HistoryES
;
         ID    StatusID DatumOd                 StariStatus                  cena naziv StariDatumOd
----------- ----------- ----------------------- ----------- --------------------- ----- -----------------------
          1           1 2011-01-01 00:00:00            NULL                100.00 P1    2011-01-16 00:00:00.000
          1           2 2011-01-16 00:00:00               1                100.00 P2    2011-01-18 00:00:00.000
          1           3 2011-01-18 00:00:00               2                102.00 P2    2011-01-18 00:00:00.000
          1           4 2011-01-18 00:00:00               3                103.00 P3    NULL

(4 row(s) affected)

-- StariDatumOd u stvari pokazuje 'Vazi Do'
-- Imamo dve promene na isti dan, 18 januar 2011. Moze da bude OK, a i ne mora.

Onda sam pokusao da unesem neke pogresne podatke.
Code:

-- slucaj 1: nema promene u (Cena,Naziv), menja se samo StatusID
INSERT INTO dbo.Status_HistoryES(Id, datumod    ,StariStatus ,cena  ,naziv)
SELECT ID = 1, datumod = '2011/01/19', StariStatus = 4, Cena = 103.00, Naziv = 'P3'
-- DObro, spreceno je:
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_CenaNazivStatus_HistoryES". The conflict occurred in database "Test", table "dbo.Status_HistoryES".
The statement has been terminated.
*/

Sad sam pokusao da unesem pogresan datum. Poslednji uneti datum je bio 18 Januar 2011. Sledeci datum moze biti samo isti ili veci od toga. Stio se mene tice, trebalo bi da bude strogo veci, ali da dopustimo mogucnost vece ili jednako. Datum raniji od 18 Januara ne bi smeo da se dozvoli. Zasto? Pa ako smo napravili neke fakture u periodu 01-16 Januara, ocekujemo da na njima pise cena koja je vazila od 1 Januara. pazie sad ovo:
Code:
-- Slucaj 2: pogresan datum, raniji od dosada unetih
-- STa ako sada unesem neki potpuno pogresan datum, recimo 10 januar 2011?
INSERT INTO dbo.Status_HistoryES(Id, datumod    ,StariStatus ,cena  ,naziv)
SELECT ID = 1, datumod = '2011/01/10', StariStatus = 4, Cena = 104.00, Naziv = 'P4'
-- proslo je a ne bi trebalo da prodje!

SELECT * FROM Status_HistoryES
;

         ID    StatusID DatumOd                 StariStatus                  cena naziv StariDatumOd
----------- ----------- ----------------------- ----------- --------------------- ----- -----------------------
          1           1 2011-01-01 00:00:00            NULL                100.00 P1    2011-01-16 00:00:00.000
          1           2 2011-01-16 00:00:00               1                100.00 P2    2011-01-18 00:00:00.000
          1           3 2011-01-18 00:00:00               2                102.00 P2    2011-01-18 00:00:00.000
          1           4 2011-01-18 00:00:00               3                103.00 P3    2011-01-10 00:00:00.000
          1           6 2011-01-10 00:00:00               4                104.00 P4    NULL

(5 row(s) affected)

-- Pogledaj red gde je StatusID = 4, pise da je (cena,naziv) vazila od 18 januara do 10 januara
-- Za statusID = 6 imamo da vazi od datuma 10 januar 2011. 


Ako se dozvoli naknadan unos datuma 10 Januar, onda ce se na stavkama fakture izdate na dan i posle 10 Januara iznenada promeniti cenu i/ili naziv artikala. Posto je faktura vec odstampana i poslata kupcu, sramota je da sada menjamo uslove, mogao bi kupac da nas tuzi ili da izgledamo neposlovno. To je svrha pracenja promena kroz vreme - da se ne dozvolje promene unazad. Ako je svrha samo da se upise kad su se desile promene, sa pretpostavkom da je korisnik to odradio tacno, onda nam one komplikovane funkcije i ogranixcenja i ne trebaju.

Ovde smo imali samo dva atributa ciju promenu pratimo. Izbegli smo kreiranje novih kolona, ali smo umesto novie tri kolone dobili nove tri funkcije. FK smo napravili svejedno, samo je maskiran FK kao CHECK koji gleda u funkciju. Sve u svemu, suvise komplikovano, za samo dve kolone.

Nacin koji sam opisao postize ovo sa mnje pisanja. Novi post.





[Ovu poruku je menjao Zidar dana 05.04.2011. u 18:11 GMT+1]
[ Zidar @ 05.04.2011. 17:10 ] @
Evo kako je moglo:
Code:

CREATE TABLE PromeneImenaIcene
-- primer tabele gde se prate promene dva atributa
-- Za vise tributa, povecava se PK i UN_StariPodaci, dodavanjem novih kolona
-- takodje, u CK_StariPodaci_su_NULL_na_pocetku treba dodati odgovarajuce nove kolne
(
    ArtiklID int NOT NULL
    , Naziv varchar(5) NOT NULL
    , Cena money NOT NULL
    , VaziOd datetime NOT NULL
    , STariDatum datetime NULL
    , StaraCena money NULL
    , StariNaziv varchar(5)
    , CONSTRAINT PK_PromeneImenaIcene 
                PRIMARY KEY (ArtiklID, VaziOD, Cena, Naziv)
    , CONSTRAINT UN_StariPodaci 
                UNIQUE (ArtikLID, Staridatum, StaraCena, StariNaziv)
    , CONSTRAINT CK_StariDatum_Stariji_od_Novog 
                CHECK((StariDatum < VaziOd OR STaridatum IS NULL))
    , CONSTRAINT CK_StariPodaci_su_NULL_na_pocetku 
                CHECK (
                        (Staridatum IS NULL AND StaraCena IS NULL 
                                AND  StariNaziv IS NULL)
                        OR
                        (Staridatum IS NOT NULL AND StaraCena IS NOT NULL 
                                AND  StariNaziv IS NOT NULL)
                        )
)
;


Malo testiranja:
Code:

-- za prvi red o nekom artiklu, stari podaci su svi NULL
INSERT INTO PromeneImenaIcene (ArtiklID, Naziv, Cena, VaziOd, STariDatum,  StaraCena, StariNaziv)
                            SELECT ArtiklID = 1, Naziv = 'A1', Cena = 10, VaziOd = '20110101'
                                    , STariDatum = NULL,  StaraCena = NULL, StariNaziv = NULL
;
SELECT * FROM PromeneImenaIcene
;
   ArtiklID Naziv                  Cena VaziOd                  STariDatum                          StaraCena StariNaziv
----------- ----- --------------------- ----------------------- ----------------------- --------------------- ----------
          1 A1                    10.00 2011-01-01 00:00:00.000 NULL                                     NULL NULL

(1 row(s) affected)

-- prva promena, 07 januara promenili smo ime iz A1 u A2 i cenu iz 10 u 11 dinara:
INSERT INTO PromeneImenaIcene (ArtiklID, Naziv, Cena, VaziOd, STariDatum,  StaraCena, StariNaziv)
                            SELECT ArtiklID = 1, Naziv = 'A2', Cena = 11, VaziOd = '20110107'
                                    , STariDatum = '20110101',  StaraCena = 10, StariNaziv = 'A1'
;
SELECT * FROM PromeneImenaIcene
;
   ArtiklID Naziv                  Cena VaziOd                  STariDatum                          StaraCena StariNaziv
----------- ----- --------------------- ----------------------- ----------------------- --------------------- ----------
          1 A1                    10.00 2011-01-01 00:00:00.000 NULL                                     NULL NULL
          1 A2                    11.00 2011-01-07 00:00:00.000 2011-01-01 00:00:00.000                 10.00 A1

(2 row(s) affected)


Pokusajmo da napravimo istu gresku zbog koje smo krtikovali Nadino resenje:
Code:
-- Pokuajmo da umetnemo novi red izmedju prva dva, recimo 3 Januara
INSERT INTO PromeneImenaIcene (ArtiklID, Naziv, Cena, VaziOd, STariDatum,  StaraCena, StariNaziv)
                            SELECT ArtiklID = 1, Naziv = 'A03', Cena = 13, VaziOd = '20110103'
                                    , STariDatum = '20110107',  StaraCena = 11.00, StariNaziv = 'A2'

-- ne moze:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_StariDatum_Stariji_od_Novog". The conflict occurred in database "test", table "dbo.PromeneImenaIcene".
The statement has been terminated.

Sta ako nam stvarno treba novi red sa datumom 3 Januar, jer smo ga zaboravili pri unosu? Sve zavisi od realnosti. Ako nismo izadl;i ni jednu fakturu koja vuce cenu izmedju 1 i 7 Januara 2011, onda je OK. Obrisimo sve redove koji su uneti sa datumima vecim od 3 Januar. Brisanje je moguce samo jedan po jedan red, i to poslednji red,. Znaci da smo sprecili i nenamerna brisanja mase redova.

Ako smo pak izdali neku fakturu koja cita cenu iz bilo kog od unetih redova, onda treba spreciti brisanje tih redova. Tu nam sad treba funkcija ili triger, da spreci brisanje.

O nakakvom UPDATE nad istorijskom tabelom nemojte ni da sanjate.

Sve u svemu, brisanje je jako otezano, izmene su prakticno nemoguce - a to je princip na kome se poslednjih 200 godina i nesto duze zasniva knjigovodstvo. nema umetanja redova unazad, nema brisanja podataka i pisanja novih vrednosti. Ako je neka transakcija uenta pogresno, ispravka se vrsi dodavanjem nove transakcije koja ce stanje dovesti na tacnu vrednost.

Kad se knjigovodstvo vodi na papiru, princip nepromenljivosti unetih transakcija se lako obezbedjuje ispartanim papirom i mastiljavom olovkom. Kompjuteri nazalost imju UPDATE i DELETE komande i mi se uglavnom ne stitimo od toga. Istina, uglavnom je UDATE/DELETE par sakriven od korisnika, ali nije od programera. A programei su ti koji brljaju po bazi, nisu korisnici.

Gde ja radim, obracun plata, poreza, godisnjih odmora, bolovanja, prisustva na poslu je prebacen sa COBOLa na ORACLE. Divno, napredujem u dobrom pravcu. Tu se cuva izmedju ostalog i podatak o bracnom stanju i deci, zbog koordinacije zdravstvenog osiguranja. I broj dana godisnjeg odmora na raspolaganju. COBOL je bio spor i nezgraopan, ali je radilo sve kako treba. U ORACLE, iznenada smo svi postali razvedeni i godisnji odmor nam je nestao. Neko je odradio neki DELETE ili UPDATE a da nije primetio gresku. Greku je primecena kad je nekome odbijen zahtev za neplatu lekarskih usluga, za zenu i decu, jer u sistemu je ta osoba 'single', iako se vide deca i bracni drug. Sistem nije imao ogranicenje (CONSTRAINT) koji trazi da ako imas bracnog druga da ne mozes biti 'single'. I nije imao, niti ima, pracenje raspolozivog broja dana za godisnji odmor.

Zato je ZIdar ovoliko dosadan sa ogranicenjima, CONSTRAINTS pa CONSTRAINTS, check, FK ili PK, nema veze, samo da se zavede nekakav red u bazi.

[ nadavesela @ 06.04.2011. 08:23 ] @
Zidar u vezi prvog propusta tipa
Citat:

Code:
-- Slucaj 2: pogresan datum, raniji od dosada unetih
-- STa ako sada unesem neki potpuno pogresan datum, recimo 10 januar 2011?
INSERT INTO dbo.Status_HistoryES(Id, datumod ,StariStatus ,cena ,naziv)
SELECT ID = 1, datumod = '2011/01/10', StariStatus = 4, Cena = 104.00, Naziv = 'P4'
-- proslo je a ne bi trebalo da prodje!


posto je to bio moj prvi i osnovni constraint, pa sam morala proveriti kako je moguce, pustila sam tvoju scriptu i proverila kod mene, i u tvojoj scripti nema UDF fn_StariDatum, i zato prolazi pogresno unesen datum. Naravoucenije za mene, drugi put da pustim celosnu scriptu.

Citat:

ALTER TABLE [dbo].[Status_HistoryES] WITH CHECK
ADD CONSTRAINT [CK_DatumOdStatus_HistoryES]
CHECK (([dbo].[fn_StariDatumOD]([Id],[StatusId])<=[DatumOd])) -- izmena, bilo je 'fn_StariDatum'
GO


Tacno je da je u ovom constraintu bilo fn_StariDatum, i treba da bude da ne bi prosao pogresan datum..Ne znam zasto si menjao...fn_StariDatumOd mi je za computed kolonu StariDatumOd.
[ nadavesela @ 06.04.2011. 09:14 ] @
Posle uspesnog ogranicenja koje si imao sa
Code:

INSERT INTO PromeneImenaIcene (ArtiklID, Naziv, Cena, VaziOd, STariDatum,  StaraCena, StariNaziv)
                            SELECT ArtiklID = 1, Naziv = 'A03', Cena = 13, VaziOd = '20110103'
                                    , STariDatum = '20110107',  StaraCena = 11.00, StariNaziv = 'A2'


Testirala sam i ja malo ovo drugo resenje :) i nesumice unela
Code:

INSERT INTO PromeneImenaIcene (ArtiklID, Naziv, Cena, VaziOd, STariDatum,  StaraCena, StariNaziv)
                            SELECT ArtiklID = 1, Naziv = 'A03', Cena = 13, VaziOd = '20110103'
                                    , STariDatum = '20110101',  StaraCena = 11.00, StariNaziv = 'A2'

Ovo prolazi, i treba da prodje, ali zar ne treba StariDatum mesto '20110101' da bude '20110107' jer je to datum poslednjeg zapisa koji se menja.
Zakljucak, znaci treba da gadjam datum (isto kao sto treba da gadjam StariStatus) u mom resenju.
Mislim da istoriske izmene, podrazumevaju mogucnost izmene samo jednog reda za izabrani entitet, tako da sa kolonom Aktivan ili u mom resenju, koristeci StariDatumOd IS NULL, mogu istoriski evidentirati snizenje svih cena za dati iznos ili procent, a da pri tom imam istoriske izmene.
Code:

INSERT INTO dbo.Status_HistoryES (Id,DatumOd,StariStatus,cena,naziv)
SELECT Id,'20110119',StatusId as StariStatus,cena-20.00 as cena ,naziv from dbo.Status_HistoryES Where StariDatumOd is null



[Ovu poruku je menjao nadavesela dana 06.04.2011. u 10:30 GMT+1]
[ Zidar @ 06.04.2011. 14:27 ] @
Originalna skripta nije htela da prodje, pa sam promenio tako da moze da prodje. Zato pozeljno da se pripremi primer i testira sve, specijalno za clanak. OK, znaci moze da se napravi da radikako treba, a to je najvaznije.

Ako se porede resenja, imamo jedno gde se broj kolna duplira (moj predlog) i drugo gde originalna tebla ostaje ista, a ogranicenja se postizu kroz user-defined funkcije, ugrubo po jedna za svaku kolonu koja se prati. U razlicitim okolnostima, verovatno bi se resenja ponasala razlicto, u smislu brzine.

U mnogim knjigama sam nasao da funkcije u ulozi constraints mogu da uspore rad, ako ima mnogo redova. To nisam uspeo da vidim u praksi, iako u nekim situacijama debelo koristim funkcije na tabelama sa desetak miliona redova, i to povelikih redova. Isto tako, resenje po metodi Alexa Kuznetsova trebalo bi da uspori insert, jer se procesira dvostruko veci broj kolona i indeksi su ogromni. Ne mogu ni da potvrdim ni da odbacim, tamo gde sam primenio resenja Alexa Kuznetsova nisma jos dostigao toliko veliki broj redova da bi se usporenje osetilo. Metoda Kuznetsova implicitno podrazumeva da se unosi jedan po jedan red (mada ne mora) i tada je tesko dokazati da je nesto toliko sporo da bi se na front endu osetilo.

U svakom slucaju, Nadi sve cestitke za odlicno obavljen posao. Ono malo sto smo dobili kao ideju, da nekako treba obezbediti nepromenljivost unetih podataka, nepromenljivost istorije ako hocete, Nada je resila na originalan nacin. Nije lako da se razume, ako ni resenje Kuznetsova sto nije lako da se razume. Ali, kad jednom ukapirate, mnogi problemi koji su do sada bili ne resivi postaju resivi. naravno da ovo ne resava sve moguce probleme, ali otavar nam horizonte zasigurno.

[ nadavesela @ 07.04.2011. 13:18 ] @
Citat:

Kompjuteri nazalost imju UPDATE i DELETE komande i mi se uglavnom ne stitimo od toga. Istina, uglavnom je UDATE/DELETE par sakriven od korisnika, ali nije od programera


Hvala na interesovanju i cestitkama, a da ideja zahteva doradu, odnosno jos constrainta, i pukotina koja trazi resenje je sledeca:
DELETE nije moguc, ni Update kolone StariStatus, ali...kolonu DatumOd, Cenu, Naziv je moguce menjati u istoriskim podacima.
Ako pogodim Constaint za jednu od ovih kolona imam ga i za ostale.

Dok sa dodavanjem kolona u 'tvom' primeru, PRIMARNG KLJUCA
Code:

ALTER TABLE [dbo].[Status_HistoryES] ADD  CONSTRAINT [PK_Status_HistoryES] PRIMARY KEY CLUSTERED 
(
    [StatusID] ASC,
    [DatumOd] ASC,
    [cena] ASC,
    [naziv] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

i referencijalnog integriteta
Code:

ALTER TABLE [dbo].[Status_HistoryES]  WITH CHECK ADD  CONSTRAINT [FK_Status_HistoryES_Status_HistoryES] FOREIGN KEY([StariStatus], [DatuOdStari], [CenaStara], [NazivStari])
REFERENCES [dbo].[Status_HistoryES] ([StatusID], [DatumOd], [cena], [naziv])

izmene nisu moguce, TAKO DA JE KUZNECOV IPAK VISHE U PRAVU, u smislu ogranicenja.
Sad mi je ideja u pravcu da te kolone koje ipak treba dodati, budu computed kolone koje kao Persisted, kreirane fizicki ne znam dal mogu ucestvovati u izgradnji referencijalnog integriteta. Probacu.


[Ovu poruku je menjao nadavesela dana 07.04.2011. u 15:39 GMT+1]

[Ovu poruku je menjao nadavesela dana 07.04.2011. u 16:31 GMT+1]
[ nadavesela @ 09.04.2011. 08:48 ] @
Pukotina u mom resenju se najednostavnije krpi trigerom
Code:

ALTER TRIGGER [Status_HistoryES_ZabranjenoUPDATE] ON [dbo].[Status_HistoryES] FOR UPDATE
AS
rollback

Na taj nacin je zadovoljen uslov da nikakve izmene nisu moguce nad vec upisanim podacima (redovima).
Delete je vec ogranicen Referencijalnim Integritetom izmedju StausId i StariStatus,
Insert je ogranicen Constraintima koji koriste UDF i koje mogu menjati i prilagodjavati saglasno potrebama,
a izbegavam fizicki upis podataka u kolone(StaraCena,StariNaziv....) koje vec imam u prethodnom zapisu i mogu ih dobiti u svakom momentu preko StariStatus i neke UDF.
[ MarkoBalkan @ 09.04.2011. 21:06 ] @
za @Zidar

zidar je stari baja koji zna puno toga, ali komplicira.


što se tiče tablica za history.

može se napraviti bez puno kompliciranja, recimo, nešto u stilu:

create table artikli (
artikli_šifra integer,
artikli_ime varchar(50),
artikli_opis varchar(200),
artikli_jm varchar(10)
)

create table artikli_cjenik(
artikli_šifra integer,
datumOd date,
datumdo date,
cijena double precision,
PRIMARY KEY (artikli_šifra,datumOD)
)

datum do stavimo recimo 2099.
za artikl za koji želimo dohvatiti cijenu, provjerimo da li se datum nalazi između DatumOd i datumDo.
npr.

Code:

INSERT INTO artikli(
            artikli_sifra, artikli_ime, artikli_opis, artikli_jm, artikli_tezina)
    VALUES (1, 'jabuke', 'jabuke 1 klase', 'kg');

INSERT INTO artikli(
            artikli_sifra, artikli_ime, artikli_opis, artikli_jm)
    VALUES (2, 'sok', 'sok od jabuke', 'kom');




Code:

INSERT INTO artikli_cjenik(
            artikli_sifra, datumod, datumdo, cijena)
    VALUES (1, '1.1.2011', '1.3.2011', 5.00);

INSERT INTO artikli_cjenik(
            artikli_sifra, datumod, datumdo, cijena)
    VALUES (1, '1.4.2011', '31.12.2100', 5.20);


ako bi sad za artikl sa šifrom 1 ubacili novu cijenu, prvo bi morali postaviti današnji datum (datumDo) i ubacili bi artikl sa novom cijenom
datumOd -> sutašnji datum, a datumDo bi stavili 2100 godinu.

i u svakom trenutku možeš saznati koja je bila cijena artikla u određenom periodu.

Code:

SELECT 
  artikli.artikli_sifra, 
  artikli.artikli_ime, 
  artikli.artikli_opis, 
  artikli.artikli_jm, 
  artikli_cjenik.datumod, 
  artikli_cjenik.datumdo, 
  artikli_cjenik.cijena
FROM 
  public.artikli, 
  public.artikli_cjenik
WHERE 
  artikli.artikli_sifra = artikli_cjenik.artikli_sifra;


1;"jabuke";"jabuke 1 klase";"kg";"2011-01-01";"2011-03-01";5
1;"jabuke";"jabuke 1 klase";"kg";"2011-04-01";"2100-12-31";5.2





ovo je napravljeno na postgresql-u.
ali princip je isti.
[ nadavesela @ 11.04.2011. 08:04 ] @
Posto zelim da se ideja shvati probacemo na konkretnim primerima:
Dali tvoje resenje dozvoljava nakon:
Citat:
INSERT INTO artikli_cjenik(
artikli_sifra, datumod, datumdo, cijena)
VALUES (1, '1.1.2011', '1.3.2011', 5.00);

INSERT INTO artikli_cjenik(
artikli_sifra, datumod, datumdo, cijena)
VALUES (1, '1.4.2011', '31.12.2100', 5.20);

Da neko u nekom buducem trenutku, nakon sto je dati artikl upotrebljen u nekim drugim tabelama (sa cenama u periodu vazenja ) izvrsi ovakve inserte:
Code:

INSERT INTO artikli_cjenik(
            artikli_sifra, datumod, datumdo, cijena)
    VALUES (1, '20.12.2010', '31.12.2010', 4.00);

ili pak
Code:

INSERT INTO artikli_cjenik(
            artikli_sifra, datumod, datumdo, cijena)
    VALUES (1, '15.1.2011', '15.2.2011', 6.00);
INSERT INTO artikli_cjenik(
            artikli_sifra, datumod, datumdo, cijena)
    VALUES (1, '16.2.2011', '28.2.2011', 8.00);


Dali to znaci da ce za fakture izdate na primer u periodu meseca januara postojati dve cene za artikl 1:
od '1.1.2011' do '1.3.2011' koja kaze 5.20
i druga od '15.1.2011' do '15.2.2011' koja kaze 6.00
i tu je sad pitanje validnosti vazenja datumdo, jer po toj logici cena 5.20 vazi do 14.1.2011.








[Ovu poruku je menjao nadavesela dana 11.04.2011. u 11:00 GMT+1]
[ MarkoBalkan @ 11.04.2011. 16:37 ] @
provjeru staviš datumOd > (select max(datumD0) from artikli_cjenik where artikli_šifra=@šifra)


ovo možeš odraditi programski ili na proceduri.




[ Zidar @ 11.04.2011. 21:00 ] @
Marko, nisi shvatio poentu. Poenta nije uopste da se prati istorija promena da bi se videlo kad se sta promenilo. Poenta je da se garantuje kvalitet podataka, integritet ako hoces, tako da se sprece u najvecoj meri mogucnosti da nepazljivi UPDATE ili DELETE napravi stetu.

Ako zapisujem promene cene u obliku (ArtklID, Cena,VaziOdDatuma) to je sasvim dovoljno da mi da istoriju kroz nekakav kveri, ne treba mi nikakav DatumOD, DatumDo. I sve to lepo vazi dok se ne napravi greska na unosu. Greska je obicno takva da se tesko i otkriva. A podaci se u bazu unose na sve moguce nacina. Kroz aplikaciju X, kroz aplikaciju Y, bulk insert, kroz proceduru, direktno INSERT INTO narednom kroz SMS, na milion nacina. Naivno je verovati da se integritet podataka stiti programskim kodom, bilo u nekom programskom jeziku, bilo u stored proceduri, pa cak i trigeri nisu pravo resenje. Trigeri se ne aktiviraju uvek prilikom bulk insert. Naivno je reci da ce isert uvek ici kroz neku stored proceduru i samo tako. Ni ogranicenja kroz permissions nisu resenja, jer kako rekosmo, katastrofalne greske prave oni koji imaju permissions jako visokog nivoa. Nije retko da svi programeri u odeljenju imaju 'sa' privilegije. A ako i nemaju, nisu daleko od toga. Mogu da kreiraju objekte, izvrsavaju procedure i imaju write access podacima.

Jos je naivnije oslanjati se na sposobnosti programera da zastite podatke 'kroz kod'. Eto, ti nisi ni primetio sta se u stvari trazi, koji se problem resava, a ponudio si resenje koje je Nada istog momenta torpedovala jer ga je lako torpedovati.

Cela ova prica nije zapoceta da bi se sporili koji je najbolji nacin da se prati istorija cene ili naziva proizvoda. Primer je izabran jer je zgodan da se objasne principi jednog metoda koji se dalje moze nadgradjivati tako da donese velike koristi. O tome ce bitio vise reci na blog-sajtu BazePodataka, tokom narednih nekoliko nedelja.

Opet, na srecu ili nesrecu, data management nije regulisan zakonom kao inzinjerske grane i svako moze bukvalno da radi sta i kako hoce. Ako ti je metoda dosadna i prekomplikovana, niko te ne tera da je upotrebis.

Rekoh, radi ko sta hoce i kako hoce. Iako ne bi bilo zgoreg da su neke stvari propisane zakonom, na primer da sta sve ulazi u dokumentaciju i koji koraci i postupci moraju da se odrade i sta sve mora da se dokaze na papiru pre nego sto se projekat pusti u produkciju. Ni jedan gradjevinski projekat ne dobija zeleno svetlo dok se ne urade arhitektonski planovi, staticki proracun, proracun struje, vode knalizacije, grejanja i tako dalje. I nije dovoljno samo imati sve te dokumente, nego cela stvar ide na reviziju gde je nako placen da pronadje sto vise rupa u projektu. Pa se opet desi da padne poneki balkon i nakrivi se zgrada. Ali mnogo, mnogo manje nego broj softverskih resenja koja ili ne rade uopste ili ne rade kako se ocekuje da rade.

[ nadavesela @ 12.04.2011. 10:44 ] @
Posto sam generalno shvatila i zelim primenu metoda i nacina rada o kome je Zidar pisao, ipak imala bih da prijavim jos jednu rupu u mom resenju a Zidar ce nam reci dal je tako i u resenju Kuznecova, pa da vidim koja su moguca resenja.
Naime constraint koji je izmedju StasusId i StariStatusId, odlicno funkcionishe (ogranicava) kad brisem jedan po jedan zapis osim poslednjeg zapisa za dati artikl, al kad izvrsim naredbu
Code:
DELETE FROM dbo.Status_HistoryES

izbrise sve redove iz tabele.
Isto ogranicenje vazi za naredbu
Code:
DELETE FROM dbo.Status_HistoryES Where StariDatumOd is not null

Nadam se da jedino resenje nije triger koji radi revoke na DELETE za zapise ciji StariDatumOd is not null.
I to mi se vishe svidja (sto jednistavniji triger) nego programski ili proceduralni kod.
Sve Vas pozdravlja Vesela

[ Zidar @ 12.04.2011. 15:56 ] @
Kod Kuznetsova nije moguce obrisati sve redove odjednom. Na poslednjem redu za dati entitet, dozvoljene su promene 'novog stanja'. Taj red je moguce i obrisati. Ostale redove nije moguce obrisati jer FK to ne dozvoljava.

Mnogi ovo ne vole. Ako se zaista napravi greska negde na pocetku, recimo zaboravi se jedna promena, gresku je nemoguce ispraviti, niti je moguce obrisati sve pa ispocetka. Brisanje se radi jedan po jedan red, pa se onda ponovo sve insertuje. Alex ne preporucuje funkcije u FK jer tvrdi da se razlicito ponasaju kad imas single-row insert/update i kad imas multiple-row insert/update, u nekim slucajevima. Ja nisam uspeo da ove probleme sa funkcijama u FK potvrdim, ali nisam mnogo ni probao. alex kaze da je moguce dobiti false-positive i false-negative reakcije stranog kljuca, u nekim situacijama.

Sto se tice ispravke gresaka i masovnog brisanja, u nekim slucajevima Alex koristi MERGE komandu, ali to radi samo u SQL 2008 koji ja jos uvek nemam pa ne mogu da komentarisem.

[ MarkoBalkan @ 12.04.2011. 16:38 ] @
za @nadavesela i @zidar, da odgovorim

kreiramo funkciju i constraint

Code:

CREATE OR REPLACE FUNCTION provjera(integer)
  RETURNS date AS
'select max(datumDo) from artikli_cjenik where artikli_sifra=$1'
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION provjera(integer) OWNER TO postgres;


ALTER TABLE artikli_cjenik
  ADD CONSTRAINT proba CHECK (datumod > provjera(artikli_sifra));


ovo nam osigurava da se razdoblja za određeni artikl ne preklapaju.

što se tiče brisanja, zato postoje role, useri i prava na bazi da se definira tko što smije raditi.

tako da se sve može napraviti sa funkcijama i constraint-ima, a da se ne komplicira.

[ Zidar @ 12.04.2011. 18:17 ] @
Marko je u pravu, iako je u pocetku malo bio narogusen
Citat:
tako da se sve može napraviti sa funkcijama i constraint-ima, a da se ne komplicira.
Ne moze sa bas uvek sev napraviti, ali treba teziti tome. Ne vidim samo zasto su FK bez funkcija vise komplicirani nego FK sa funkcijama. neka to ostane stvar ukusa i testova brzine, vazno je da psotoje constraints

Citat:
što se tiče brisanja, zato postoje role, useri i prava na bazi da se definira tko što smije raditi.
To je sve OK, ali kako rekoh, podatke obicno sprzi neko ko ima odgovarajucu rolu i permission. I nikad namerno. Uglavnom se previdi nesto i poof - otisli podaci. Podaci se ne stite paswordima. To mozemo i u obicnom file sistemu, ne treba nam RDBMS za to.

Da sumiramo: ako ne postoje constraints, CHECK, FK, UNIQUE, sve ostalo ne garantuje integritete podataka. Podaci se ne stite ni programskim kodom - stored procedure i trigeri su programski kod, ako je bolje koristiti sp i trigger nego VB/C# kod za garantovanje integritete. UDF funkcije su negde na pola puta, ponekd korisne, ponekad opasne, ponekad brze, ponekad spore. Naravno, ima situacija kada se mora koristiti sto se ima i sto se ume, kad nema mnogo izbora. U svakom slucaju vredi sto vise alata imati u torbi, moze da zatreba.

Postoje situacije koje se ne mogu resiti onim sto nam trenutno nude proizvodjaci RDBMS. Koje ikad cuo za praznu fakturu? U realnom svetu faktura bez stavki je nemoguca. Fakture je celina koju cien zaglavlje i stavke. U RDBMS mi to razbijamo na dve tabele - Faktura i Stavke. Da bi stavka postojala, mora prvo da postoji faktura, tako kazu pravila normalizacije. Znaci, 'INSERT INTO Fakture' => evo je faktura bez ijedne stavke. Tek onda mozemo da radimo 'ISNERT INTO Stavke'. Ako iz nekog razloga dodje do prekida u radu, ostade nam faktura bez stavki - roditelj bez dece. Ne postoje constraints niti funkcije niti trigeri, nista sto moze da speci postojanje fakture bez stavki. U tom slucaju moramo da to odardimo na nivou stored procedure ili na nivou front end koda. Pocnemo TRANSakciju, pa ako ne unesemo stavke, onda ROLLBACK. Lepo. ali radi samo kroz stored proceduru ili kroz program. Masovni insert ili bulk insert ne radi ovako. Neki pametni ljudi trazili su vec da u SQL standard udje nesto sto se zove DEFERRED CONSTRAINT RESOLUTION, gde bi se ove stvari omogucile. Mi mozemo da ostavimo relaciju (relationship, ne relatin) 1: (nula ili vise) u SQL => ne mozemo da sperceimo 1: 0, bar u jednom kratkom vremenu. Za sada, ni jedan proizvodjac ne nudi nista na ovu temu. Isto kao sto ne nude pracenje istorije promene, pa da one duplicirane i komplicirane kolone budu sakrivene od korisnika, ali prisutne u sistemu i da se automastki popunjavaju.

A da je komplikovano, jeste, nije lako ni da se razume, ni da se primeni kad se jednom razume. Pa sta ako nije lako, one velike pare koje placaju DBA treba i da se opravdaju, niko nas ne samo placa da cuvamo lozinke.



[ MarkoBalkan @ 12.04.2011. 19:04 ] @
Citat:
Zidar
Da sumiramo: ako ne postoje constraints, CHECK, FK, UNIQUE, sve ostalo ne garantuje integritete podataka. Podaci se ne stite ni programskim kodom - stored procedure i trigeri su programski kod, ako je bolje koristiti sp i trigger nego VB/C# kod za garantovanje integritete. UDF funkcije su negde na pola puta, ponekd korisne, ponekad opasne, ponekad brze, ponekad spore. Naravno, ima situacija kada se mora koristiti sto se ima i sto se ume, kad nema mnogo izbora. U svakom slucaju vredi sto vise alata imati u torbi, moze da zatreba.



zašto se kod velikih enterprise aplikacija sve radi kroz orm mapiranje bez ičega na bazi osim referenci?
sustav je brz onoliko koliko je optimizirano sve.ako radiš query sa 10 funkcija, a svaka funkcija ima 10 query-a, naravno da bude sporo.
što se tiče skrivanja strukture, koriste se view-i(ili pogledi).
što se tiče bulk inserta, obraditi i pročistiti preko ETL-a i ubaciti ili učitati i obraditi preko procedure.
[ Zidar @ 12.04.2011. 19:40 ] @
Citat:
zašto se kod velikih enterprise aplikacija sve radi kroz orm mapiranje bez ičega na bazi osim referenci?

Ne razumem sta ovo znaci. Sta je 'orm' i sta je 'mapiranje bez icega na bazi osim referenci'?
[ MarkoBalkan @ 12.04.2011. 20:00 ] @
Citat:
Zidar: Ne razumem sta ovo znaci. Sta je 'orm' i sta je 'mapiranje bez icega na bazi osim referenci'?



http://en.wikipedia.org/wiki/Object-relational_mapping

zašto se to koristi, zato što je po nekim aprogramiranje time brže, dok s druge strane je jedno 3-5 puta sporije što se tiče performanci.

da li je ok, neću komentirati, neki kažu da je ok, neki kažu da je glupost.
[ Zidar @ 12.04.2011. 20:53 ] @
OK, object relational mapping. Onda uporedjujemo babe i zabe, ne vredi raspravljati. Ne valja samo ako ono 'bez icega na bazi' znaci da na bazi nema nikakvih constraints. Od previse constraints ne moze da boli glava.

U zdravlje, valjda je vereme da zavrsimo temu.

[ nadavesela @ 13.04.2011. 08:10 ] @
Citat:

Za sada, ni jedan proizvodjac ne nudi nista na ovu temu. Isto kao sto ne nude pracenje istorije promene, pa da one duplicirane i komplicirane kolone budu sakrivene od korisnika, ali prisutne u sistemu i da se automastki popunjavaju.

Mislim da ce sirenje same metode o istoriji promena i integritetu podataka u tom smislu, zahtevati i da DBMS omoguce na nivou tabele definiranje dali ista moze da bude samo INSERT tabela, dali moze INSERT i UPDATE, dali INSERT i DELETE, dali INSERT i UPDATE i DELETE,
pa da mi sami u dizajnu definiramo kakav nacin rada zelimo i tada mozda necemo morati definirati ni Constraint, ni UDF, ni Trigger...sve ce to biti skriveno od nas i shvatljivo svima.
[ nadavesela @ 13.04.2011. 08:49 ] @
Citat:

Mnogi ovo ne vole. Ako se zaista napravi greska negde na pocetku, recimo zaboravi se jedna promena, gresku je nemoguce ispraviti, niti je moguce obrisati sve pa ispocetka.


Samo kao pocetna ideja da je sve ipak moguce al da trazi rad i doradu, moze se ispraviti i izbrisati...
Mozda je jednostavnije u mom primeru jer radim sa Identifikatorima reda (Vestackim kljucem), tako da mi datumOd nije deo primarnog kljuca.
Ideja je ova, kao sto je zapis istorije promena isla u jednom pravcu

Id datumOd Cena PrethodniId Korekcija
1 01/01/2011 200 - 0
2 01/02/2011 300 1 0
3 01/03/2011 400 2 0

isto je moguce napraviti korekciju inserta do momenta unazad koji treba da bude poslednji vazeci
(neka to bude momenat kad je datum '01/01/2011' kad je cena 200 a Id=1)
te bih unazad zapisujuci datum kad pravim promenu '01/04/2011' insertovala ovako
4 '01/04/2011' 400 3 1
5 '01/04/2011' 300 2 1
u svim funkcijama moracu kao uslov imati i kolonu Korekcija, a sam upis vrednosti cene u uvom slucaju mi je odvisan jer meni je bitna veza da znam koji redovi su proglaseni nevazecim ( a to su redovi za vrednosti kolone Korekcija=1 koji su u koloni PrethodniId, znaci redovi 2 i 3) i za naredni upis redovi ciji Id=2 i 3 ne ulazi u mogucnost da se preko UDF i constrainta definisanog preko nje unese kao vazeci za kolonu PrethodniId
e tad mogu da upisem eventualni naredni ispravni podatak, sa ispravnim datumom
6 '01/03/2011' 400 1 0
Malo je futuristicki i nerealno al ipak to je to...imate trag svega sto se radi...bas kao u knjigovodstvu.
Izviinjavam se za eventualni los prikaz podataka za primer.
[ Zidar @ 13.04.2011. 15:22 ] @
Citat:
Malo je futuristicki i nerealno al ipak to je to...imate trag svega sto se radi...bas kao u knjigovodstvu.

Upravo to je poenta - trag svega sto se radi, bas kao u knjigovodstvu.

Sve je ovo novo, ima puno da se radi i istrazuje i otkrivaju nove stvarina svakom koraku, sta moze i sta ne moze. Prvi clanak na ovu temu napisao je Joe Celko, cak je dao i kod, koji naravno nije radio. Posle nekoliko meseci Alex je napisao knjigu sa primerima iz stvarne prakse gde deo toga radi. Onaj deo koji smo opisali u ovoj temi - pracenje promena. Sada je na nama da istrazujemo i probamo. Ja imam jednu bazu u produckciji koja radi dve stvari - pracenje promena i validaciju statusa, po Celkovom modelu. nemem vremena da to objavim i opisem ali bice valjda uskoro. Nada ce imati nesto sa korekcijama, neko drugi jos nesto i eto nas na sledecem nivou kvaliteta.

Alex je dao i primer kako se iz ove price lako ulazi u pracenej stanja, gde je stanje = SUM(ulaz) - SUM(izlaz) . Stanje se se cuva u tabeli i uvek je azurno, a sve bez trigera. Samo FK i CHECK.

A na forumu Access dali smo resenje za probleme tipa iznajmljivanja - biblioteka, rent-a-car, duzenje osnovnih sredstava (ko je u kom momentu duzio kamion...) Ne znam tacno koja je tema, potrazicu na Access forume. E vala ako smo mogli u Accesu da uspostavimo sva ogranicenja na nivou tabela, bez koda, onda mora da moze i u SQL. Samo treba da se potrudimo, pa sta bude.


[ MarkoBalkan @ 13.04.2011. 16:08 ] @
Citat:
Zidar

Alex je dao i primer kako se iz ove price lako ulazi u pracenej stanja, gde je stanje = SUM(ulaz) - SUM(izlaz) . Stanje se se cuva u tabeli i uvek je azurno, a sve bez trigera. Samo FK i CHECK.



zašto se stanje čuva u bazi?
zašto se takve promjenjive vrijednosti čuvaju u bazi, kad se može napraviti fukcija koja to računa?

ako imamo par tisuća artikala, nije problem, ionako se korisniku prikazuje maksimalno 20 artikala na stranici, ako lista dalje, računa se za slijedećih 20 i sve brzo radi.
[ mkaras @ 13.04.2011. 18:08 ] @
On 13.4.2011 17:08, "MarkoBalkan" wrote:

Citat:

...
zašto se stanje čuva u bazi?
...


Zato što se koristi tip podataka kao izračunata vrednost. Ne zauzima
prostor i izračunava se u vreme upita. Nekada može da bude izuzetno
koristan podatak.
[ MarkoBalkan @ 13.04.2011. 18:24 ] @
Citat:
mkaras: On 13.4.2011 17:08, "MarkoBalkan" wrote:



Zato što se koristi tip podataka kao izračunata vrednost. Ne zauzima
prostor i izračunava se u vreme upita. Nekada može da bude izuzetno
koristan podatak.


nismo se razumjeli.
npr,
kreiram funkciju "stanje" sa ulaznim parametrom artikla, a funkcija mi vraća double na dvije decimale.

i onda imam

select artikl_šifra,artikl_ime,artikl_stanje(artikl_šifra) as stanje from artikli

vrijednosti koje se moraju računati, nikad se ne čuvaju u bazi već se dinamički izračunavaju.
kad korisnik pregledava artikle(proizvode), njemu se izračunava stanje i prikazuje na ekranu za 20 artikala(20 artikala po stranici).

jer ako se zapiše u bazu, stanje se non stop mijenja i nije 100 % točan podataka, a i narušava se 3. normalna forma.

jer ako se vrijednost može dobiti pomoću agragatnih funkcija i/ili pomoću operacija, tada se vrijednost ne čuva u bazi.

ako oltp baza nije minimalno u 3. normalnoj formi, kompliciraju se stvari, a osim toga postoji redudancija podataka, održavanje je teško, a onaj tko je projektirao takvu bazu nema pojma.

da se vratim na moj prijedlog, kad se stave ograničenja, datum do se ostavi prazan, a kad se unosi nova cijena, slog sa praznim poljem se update-a i ubaci novi.

znači u mojem slučaju nije moguće izvršiti update bilo kojeg postojećeg sloga bilo kojeg polja, osim ako to polje nije prazno.
a samo za datumdo bi stavio null, ostala polja bi trebala biti not null.



[Ovu poruku je menjao MarkoBalkan dana 13.04.2011. u 19:51 GMT+1]

[Ovu poruku je menjao MarkoBalkan dana 13.04.2011. u 19:51 GMT+1]
[ Zidar @ 13.04.2011. 19:34 ] @
Marko, ne treba da se uzbudjujes, mi samo diskutujemo razne opcije. Niko ne kaze de je to sto radis funkcijom lose i pogresno. (Ako me pamcenje ne vara, ja sam licno postavio blog post u kome se funkcije koriste za racunanje ili kontrolu stanja). Niti kazemo da se stanje MORA cuvati u tabeli. Samo pokusavamo da pokazemo kako to moze, ada ne kosta mnogo i da nije komplikovano mnogo.

Tacno je da teorijski se stanje moze izracunati uvek kverijima. Isto je tacno da je nekeda, u nekim situacijama to prilicno sporo. Ako ti Mkaras kaze da ima smisla NEKADA cuvati stanje u bazi, onda veruj.

Zamisli koliko ljudi danas koristi bankovne masine i koliko ljudi pristupa svojim racunima u banci putem interneta. Za svaki taj pristup pokazuje se stanje, znaci vrte se hiljade instanci kverija, umotanih u funkciju ili direktno, nad hiljadama rekorda, u svakom trenutku. SQL masina koja to radi mora da je jako mocna... Banke, barem ove u Americi ne vrte kverije. Stanje se izracunava svaki put i svaki put se radi update. Kako - ne znam, mozda nije uopste SQL u pitanju, ali znam da se stanje azurira posle svake transakcije.

Zasto se ne strpis malo, mozda ime nesto interesantno i korisno da se vidi. Niko ne kaze da MORA da se radi ovako. I veruj mi, Alex Kuznetsov je poslednja osoba koja bi odstupila od pravila normalizacije. I covek radi u finansijskoj instituciji. Pola knjige mu je o tome kako se stititi od gresaka i ako zasita zastititi podatke, od korisnika, i od onih koji imaju sve passworde i privilegije (takvi su najopasniji). Ako se strpis, videces da nema nikakve denormalizacije - sve je i dalje kontrolisano kroz FK i poneki CHECK, bez funkcija i bez trigera.

Normalizacija ne znaci 'nema redundantnosti', normalizacija znaci 'nema nepotrebne i stetne redundantnosti'

Teras me da radim na brzinu pa mogu negde da pogresim
[ MarkoBalkan @ 13.04.2011. 19:58 ] @
u 3. mormalnoj formi nema redudancije podataka.

što se tiče banaka i vođenja računa.


obrada kod banaka se radi po noći kad je najmanje opterećenje.
ako se lova diže sa bankomata, obrada je trenutna.

stanje kod banaka se računa kod obrade i zapisuje jer je brže i jednostavnije nego da se svaki puta računa.

postoji x načina kako se nešto može napraviti, samo je pitanje koji je pravi i najbolji, jer uvijek imaš samo jedan ili eventualno dva načina koji su najbolji za određenu situaciju.

ne uzbuđujem se.

[Ovu poruku je menjao MarkoBalkan dana 13.04.2011. u 21:08 GMT+1]
[ Zidar @ 13.04.2011. 21:55 ] @
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

:-)
[ mkaras @ 13.04.2011. 23:19 ] @
@MarkoBalkan:

Pogledaj definiciju za "Computed Columns in SQL Server". Jedna od njih
sa objašnjenjem se može naći na http://www.mssqltips.com/tip.asp?tip=1682

Ako malo bolje pogledaš videćeš da su to samo izračunate vrednosti koje se ne čuvaju u tabeli već se čuva definicija kolone koja je neka funkcija i
uvek se izračunava prilikom prikaza. To i nisu stvarni podaci ali se sa njima radi kao da jesu. malo olakšava pisanje upita i prikaza rezultata upita.

Citat:
For such scenarios where calculated values are required or values are generated through manipulation on other columns, we have a powerful feature provided in SQL Server. This feature is "Computed Columns".

A computed column is computed from an expression that can use another column or columns in the same table. Functions, variables, constants, non computed column names or any combination of all these may be used along with operators to create a computed column. In this tip we will go through an example of implementing a computed column.

I primer za kreiranje tabele sa kolonom koja sadrži izračunate vrednosti:
Code:
USE [AdventureWorks]
GO 
-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
[empNumb] [int] NULL,
[DOBirth] [datetime] NULL,
[DORetirement] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
)
GO
[ nadavesela @ 14.04.2011. 07:45 ] @
Citat:

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


Zidar, vec sam pitala dal u metodi Kuznecova DELETE nad tabelom je moguc, rekao si NE. Al pokusaj da izvrsis
Code:

DELETE StanjeNaLageru

rezultat je:
(3 row(s) affected)
[ Zidar @ 14.04.2011. 12:21 ] @
Citat:
Zidar, vec sam pitala dal u metodi Kuznecova DELETE nad tabelom je moguc, rekao si NE. Al pokusaj da izvrsis DELETE StanjeNaLageru

Oops, nisasm probao ali ti verujem. Ja samo priacm onako kako cujem Danas mi racunar nece biti dostupan pa ne mogu da probam nista niti da odgovaram. Dobro je da znamo za problem, pa cemo ga nekako resiti,

Havala nazapazanju
[ MarkoBalkan @ 14.04.2011. 16:20 ] @
ovo je ok rješenje, ali nije baš upotrebljivo kad imaš 50 ili 100 ljudi u prodaji, a koji su ujedno i konkuretni useri na bazi.
pošto si rekao da se ručno upisuje stanje.

jedino se može napraviti constraint da računa: stanje_na skladišu - rezervacija_artikla

i kako svaki unosi rezervaciju , automatski se provjerava da je gornji izraz > od onog što komercijalista recimo unese kao rezervaciju ili količinu za narudžbu.

[ Zidar @ 14.04.2011. 21:36 ] @
Citat:
ovo je ok rješenje, ali nije baš upotrebljivo kad imaš 50 ili 100 ljudi u prodaji, a koji su ujedno i konkuretni useri na bazi.
pošto si rekao da se ručno upisuje stanje.

Varujem da resenje iammsmisla tamo gde su promeen relativno retke. Rucno upisivanje stanja nije obavezno, pokazacu sutra kako se to radi kroz proceduru pa korisnik ne mora ni da zna sta se desava. Poenta je da je rucno uopsivanje poprilicno komplikovano i da ce svi prirodno teziti da upotrebe proceduru - procedura se nece moci zaobici tako lako. Ako vec moramo kroz proceduru, onda moze da se tamo doda sta god hoces. Procedura nije pouzdana za kontrolu unosa kao se lako moze zaobici.

Ne znam koliko ej sve sporo ili brzo u nekim realnim uslovima sa mnogo korisnika, ali nije ni predvidjeno za bas sve uslove. Vazno je da znamo da moze i ovako.

Sto se tice prosirenja na pracenje rezervacija - treba probati. Probaj, pa javi kako je proslo ili je negde zapelo. Volim kad izviru nove ideje.

Ovo je sve u stvari uvod u slucajeve koji se drugacije ne mogu resiti. Iznajmljivanej knjiga/auta/opreme se na drugi nacin ne moze resiti pouzdano. Slicna struktura ide za redove voznje i pracenje resavanja problema na primer. To su sve stavri koje se nemogu resiti u okviru 'regularne' normalizacije.

[ nadavesela @ 15.04.2011. 12:25 ] @
Citat:

Ovo je sve u stvari uvod u slucajeve koji se drugacije ne mogu resiti. Iznajmljivanej knjiga/auta/opreme se na drugi nacin ne moze resiti pouzdano. Slicna struktura ide za redove voznje i pracenje resavanja problema na primer. To su sve stavri koje se nemogu resiti u okviru 'regularne' normalizacije.


Ali dokle god poslednji zapis u istoriji datog elementa moze da se brise, znaci moze unazad da se obrise cela tabela
jednom naredbom DELETE, i tu se gubi znacaj referencijalnog integriteta koji je postavljen nad tabelom preko StatusId i StariStatus.
[ Zidar @ 15.04.2011. 15:31 ] @
Imamo znaci problem. UPDATE je donekle kontrolisan, ali DELETE moze da prodje u najopsnijim oblicima - svi redovi iz tabelee ili svi redovi za odabrani artikl. Scarry! S druge strane, moramo da dozvolimo da se nekako moze brisati, ali kontrolisano. Recimo, brisanje jedan po jedan red je kontrolisano. Ili neko drugo pravilo mozete da postavite. ja cu da probam sa brisanjem jedan po jedan red.

Znaci, da bi obrisali 50 redova za ArtiklID, mi treba da brisemo jedn po jedn red, sa 50 DELETE naredbi. Mozemo da napravimo loop, naravno. Ali to je komplikovano i dobro je da je tako. Ako neko bas hoce da obrise sve promene nad izabranim artiklo, red po red, to je OK, verovatno ima razloga za to. I plus, ta se akcija moze uhvatiti u nekoj drugoj istoriji. Cilj je da se ne dozvoli slucajno nenamerno brisanje. Ovo ej samo ejdan nacin da se to postigne, lighweight protection kako bi ovde rekli. Uvek moze da se doda na to, triger koji kontrolise sta sme a ne sme da se radi, do stepena koji hocemo. Niste ne pomaze protiv TRUNCATE TABLE...

Da ne bismo slucajno i nenamerno kucali DELETE StanjeNaLageru, evo triger koji sprecava brisanja ali samo ako se zahteva brisanje SVIH redova. OStala brisanja prolaze. Ovo je na brzaka resenje, verovatno imarupa i sad treba pronaci rupe i mozda nacin da se zakrpe. U trigeru se koristi varijabla, moglo je i ebz nje, ali mi se cini da je ovako citljivije:
Code:
 
CREATE TRIGGER trg_StanjeNaLageru_NoDeleetAll ON StanjeNaLageru
FOR DELETE
AS
DECLARE @CntBaseTable int, @CntDeleted int
;
-- @CntBaseTable je 0 ako smo obrisali sve redove
SELECT @CntBaseTable =  (SELECT COUNT(*) FROM StanjeNaLageru)
;
IF @CntBaseTable = 0 
    BEGIN 
        ROLLBACK 
    END
GO

DELETE  StanjeNaLageru
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Ovo nazalost nije dovoljno. Nada je odlicno zapazila da se moze izvristi bez problema ovo:
Code:
DELETE StanjeNaLageru
WHERE  ArtiklID = 1
Ako hocemo da ogranicimo brisanje, tako da se u jednom DELETE mzoe obrisati tacno jedan red, treba nam drugaciji triger. Ne bih prepravljao postojeci, jer mu ime nie odgovarajuce. Radije cu ga onesposobiti ili obrisati.
Code:
-- Postojeci triger moramo ili da iskljucimo, ili da obrisemo
ALTER TABLE StanjeNaLageru 
DISABLE TRIGGER trg_StanjeNaLageru_NoDeleetAll
;

-- a moze i ovo:
DROP TRIGGER trg_StanjeNaLageru_NoDeleetAll 
;
Ide novi triger:
Code:
  CREATE TRIGGER trg_StanjeNaLageru_Dlete_1_red ON StanjeNaLageru
FOR DELETE
AS
DECLARE  @CntDeleted int
;
-- @CntBaseTable je 0 ako smo obrisali sve redove
SELECT @CntDeleted =  (SELECT COUNT(*) FROM Deleted)
;
IF @CntDeleted > 1 
    BEGIN 
        ROLLBACK 
    END
GO
Testirajmo novi triger, da opet ne lupim nesto bez pokrica pa se opet obrukam :-)
Code:

-- probajmo da obrisemo sve redove u tabeli:
DELETE StanjeNaLageru
;
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
;

-- Da probamo da obrisemo sve redove za ArtiklID = 1
DELETE StanjeNaLageru
WHERE  ArtiklID = 1
;
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

OK, znaci nema brisanja svih redova u tabeli, nema brsianja svih redova za izabrani artikl. Da li moze jedn po jedan? Da probamo:
Code:



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
          2          10 2011-01-01 00:00:00.000             10            NULL NULL

(5 row(s) affected)

-- da obrisemo tacno jedan red, onaj za ArtiklID = 2
DELETE StanjeNaLageru
WHERE  ArtiklID = 2
-- (1 row(s) affected)    -- radi, kako smo predvideli

-- da obrisemo neki red u sredini, recimo treci red za ArtiklID = 1
DELETE StanjeNaLageru
WHERE  ArtiklID = 1
AND DatumPromene = '2011-01-25 00:00:00.000'
-- ne moze, ali ovaj put nas nije sprecio trigger, nego FK:
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 "Dejan", table "dbo.StanjeNaLageru".
The statement has been terminated.

Pokusaji da se obrsie vise redova, ili cela tabela, spreceni su dejstvom trigera. Pokuasj da se obrise tacno jedan red, negde u sredini, sprecen je u FK, nije ni dosla akcija do trigera.
Nismo jos pokusali da obrisemo poslednji red za artikl 1, onaj gde je Promena = 0. Evo:
Code:
-- moze li poslednji red za artiklid = 1?, onaj gde je proemna nula, zero, 0
BEGIN TRANSACTION
DELETE StanjeNaLageru
WHERE  ArtiklID = 1
AND DatumPromene = '2011-01-25 00:00:01.000'
;
--(1 row(s) affected)

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)

-- da vratimo red, trebace nam mozda
ROLLBACK


Deluje da smo privremeno zakrpili nekako najvece rupe u resenju. Bilo bi lepo ako bi moglo i bez trigera, li sta da radimo, ne moze uvek. ako nista, integritet podataka ne zavisi od trigera. Ako radimo bulk insert, moracemo dobro da pripremimo podatke, inace ce ih FK-CHECK odbaciti. Ako bi nitegritet stitili trigerom, pa radimo bulk insert, triger bi mogao biti privremeno iskljucen (DISABLE TRIGGER je veoma laka komanda za kucanje). Sad, i DELETE triger moze da se DISABLE, ukoliko imate prava na to. E pasad, i dba treba nesto da rdi, da malo razmisli ko ima prava na sta. Nije samo read/write sta treba kontrolisati.

U sledecm postu, stored procedura koja sakriva kompleksnost INESRT INTO komande i celu ovu duplikaciju kolona od korisnika. Verovatno posle vikenda, imam puno posla danas na poslu pa ne mogu da zabusavam bas mnogo. :-)


:-)
[ nadavesela @ 18.04.2011. 15:29 ] @
Citat:
Deluje da smo privremeno zakrpili nekako najvece rupe u resenju. Bilo bi lepo ako bi moglo i bez trigera, li sta da radimo, ne moze uvek. ako nista, integritet podataka ne zavisi od trigera

Mislim da time sto je omoguceno brisanje jednog, poslednjeg zapisa...nakon njegovog brisanja, moguce je brisanje i drugog...i brisanje cele istorije
Citat:
Ako neko bas hoce da obrise sve promene nad izabranim artiklo, red po red, to je OK, verovatno ima razloga za to. I plus, ta se akcija moze uhvatiti u nekoj drugoj istoriji. Cilj je da se ne dozvoli slucajno nenamerno brisanje.

Ako je neko pogresio, treba da postoji trag njegove korekcije...odnosno ako je konstatovano da je poslednje stanje pogresno uneseno onda isto treba ponistiti (koregovati) sa novim zapisom suprotne vrednosti.
a brisanje unazad ne omogucuje taj trag, a hvatanje te akcije u nekoj drugoj istoriji je nesto drugo i narusava osnovnu ideju.
Slicno ovom rekla bih da i update poslednjeg zapisa u stvari bi trebalo da se vrsi preko novog inserta storniranog iznosa i zatim inserta sa novom koregovanom, ispravnom vrednoscu.
Kao zakljucak, bi bilo da DELETE i UPDATE nad tabelom treba da su REVOKE.
Deo DELETE i UPDATE su spreceni referencijalnim integritetom dizajna tabele, ali posto za poslednji uneseni zapis za dati artikl to ne vazi...a trebalo bi, jer tu je rupa. Kao sto se moze obrisati i menjati roditelj koji nema decu, tako i u ovm dizajnu poslednji zapis za dati artikl je roditelj bez dece, al kad njega obrisete, neki drugi zapis postaje roditelj bez dece...i u krugu...moguce je brisanje svega.

[ Zidar @ 18.04.2011. 15:44 ] @
Citat:
Slicno ovom rekla bih da i update poslednjeg zapisa u stvari bi trebalo da se vrsi preko novog inserta storniranog iznosa i zatim inserta sa novom koregovanom, ispravnom vrednoscu.
Kao zakljucak, bi bilo da DELETE i UPDATE nad tabelom treba da su REVOKE.

Nemam nista protiv, moze i tako. Tacno, to je stroziji uslov. U tom slucaju, dovoljno je ovo (svi ostali trigeri treba da se uklone):

Code:

CREATE TRIGGER trg_StanjeNaLageru_NoDelUpd ON StanjeNaLageru
FOR DELETE, UPDATE
AS
        ROLLBACK 
GO

Ja ovakav triger obicno stavljam na istorijske tabele 'u pozadini'. Verujem da je metod poznat mnogima- na DELEET ili UPDATE table MyTable ide triger koji prepise sve iz Inseretd u tabelu MyTable_History. Tabela MyTable_History ima triger koji radi ROLLBACK na svaki pokusaj UPDATE ili DELETE. Ako vec nemmaom MyTable_History, nego history vodimo u samoj tabeli, onda ima smisla zabraniti UPD/DEL. A i nekkao je u skladu sa knjigovodstvom - greske se ne ispravlajju brisanjem nego upisivanjem nove transakcije koja koriguje stanje.

Hvala Nadi na zapazanju Lepo je kad neko obraca paznju na ovo sta pricamo, da ne promakne bespotrebno poneka glupost.






[ Zidar @ 26.04.2011. 14:07 ] @
Spomenuo sam na pocetku da smo nesto slicno vec probali, i to u MS Acces. Access je u odnsou na MS SQL malo kabast za ovakve stvari, ali moze da prodje. http://www.elitesecurity.org/t...davanje-serijskih-brojeva-bazu