[es] - Triger ili index za ograničenje zapisa polja

adopilot
Admir Hodžić
It menager
Sarajevo BiH

Član broj: 123492
Poruke: 134
217.199.133.*

Sajt: nemam ja to

Profil

Re: Triger ili index za ograničenje zapisa polja

^{08.02.2008. u 16:52 - pre 197 meseci}

Fedya je to bolje objasno:
za isti art_id mozes imati samo jedan koji je 'osnovni' i vise onih koji to nisu

Znači radi se o robnoj aplikaciji gdije je zahtjev bio da na jedan artikal ili kataloški broj možemo staviti više barkodova.

To smo riješili relacijama jedan art naspram više barkodova.

Nakon toga se je pojavio problem koji BARCODE prikazati na fakturi.
Ako postoji samo jedo polje rezervisano za barkod u reportu.
Nekada nova zaliha prekrije postojeću i onda oba bar koda ostaju aktivna
ali važno je da se prikaže tačno određeni koji je aktuelan.

U tu svrhu mi smo dodali i polje OSNOVNI koje se može markirati kroz grid osnovnih podataka na maski artikla
iz razloga da bi sql mogao pronači tačno taj barkod i isti prikazati na fakturi.
Kontrolu da za jedan artikal među barkodovima postoji samo jedan sa markerom osnovni ugradili smo u delphijovu aplikaciju
kontrola nažalos nije pokazala super pouzdanom pa se nekako u bazi pojave i artikli koji imaju dva bar koda a obadva osnovna.

Za posljedicu toga imao sam pucanje pojednih galerija gdije se bar kod dovlačio kao subquery.
Jer bi se pojavila dva barkoda.

Select
art,
naziv,
kol,
(select barcode from barcode b where b.art_id=l.art_id and osnovni=1) as barcode
sifra,...
from lager l where...

Ovo sam izbijegao dodavanjem funkcije MAX ispred barcode da bi dobio samo jedan rekord kao rezultat subquerya.

Ali da ne bih više strahovao kada će oni (klijenti) uspijeti izraditi aplikaciju i unjeti dva puta osnovni barkod u aplikaciju
ja sam mislio nastimati triger koji bi to kontrolisao.

Ali izgleda da je kompikovanije nego što sam mislio

S poštovanjem

Odgovor na temu

Zidar
Canada

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

+79 Profil

Re: Triger ili index za ograničenje zapisa polja

^{08.02.2008. u 17:20 - pre 197 meseci}

Pretpostavimo da ne mozes da menjas dizajn tabela, takav je kakav je. Imamo dakle problem da za jednu kombinaciju (Artikl, BarCode) dozvolimo samo jednom Osnovni = 1 i neogranicen broj redova sa Osnovni = 0. Moze dakle triger, a moze i stored procedura. Nasao sam i treci ancin - CHECK CONSTRAINT.

Malo poznata osobina MS SQL 2005 jeste da se u CHECK CONSTRAINTS mogu koristiti skalarne funkcije. N a osnovu definicije tabele koju si dao i pretpostavke "za jednu kombinaciju (Artikl, BarCode) dozvolimo samo jednom Osnovni = 1 i neogranicen broj redova sa Osnovni = 0" uradio sam sledece:

Code:

-- kreiramo tabelu:
CREATE TABLE BarKodovi
(
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ART_ID] [int] NULL,
[barcode] [varchar](30) NULL,
[faktor] [float] NULL,
[tip] [varchar](1) NULL,
[OSNOVNI] [int] DEFAULT 1 CHECK ([OSNOVNI] IN (1,0))
)
GO
-- napisemo funkciju koja prebrojava koliko se puta Osnovni=1 javlja za datu kombinaciju (Artikl, BarCode)
CREATE FUNCTION dbo.SamoJedanOsnovni(@Artikl_ID int, @BArCode varchar(30))
RETURNS int
AS
BEGIN
    DECLARE @BrojOsnovnih int

    SET @BrojOsnovnih = (SELECT COUNT(*) FROM BarKodovi
                        WHERE BarCode = @BArCode
                        AND [ART_ID] = @Artikl_ID
                        AND Osnovni = 1
                        )

    RETURN @BrojOsnovnih
END
GO
--- Sad dodamo constraint pre dodavanja ikakvih podataka u tabelu
--- Ako vec postoje neki podaci, ADD CONSTRAINT ce raditi samo ako su postojeci podaci u redu
ALTER TABLE BarKodovi
ADD CONSTRAINT BarKodovi_SamoJedanOsnovni CHECK (dbo.SamoJedanOsnovni([ART_ID],[barcode])=1)
GO
-- Sad cemo da testiramo resenje ---
-- Posto je tabela prazna, ovo bi trebalo da prodje:
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '12345',   1.6,    'A',     1 )
-- rezultat:
(1 row(s) affected)
-- naravno da je proslo:
SELECT * FROM BarKodovi
         ID      ART_ID barcode                                        faktor tip      OSNOVNI
