Upotreba korisničkih funkcija u CHECK CONSTRAINTS

Wednesday, 05.03.2008 – Zidar

MS SQL  2000 doneo je jednu lepu novinu – mogućnost pisanja korisničkih funkcija. Uz to je tiho došla još jedna novina, slabo dokumentovana,  iako se može naći u Boks On Line. Korisničke funkcije mogu se pozvati iz CHECK constraints. Funkcije ko funkcije, mogu da pozivaju redove iz posmatrane tabele, ali i drugih tabela. Ovo nam omogućava da izbegnemo proceduralno programiranje (trigere) i da neka ograničenja za koje smo morali koristiti Stored Procedures ili čak front-end funkcije, sada možemo da postavimo na nivo tabele, tamo gde i treba.

Na jednostavnom primeru pokazaćemo kako to radi.

Dobrotvorna organizacija daje pomoc gradjanima. Svaki gradjanin ima svoj ‘racun’. Novac se isplacuje višekratno, u manjim iznosima. Nikome se ne sme isplatiti više od 100 dinara ukupno.

Krairana je tabela ‘Zaduzenja’ sa tri kolone – Transakcija, Racun, IznosZaduzenja.
IznosZaduzenja je ono sto se isplaćuje u jednoj transakciji. Ukupan zbir IznosZaduzenja po računu ne sme da predje 100.

Ovo su ograničenja koja želimo da postavimo:
Pretpostavljamo IznosZaduzenja mora biti pozitivan, ne mozž biti nula ili manji.
Ukupan zbir svih IznosZaduzenja po Racunu ne sme preći 100.

CREATE TABLE Zaduzenja — DROP TABLE Accounnt
(Transakcija int PRIMARY KEY
, Racun int NOT NULL
, IznosZaduzenja money NOT NULL CHECK (IznosZaduzenja > 0)
)

Ovako nasa tabela postuje  prvo ograničenje. Kako da sprečimo da se nekome isplati više od 100 dinara?

MS SQL, od verzije 2000 pa naovamo ima jednu malo poznatu osobinu – u CHECK constraints mogu se koristiti user defined functions.
Iskoristimo to. Napišimo prvo funkciju koja izračunava ukupno zaduženje po računu. Funkcija bi mogla ovako da izgleda:

CREATE FUNCTION dbo.UkupnoZaduzenje (@Racun int)
RETURNS money
AS
BEGIN
 DECLARE @Retval money
 SET @Retval = (SELECT SUM(IznosZaduzenja)
     FROM Zaduzenja WHERE Racun = @Racun
     )
 RETURN COALESCE(@Retval,0) — inace vraca NULL kad ne postoji ni jedan red u in line kveriju
END

Funkciju pozivamo ovako:
SELECT dbo.UkupnoZaduzenje(100)

Ovako pravimo CHECK constraint koji koristi nasu funkciju:


ALTER TABLE Zaduzenja
–DROP CONSTRAINT ck_UkupnoZaduzenjeManjeod100
ADD CONSTRAINT ck_UkupnoZaduzenjeManjeod100
 CHECK
  (
   100 >= dbo.UkupnoZaduzenje (Racun)
  )

Da vidimo da li radi:

INSERT INTO Zaduzenja VALUES (1,100,50)
(1 row(s) affected)


SELECT * FROM Zaduzenja
Transakcija Racun       IznosZaduzenja       
———– ———– ———————
          1         100               50.0000

(1 row(s) affected)

Dodajmo jos 25 dinara na isti racun:
INSERT INTO Zaduzenja VALUES (2,100,25)
(1 row(s) affected)

Sada imamo u tabeli Zaduzenja:
Transakcija Racun       IznosZaduzenja       
———– ———– ———————
          1         100               50.0000
          2         100               25.0000

Ukupno azduzenje za Racun=100 jeste 75.
Pokušajmo da dodamo 30 dinara, što bi podiglo ukupno zaduženje na 105.

INSERT INTO Zaduzenja VALUES (3,100,30)

Rezultat:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint ‘ck_UkupnoZaduzenjeManjeod100’.
The conflict occurred in database ‘master’, table ‘Zaduzenja’, column ‘Racun’.
The statement has been terminated.

