Ograniczenie powtarzalności do określonej liczby sql server

Odpowiedz Nowy wątek
2012-06-18 18:55

Rejestracja: 8 lat temu

Ostatnio: 6 lat temu

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?

Pozostało 580 znaków

2012-06-18 19:18

Rejestracja: 9 lat temu

Ostatnio: 2 lata temu

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.


edytowany 1x, ostatnio: byku_guzio, 2012-06-18 19:19

Pozostało 580 znaków

2012-06-18 20:34

Rejestracja: 8 lat temu

Ostatnio: 6 lat temu

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;
edytowany 7x, ostatnio: LukaszC89, 2012-06-18 21:25
Gdzieś słyszałem że w MS za użycie kursora ucinają ręce;) Alternatywa bez kursora w odpowiedzi bo komentarzu nie ma znaczków kodu... - Panczo 2012-06-18 23:26

Pozostało 580 znaków

2012-06-18 21:33

Rejestracja: 9 lat temu

Ostatnio: 2 lata temu

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


Pozostało 580 znaków

2012-06-18 21:53

Rejestracja: 8 lat temu

Ostatnio: 6 lat temu

0

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

Pozostało 580 znaków

2012-06-18 23:29

Rejestracja: 17 lat temu

Ostatnio: 2 minuty temu

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

Pozostało 580 znaków

2012-06-19 01:00

Rejestracja: 9 lat temu

Ostatnio: 2 lata temu

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?


Pozostało 580 znaków

2012-06-19 09:03

Rejestracja: 17 lat temu

Ostatnio: 2 minuty temu

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/sqlpr[...]sors-with-set-operations.aspx

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

Od roku pracuję z sql server, rąk mi w firmie nie ucinają, ale krzywią się okrutnie - hyde 2012-06-22 14:47

Pozostało 580 znaków

Odpowiedz

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

Robot: Bingbot