----------- ----------- ------------------------------ ---------------------- ---- -----------
          1           1 12345                                             1.6 A              1

-- moze li jos jednom isti barcode, osnvni = 1?
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '12345',   1.6,    'A',     1 )
-- ne moze, vec imamo jeda :-)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "BarKodovi_SamoJedanOsnovni". The conflict occurred in database "Dejan", table "dbo.BarKodovi".
The statement has been terminated.

INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '12345',   1.6,    'A',     0 )
-- (1 row(s) affected)
-- jos jedan sa 0?
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '12345',   1.6,    'A',     0 )
(1 row(s) affected)

SELECT B.* , Retval = dbo.DaLiMozeOsnovni(ART_ID,barcode)
FROM BarKodovi AS B

-- Da unesemo novu kombinaciju, isti artikl, drugaciji bar kod, ali da je Osnovni = 0, a da pre toga nije bio OSnovni = 1:
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '55555',   1.6,    'A',     0 )
---
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "BarKodovi_SamoJedanOsnovni". The conflict occurred in database "Dejan", table "dbo.BarKodovi".
The statement has been terminated.
-- Super, ne dozvoljava da se unese prva instanca a da je Osnovni = 0
-- Probajmo na 'pravi nacin', prvo Osnovni=1 pa onda dalje:
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '55555',   1.6,    'A',     1 )
(1 row(s) affected)

-- onda jos 2-3 puta ovo:
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '55555',   1.6,    'A',    0 )

Bez trigera is tored procedura (daleko bilo !) dobili smo resenje koje za par (Artikl, BarCode):
a) dozvoljava da bude samo jedan Osnovni=1
b) dozvoljava neogranicen broj redova sa Osnovni=0
c) zahteva da prva instanca uvek bude Osnovni = 1 - nece da prihvati Osnovni = 0 ako ne postoji Osnovni = 1
I sve to radi na nivou tabele, sto obezbedjuje maksimalan stepen integritetta podataka

Jos uvek smatram da nesto nije u redu sa dizajnom tabela. Ovo mora da moze i drugacije i bilo bi bolje.

Samo sam hteo da pokazem sta se moze uraditi sa CHECK CONSTRAINTS i funkcijama. Budite an oprezu, ako vam se cini da bas nista drugo ne pomaze, nego morate da radite ovo sto sam upravo pokazao, verovatno imate problem negde u logickom dizajnu tabela.

Odgovor na temu

Zidar
Canada

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

+79 Profil

Re: Triger ili index za ograničenje zapisa polja

^{08.02.2008. u 17:48 - pre 197 meseci}

Dok sam spremao odgovor nisam video objasnjenj koje je Ado dao. No cini mi se da sam bio na pravom putu.

Na postojecim podacima probao sam
a) da ukolonim onog jednog koji je Osnovni=1, tako da ostanu samo oni koji su Osnovni=0. Proslo je bez problema.
Onda sam na to dodao jednog koji je Osnovni=1. Proslo bez problema.

b) onog koji je Osnovni=1 da UPDATE tako da postane Osnovni = 0 tako da su svi sada Osnovni = 0. Radilo je
Onda sam izabrao nekog drugo i proemnio mu Osnovni sa 0 na 1. I to je radilo.

Ovo znaci da je lako proglasiti nekog drugog za Osnovnog.

Sad losa vest:
Probao sam da jos jednog postojeceg pretvorim u Osnovni=1, da bih imao dva. Nazalost, proslo je. Dobro testiranej para vredi

Pogledacu da li ovo moze da se ispravi.

Odgovor na temu

Zidar
Canada

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

+79 Profil

Re: Triger ili index za ograničenje zapisa polja

^{08.02.2008. u 19:17 - pre 197 meseci}

Mislim da sam naso resenje, bez CHECK i bez funkcija.

Kreairao sam jednu izracunatu kolonu, PomocnaKolona , kojaje jednaka ID kad je Osnovni=0 i -1 kad je osnovni = 1
Ako napravim unique index CREATE UNIQUE INDEX IX_JedanOsnovni
ON BarKodovi ([ART_ID],[barcode],PomocnaKolona );
svaki pokusaj da se unese vise od jednog gde je OSnovni = 1 rezultirace duplikatom i index ce to da odbije.

Resenje dakle dozvoljava za bilo koji parv (Art_ID, BArcode):

a) neogranicen broj redova sa Osnovni = 0
b) mogu svi da budu Osnovni = 0
c) ne moze vise od jednog greda de je Osnovni = 1
d) dozvoljen je DELETE za bilo koji red
e) UPDATE gde se Osnovni=1 pretvara u Osnovni=1, potencijalni di=uplikat ne prolazi

