Ograniczenie powtarzalności do określonej liczby sql server

0

Mam pewien problem z ograniczeniem tabeli.
Stworzyłem 3 tabele, towar_tab, klient_tab, zam_tab
i w tabeli zam_tab istenieją takie pola:

zam_id (PK)
zam_towar_id (FK)
zam_klient_id (FK)

Szukałem w sieci, ale nie wiem dokładnie jak określić problem.

A mianowicie chciałbym, aby dany klient mógł tylko zamawiać 3 razy, w tabeli zam_tab. (czyli zam_klient_id moze byc tylko 3 razy użyty dla tej samej wartości klucza FK).
oraz, aby nie mogł zamawiać tego samego towaru 2x lub 3x.

Czy jest jakiś sposób abym mogł to ograniczyć. W sql server managment studio, ewentualnie w kodzie?

1

Co do pierwszego to możesz stworzyć trigger, który będzie to sprawdzał przy wstawianiu rekordu, być może też da się do zrobić check constraint'em.
Trigger może wyglądać mniej więcej tak:

CREATE TRIGGER
    ON zam_tab
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @clientId;
    DECLARE Inserted_Cursor CURSOR FOR
        SELECT zam_klient_id FROM Inserted;

    OPEN Inserted_Cursor;
    FETCH NEXT FROM Inserted_Cursor INTO @clientId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (SELECT COUNT(*) FROM zam_tab WHERE zam_klient_id = @clientID) >= 3
        BEGIN
            RAISERROR('Klient nie może złożyć większej liczby zamówień!', 16, 1);
            ROLLBACK TRAN;
        END

        FETCH NEXT FROM Inserted_Cursor INTO @clientId;
    END
END

Co do drugiego to możesz ustawić PK na wszystkich 3 polach tabeli - wtedy klient nie będzie mógł 2 razy zamówić tego samego, bo każdy wpis będzie musiał być unikalny.

0

Tą drugą sprawę załatwiłem poprzez If exists

ale ten trigger wyrzuca błąd:

Msg 16915, Level 16, State 1, Procedure ogranicznik, Line 8
A cursor with the name 'Inserted_Cursor' already exists.
The statement has been terminated.

tzn. trigger działa dla pierwszego insertu a pozniej nazwa inserted_cursor jest zajęta.

dopisałem local przy powoływaniu kursora tj.

CREATE TRIGGER
    ON zam_tab
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @clientId;
    DECLARE Inserted_Cursor CURSOR LOCAL FOR
        SELECT zam_klient_id FROM Inserted;
 
    OPEN Inserted_Cursor;
    FETCH NEXT FROM Inserted_Cursor INTO @clientId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (SELECT COUNT(*) FROM zam_tab WHERE zam_klient_id = @clientID) >= 3
        BEGIN
            RAISERROR('Klient nie może złożyć większej liczby zamówień!', 16, 1);
            ROLLBACK TRAN;
        END
FETCH NEXT FROM Inserted_Cursor INTO @clientId;
    END
END
  

wtedy nie było błędu o powtórzeniu nazwy ale o niezamknięciu czyli:

Msg 16905, Level 16, State 1, Procedure ogranicznik, Line 21
The cursor is already open.

więc dodałem procedure zamykającą po END i wtedy zapytanie wykonuje się w nieskończoność jakby sie zapętliło.


CREATE TRIGGER
    ON zam_tab
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @clientId;
    DECLARE Inserted_Cursor CURSOR LOCAL FOR
        SELECT zam_klient_id FROM Inserted;
 
    OPEN Inserted_Cursor;
    FETCH NEXT FROM Inserted_Cursor INTO @clientId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (SELECT COUNT(*) FROM zam_tab WHERE zam_klient_id = @clientID) >= 3
        BEGIN
            RAISERROR('Klient nie może złożyć większej liczby zamówień!', 16, 1);
            ROLLBACK TRAN;
        END
        close Inserted_Cursor;
FETCH NEXT FROM Inserted_Cursor INTO @clientId;
    END
        close Inserted_Cursor;
END
        close Inserted_Cursor;
 
1

Zapomniałem, ale wystarczy go zamknąć na końcu i być może po rollback trans - nie mam czasu sprawdzić.

0

Ok. Wszystko gra ;) Dziękuje bardzo za podpowiedzi.

0

Gdzieś słyszałem że w MS za użycie kursora ucinają ręce;) Alternatywa bez kursora:

CREATE TRIGGER
    ON zam_tab
    FOR INSERT, UPDATE
AS
BEGIN
        IF EXISTS(SELECT zam_klient_id FROM zam_tab WHERE zam_klient_id in (select zam_klient_id from inserted) group by zam_klient_id having COUNT(zam_klient_id) >= 3) 
        BEGIN
            RAISERROR('Klient nie może złożyć większej liczby zamówień!', 16, 1);
            ROLLBACK TRAN;
        END
END

Co nie znaczy że tego nie można obejść, jeżeli inserted będzie niepoprawne, szczególnie przy update

0

Dlaczego Inserted przy update ma być nie poprawne?
Gdzie to wyczytałeś o tych łapkach? Bo kursora to używają w przykładach na MSDN?

0
byku_guzio napisał(a):

Dlaczego Inserted przy update ma być nie poprawne?

Inserted zawsze będzie poprawne bo to nie wynika z konstrukcji kursora/triggera, a silnika sql. Jednak przyznaje się to moja nadinterpretacja w tym wypadku, chyba upał mi zaszkodził...

byku_guzio napisał(a):

Gdzie to wyczytałeś o tych łapkach? Bo kursora to używają w przykładach na MSDN?

O tych łapkach to akurat słyszałem na jakiejś konferencji związanej z SQL Serverem, ogólnie znany żart w środowisku DBA.

Co do użycia kursora to chodzi o pewną zasadę, nie używaj jeśli jesteś wstanie zastąpić go zwykłym zapytaniem, dlaczego:
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx

http://www.sqlteam.com/article/cursor-performance

1 użytkowników online, w tym zalogowanych: 0, gości: 1