[es] - Uslovna kontrola pri upisu

Zidar
Canada

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

+79 Profil

^{08.03.2011. u 15:01 - pre 159 meseci}

Citat:

Interesuje me moze li se dodati TRIGGER ili CONSTRAINT nad zadatom tabelom koji bi to kontrolisao?

Odlicno pitanje i odlicno razmisljanje. Bravo. Moze da se resi i u triggeru i sa constraint. naravno, CONSTRAINT ima prednost. Evo kako se radi. Da napomenem da sav kod pisem direktno u poruku, mozda ponegde neka sintaksa ne proradi odmah, ali cilj nije Cut/Paste nego da se razume sta se desava. Ovako dakle:

1) Parvilo (ogranicenje) koje zelimo da ispostujemo u posmatranoj tabeli:

Citat:

ukoliko slog koji se insertuje u tabelu u jednoj koloni ima odredjenu vrijednost (recimo kupac = 'FIZICKO LICE')
da onda neka druga kolona u tom slogu (recimo "ime") ne moze biti prazna (blank) a za ostale vrijednosti
kolone "kupac" da dozvoljava da "ime" bude prazno?

2) Analiza problema: Ovde imamo odnos dve kolone [Kupac] i recimo [Ime]. Obe kolone mogu imati razlciite vrednosti, i za odredjene kombinacoje zelimo da obezbedimo da se postuje uslov koji smo nveli u 1). Vrednosti koje nas interesuju su:

([Kupac] = 'FIZICKO LICE') ili (NOT ([Kupac] = 'FIZICKO LICE' ))

([Ime] IS NULL) ili (NOT ([Ime] IS NULL) )

Znaci, imamo po jedan iskaz za svaku od dve kolone koje posmatramo, i koji moze biti TRUE ili FALSE. Za svaki izkaz imamo po dve vrednosti. Ukupan broj mogcih kombinacija je 2*2 = 4. Napisimo kombinacije i da vidimo koja je zadovoljava nase postavljeno pravilo, u obliku logickog AND. Proveravamo dakle sta se desava kad izvrsimo

([Ime] IS NULL) AND (NOT ([Ime] IS NULL) )

Code:

Kupac = 'Fizicko lice'      [Ime] IS NOT NULL       Parvilo zadovoljeno
------------------------------------------------------------------
TRUE                                  TRUE                  TRUE
TRUE                                  FALSE                 FALSE
FALSE                                 TRUE                  TRUE -- ovo nije definisamno pravilom
FALSE                                 FALSE                 TRUE

prevedeno na govorni jezik, ovo znaci:

(Kupac je fizicko lice) i istovremeno (IME nije NULL) = to je OK => (Pravilo zadovoljeno) = TRUE
(Kupac je fizicko lice) i istovremeno (IME jeste NULL) = to se ne dozvoljava => (Pravilo zadovoljeno) = FALSE
(Kupac nije fizicko lice) i istovremeno imamo ime => to deluje OK, iz pravila se u stvari ne vidi da li je ovo dozvoljeno ili ne, pa sam pretpostavio sdda ako nije explicitno zabranjeno, ond mora da je OK
(Kupac nije fizicko lice) iistovremeno nemamo ime => to je u skaldu sa pravilom (a za ostale vrijednosti
kolone "kupac" da dozvoljava da "ime" bude prazno) => (Pravilo zadovoljeno) = TRUE

Iz analize zakljucujemo da je pravilo prekrseno samo u jednom slucaju, a to je kada je kupac fizicko lice, a nemamo ime. To se u CHECK constraint moze iskazati ovako:

Code:

ALTER TABLE MojaTabela WITH CHECK
ADD CONSTRAINT ck_Kupac_Fizicko_Lice_Mora_imati_ime
CHECK NOT ((Kupac = 'Fizicko lice' ) AND ([Ime] IS NULL) )
;

Medjutim, ako preformulisemo pravilo, pa sada ono glasi:
"Ako je kupac 'fizicko lice', onda mora da postoji ime, ali ako kupac nije fizicko lice, onda ime ne sme da postoji"
onda tablca za nalizu izgleda malkice drugacije, i CHECK constraint izgledace malkice drugacije. Ovako:

Tablica istinitosti za moguce vrednosti logiskih izkaza:

Code:

Kupac = 'Fizicko lice'      [Ime] IS NOT NULL       Parvilo zadovoljeno
------------------------------------------------------------------
TRUE                                  TRUE                  TRUE   -- fizcko lice i imamo ime, tako i treba
TRUE                                  FALSE                 FALSE -- fizcko lice, a nemamo ime, ovo ne moze
FALSE                                 TRUE                  FALSE -- nije fizicko lice a imamo ime, ne moze ni ovo
FALSE                                 FALSE                 TRUE  -- nije fizicko lice i nema ime, tako i treba da bude

CHECK constraint:

Code:

ALTER TABLE MojaTabela WITH CHECK
ADD CONSTRAINT ck_Kupac_Fizicko_Lice_Mora_imati_ime_ili_je_ime_NULL
CHECK (1 =  CASE
                     WHEN (Kupac = 'Fizicko lice' )  AND ([Ime] IS NULL)   THEN 0
                     WHEN (Kupac <> 'Fizicko lice' )  AND ([Ime] IS NOT NULL)   THEN 0
                     ELSE 1
                 END)
;

CASE je moglo da se napise i drugacije, da se navedu tacni slucajevi, a ELSE 0, to je stvar ukusa i trenutnog raspolozenja.

U svakom slucaju, kad imate pravilo koje se odnosi na dve ili vise kolona, postupak je da se napravi tablica svih mogucih kombinacija za kolonu 1 i kolonu 2, neke kombinacije narusavaju pravilo, a neke ne. Uvek mozete da napisete CASE koji lista sve kombinacije i vraca 0 ili 1 za FALSe i TRUE. Posto nas interesuje samo 0 ili 1, onda pisemo CASE sto krace moze.

Ceo postupak - napisi pravilo => napravi tablicu istinitosti => napisi CONSTRAINT moze (i treba) da se napise u dokumentaciji. Bas kao sto inzenjeri napisu staticki proracun i priloze crteze, pa onda neko po tome napravi zgradu.

Nadam se da je pomoglo.

Odgovor na temu