Još 25 moze da prodje:
INSERT INTO Zaduzenja VALUES (3,100,25)

(1 row(s) affected)

i ništa vise:

INSERT INTO Zaduzenja VALUES (4,100,0.001)

Ponovo dobijamo
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint ‘ck_UkupnoZaduzenjeManjeod100’.
The conflict occurred in database ‘Zadaci’, table ‘Zaduzenja’, column ‘Racun’.
The statement has been terminated.

SELECT * FROM Zaduzenja

Transakcija Racun       IznosZaduzenja       
———– ———– ———————
          1         100               50.0000
          2         100               25.0000
          3         100               25.0000

(3 row(s) affected)

Ne moramo više da poštovanje ograničenja obezbeđujemo na front endu, nit SQL programiranjem (trigeri i stored procedure). Kod u funkciji nije proceduralni, ne maskiramo funkcijom nikakav kursor.  Onako kako i treba da bude.

🙂

  1. 2 Responses to “Upotreba korisničkih funkcija u CHECK CONSTRAINTS”

  2. Lep, jednostavan trik… baš kakvi trikovi i trebaju da budu 🙂

    Ista stvar može da se odraditi i u PostgreSQL-u. Sledeći kod je inspirisan Zidarovim tekstom i gotovo je identičan.

    CREATE TABLE zaduzenja (
    transakcija INTEGER NOT NULL PRIMARY KEY,
    racun INTEGER NOT NULL,
    iznoszaduzenja NUMERIC(12,2) NOT NULL CHECK (iznoszaduzenja > 0.00)
    );

    CREATE OR REPLACE FUNCTION ukupnozaduzenje (p_transakcija INTEGER, p_racun INTEGER) RETURNS NUMERIC AS
    $body$
    SELECT COALESCE(SUM(iznoszaduzenja), 0.00)
    FROM zaduzenja
    WHERE transakcija = $1
    AND racun = $2
    $body$
    LANGUAGE ‘sql’ VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

    ALTER TABLE Zaduzenja
    ADD CONSTRAINT ck_ukupnozaduzenjemanjeod100
    CHECK (ukupnozaduzenje(transakcija, racun) <= 100.00 – iznoszaduzenja);

    Razlika u kodu je jedino u definiciji i pozivu funkcije ‘ukupnozaduzenje’ koja u ovom slučaju ima dva ulazna parametra. Ovo se mora uraditi jer PostgreSQL proverava CHECK constraint pre nego se izvrši upis podatka. Ta činjenica dovodi do toga da funkcija pri računanju zbira nemože da uzme u obzir i novo uneti red, jer joj taj red još nije dostupan.
    Problem sam razrešio tako što sam od 100 oduzeo aktuelnu vrednost novounetog zaduženja.
    Ovo je opet prouzrokovalo nov problem – da se funkcija nemože definisati ako već postoje redovi, jer su joj tada svi redovi dostupni pri sabiranju.
    Ovaj drugi problem sam rešio tako što sam funkciji dodao još jedan parametar, i to je podatak o aktuelnoj transakciji, koju onda izuzimam iz sabiranja. Na ovaj način se napokon funkcija i njen poziv ponašaju ispravno.

    Ponoviću, zaista lep trik… a ja sad idem da se oslobodim nekih trigera 🙂

    By Srdjan on Mar 8, 2008

  3. Hvala Srdjanu na komentaru 🙂

    A ja baš pomislio da niko ne cita ovaj sajt. Ideju za trik sam pokupio u kjizi ‘Inside SQL Server 2005: SQL programming’, autor Itzik ben Gan i drugi. Ne smem da tvrdim da je rešenje sa UDF funkcijama efikasnije od trigera. Imam nameru da u nekoj skoroj budućnosti testiram ideju na malo većoj tabeli, pa da vidimo. U svakom slučaju, deluje mi jednostavnije za pisanje nego triger. Ako nista drugo, čini mi se ne moram da razmišljam da li će moći da odradi i multi-row insert. Često se lako napiše triger koji radi sa jednim redom, a više redova odjednom moze da bude problem. Nadam se da bar o tome ne moram da brinem.

    Uglavnom, eto ideje i svaki komentar ili kritika su dobrodošli 🙂

    By zidar on Mar 10, 2008

Post a Comment