Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

Computed DatumDo

[es] :: MS SQL :: Computed DatumDo

Strane: < .. 1 2 3

[ Pregleda: 6827 | Odgovora: 52 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: Computed DatumDo13.04.2011. u 19:34 - pre 157 meseci
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
 
Odgovor na temu

MarkoBalkan

Član broj: 141124
Poruke: 1624
188.125.17.*



+19 Profil

icon Re: Computed DatumDo13.04.2011. u 19:58 - pre 157 meseci
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]
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: Computed DatumDo13.04.2011. u 21:55 - pre 157 meseci
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

:-)
 
Odgovor na temu

mkaras
Marko Karas
Beograd

Član broj: 66087
Poruke: 427



+19 Profil

icon Re: Computed DatumDo13.04.2011. u 23:19 - pre 157 meseci
@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
 
Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*



+3 Profil

icon Re: Computed DatumDo14.04.2011. u 07:45 - pre 157 meseci
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)
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.dsl.bell.ca.



+79 Profil

icon Re: Computed DatumDo14.04.2011. u 12:21 - pre 157 meseci
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
 
Odgovor na temu

MarkoBalkan

Član broj: 141124
Poruke: 1624
188.125.2.*



+19 Profil

icon Re: Computed DatumDo14.04.2011. u 16:20 - pre 157 meseci
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.

 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: Computed DatumDo14.04.2011. u 21:36 - pre 157 meseci
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.

 
Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*



+3 Profil

icon Re: Computed DatumDo15.04.2011. u 12:25 - pre 157 meseci
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.
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: Computed DatumDo15.04.2011. u 15:31 - pre 157 meseci
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. :-)


:-)
 
Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*



+3 Profil

icon Re: Computed DatumDo18.04.2011. u 15:29 - pre 157 meseci
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.

 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: Computed DatumDo18.04.2011. u 15:44 - pre 157 meseci
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.






 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: Computed DatumDo26.04.2011. u 14:07 - pre 157 meseci
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

 
Odgovor na temu

[es] :: MS SQL :: Computed DatumDo

Strane: < .. 1 2 3

[ Pregleda: 6827 | Odgovora: 52 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.