Meni to lici na kompletno resenje.

Resenje sa CHECK constraint nije proslo, a dellovalo je obecavajuce. Kao da CHECK ne radi za UPDATE nego samo na INSERT.
Cudno, jer 'normalan' CHECK radi i za UPDATE naravno. U svakom slucaju, vredno je znati da se moze u CHECK ubaciti i funkcija koja poziva samu tabelu, ili cak neku drugu. Verovatno sam ja negde pogresio, raspitacu se pa cu javiti rezultat.

Na kraju, Zidareva teorema: the best code is no code at all

Ovako:

Code:

USE dejan
GO
DROP TABLE BarKodovi
GO
CREATE TABLE BarKodovi
(
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
,[ART_ID] [int] NULL
,[barcode] [varchar](30) NULL
,[faktor] [float] NULL
,[tip] [varchar](1) NULL
,[OSNOVNI] [int] DEFAULT 1 CHECK ([OSNOVNI] IN (1,0))
,PomocnaKolona  AS (CASE
                        WHEN Osnovni = 0
                            THEN ID
                            ELSE -1
                        END)
)
GO

CREATE UNIQUE INDEX IX_JedanOsnovni
    ON BarKodovi ([ART_ID],[barcode],PomocnaKolona );

-- Onda ovo jednom:
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '12345',   1.6,    'A',     1 )

-- onda nekoliko puta ovo:
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '12345',   1.6,    'A',     0 )

SELECT * FROM BarKodovi

         ID      ART_ID barcode                                        faktor tip      OSNOVNI PomocnaKolona
----------- ----------- ------------------------------ ---------------------- ---- ----------- -------------
          1           1 12345                                             1.6 A              1            -1
          2           1 12345                                             1.6 A              0             2
          3           1 12345                                             1.6 A              0             3
          4           1 12345                                             1.6 A              0             4
          5           1 12345                                             1.6 A              0             5
          6           1 12345                                             1.6 A              0             6

(6 row(s) affected)

-- da probamo d a dodamo duplikat:
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '12345',   1.6,    'A',     1 )
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.BarKodovi' with unique index 'IX_JedanOsnovni'.
The statement has been terminated.
-- ne moze, kao sto nije moglo ni pre :-)

-- Da sa UPDATE napravimo duplikat:

UPDATE  BarKodovi
SET Osnovni=1
WHERE ID = 6
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.BarKodovi' with unique index 'IX_JedanOsnovni'.
The statement has been terminated.
-- ne moze, znaci imamo potpuno resenje, bolje nego sa CHECK :-)

-- Da konvertujemo Osnovni u ne-Osnovni
UPDATE  BarKodovi
SET Osnovni=0
WHERE ID = 1
(1 row(s) affected)    -- OK, svi siu sad Osnovni=0

SELECT * FROM BarKodovi

         ID      ART_ID barcode                                        faktor tip      OSNOVNI PomocnaKolona
----------- ----------- ------------------------------ ---------------------- ---- ----------- -------------
          1           1 12345                                             1.6 A              0             1
          2           1 12345                                             1.6 A              0             2
          3           1 12345                                             1.6 A              0             3
          4           1 12345                                             1.6 A              0             4
          5           1 12345                                             1.6 A              0             5
          6           1 12345                                             1.6 A              0             6

(6 row(s) affected)

-- Da neki drugi sad proglasimo za Osnovni:
UPDATE  BarKodovi
SET Osnovni=1
WHERE ID = 5
(1 row(s) affected) -- i ovo radi :-)

-- da probamo jos jedan:
UPDATE  BarKodovi
SET Osnovni=1
WHERE ID = 3
-- nece moci :-)
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.BarKodovi' with unique index 'IX_JedanOsnovni'.
The statement has been terminated.

SELECT * FROM BarKodovi
         ID      ART_ID barcode                                        faktor tip      OSNOVNI PomocnaKolona
----------- ----------- ------------------------------ ---------------------- ---- ----------- -------------
          1           1 12345                                             1.6 A              0             1
          2           1 12345                                             1.6 A              0             2
          3           1 12345                                             1.6 A              0             3
          4           1 12345                                             1.6 A              0             4
          5           1 12345                                             1.6 A              1            -1
          6           1 12345                                             1.6 A              0             6

(6 row(s) affected)

-- Da dodamo novi par ID,BarCode, Osnovni=0 izvrsimo ovo 2-3 puta
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '555555',   1.6,    'A',     0 )

-- pa onda ovo (Osnovni=1)
INSERT INTO BarKodovi
        ([ART_ID],[barcode],[faktor],[tip],[OSNOVNI])
VALUES  (   1,     '555555',   1.6,    'A',     1 )
-- moze samo jednom, drugi put ne moze :-)

Odgovor na temu