MSSQL: view, trigger INSTEAD OF UPDATE - problem
Jacek Osiecki - 01-08-2006 01:50
MSSQL: view, trigger INSTEAD OF UPDATE - problem
Witam,
Mam problem ze zbudowaniem w MSSQLu takiego view, które by prawidłowo
działało również przy zapisie...
Najpierw opiszę strukturę bazy - przechowuje ona dane o pracownikach
i o specjalizacjach do których mają uprawnienia: do wykonywania prac
i/lub do prowadzenia szkoleń.
Zbudowane to jest na trzech tabelach - tu w uproszczeniu:
Tabela pracownicy:
id INT (primary key)
nazwisko nvarchar(16)
Tabela specjalizacje:
id INT (primary key)
opis nvarchar(32)
Wszystko łączę tabelą pracownicy_specjalizacje:
id_pracownika INT
id_specjalizacji INT (primary key na (id_pracownika, id_specjalizacji))
uprawnienia BIT
szkoleniowiec BIT
Baza ma być użyta jako źródło danych dla programu w C#, więc potrzebuję
dla każdego pracownika listę wszystkich specjalizacji i możliwość
zaznaczenia
czy ma uprawnienia do wykonywania i/lub szkolenia w ramach danej
specjalizacji.
Rozwiązuję to przy pomocy view pr_ps, opartego na takim query:
SELECT
pr.id AS id_pracownika,
sp.id AS id_specjalizacji,
sp.opis AS specjalizacja,
dbo.truefalse(ps.uprawnienia) as uprawnienia,
dbo.truefalse(ps.szkoleniowiec) as szkoleniowiec
FROM
dbo.pracownicy AS pr
CROSS JOIN
dbo.specjalizacje AS sp
LEFT OUTER JOIN
dbo.pracownicy_specjalizacje AS ps
ON ps.id_pracownika = pr.id AND ps.id_specjalizacji = sp.id
Do tego momentu wszystko super - ładnie działa.
dbo.truefalse to prosta funkcja, która przy argumencie NULL zwraca false
a przy innym - wartość argumentu:
CREATE FUNCTION dbo.truefalse (@arg AS bit)
RETURNS BIT
AS
BEGIN
IF @arg IS NULL
RETURN 0
RETURN @arg
END
Nie wiem, może da się to prościej (w samym query do view), ale nie potrafię
a dokumentacja MSDN do MSSQLa nie wspomina o możliwości używania
jakichkolwiek
instrukcji warunkowych w zapytaniach :(
Ponieważ trudno żeby można było normalnie operować na takim view jak na
tablicy,
stworzyłem trigger typu INSTEAD OF UPDATE:
CREATE Trigger ps_update
ON dbo.pr_ps
INSTEAD OF UPDATE
AS
BEGIN
DECLARE @ipr int
DECLARE @isp int
DECLARE @iup int
DECLARE @isz int
select @ipr = id_pracownika, @isp = id_specjalizacji,
@iup = uprawnienia, @isz = szkoleniowiec from inserted
if @iup IS NULL
SET @iup = 0
if @isz IS NULL
SET @isz = 0
DECLARE @justupdate int
SELECT @justupdate = count(*) FROM pracownicy_specjalizacje
WHERE id_pracownika = @ipr AND id_specjalizacji = @isp
if @justupdate > 0
begin
UPDATE pracownicy_specjalizacje
SET uprawnienia = @iup, szkoleniowiec = @isz
WHERE id_pracownika = @ipr AND id_specjalizacji =
@isp
end
else
begin
INSERT INTO pracownicy_specjalizacje
(id_pracownika, id_specjalizacji, uprawnienia,
szkoleniowiec)
VALUES (@ipr, @isp, @iup, @isz)
end
END
Czyli - jeśli jest wpis w pracownicy_specjalizacje to go update'uje, a jeśli
nie ma
to go tworzy.
No dobra, teraz mogę w końcu przejść do sedna. Problem polega na tym, że co
prawda
zapytania typu update działają dobrze (wpisy w pracownicy_specjalizacje są
tworzone
tudzież zmieniane), ale... jest dziwny efekt:
UPDATE pr_ps
SET uprawnienia = 1
WHERE (id_pracownika = 1) AND (id_specjalizacji = 3)
Wynik:
"2 rows affected by last query".
Jakim cudem "2 rows", skoro update'ował (a tak naprawdę stworzył) tylko
jeden wiersz?
Co więcej, to samo się dzieje gdy zmieniam wpis dla kombinacji
(id_pracownika,
id_specjalizacji), która już ma odpowiedni rekord w
pracownicy_specjalizacje.
Nie bardzo to rozumiem - może o czymś nie wiem i np. można na zakończenie
triggera
zwrócić taką ilość "affected rows" jaką uważam za stosowne?
Przy okazji - właśnie zobaczyłem, że mój trigger marnie działa gdy
przychodzi update'ować
więcej wierszy na raz, np. przy:
UPDATE pr_ps
SET uprawnienia = 1
WHERE (id_pracownika = 1)
MSSQL zwraca "4 rows affected by last query", mimo że powinny zostać wpisane
3 rekordy
(jest na razie 3 pracowników i 3 specjalizacje).
Pewnie będę musiał w triggerze zrobić jakąś pętlę (o ile mi się uda ;) - w
sumie będę
wdzięczny jeśli i tu mi ktoś pomoże :)
Mam nadzieję że ktoś dotarł do końca i będzie mógł mi z tym pomóc...
Chwilowo jestem w kropce i nie wiem nawet dlaczego...
Pozdrawiam,
--
Jacek Osiecki joshua@ceti.pl GG:3828944
"Poglądy polityczne mają takie znaczenie w sejmie jak upierzenie u krokodyla"
(c) Tomasz Olbratowski 2004
Maciej Łuszczyński - 01-08-2006 01:51
> CREATE FUNCTION dbo.truefalse (@arg AS bit)
> RETURNS BIT
> AS
> BEGIN
> IF @arg IS NULL
> RETURN 0
> RETURN @arg
> END
>
Tematu nie zglebilem, ale tutaj mozna naprawde prosciej i szybciej:
SELECT ISNULL(@arg, 0)
Pozdrawiam,
Macko
--
*** A czym się różni Cray od normalnego peceta?
*** Tym, że Cray wykonuje pętle nieskończone w 10 sekund.
Jacek Osiecki - 01-08-2006 01:51
Dnia Mon, 31 Jul 2006 13:25:42 +0200, Maciej Łuszczyński napisał(a):
>> CREATE FUNCTION dbo.truefalse (@arg AS bit)
>> RETURNS BIT
>> AS
>> BEGIN
>> IF @arg IS NULL
>> RETURN 0
>> RETURN @arg
>> END
> Tematu nie zglebilem, ale tutaj mozna naprawde prosciej i szybciej:
> SELECT ISNULL(@arg, 0)
O, dzięki - zawsze to krok do przodu :)
A ja nadal mam nadzieję, że ktoś będzie w stanie mi pomóc w głównej
kwestii...
Pozdrawiam,
--
Jacek Osiecki joshua@ceti.pl GG:3828944
"Poglądy polityczne mają takie znaczenie w sejmie jak upierzenie u krokodyla"
(c) Tomasz Olbratowski 2004
Jacek Osiecki - 04-08-2006 00:24
Dnia Mon, 31 Jul 2006 09:48:30 +0000 (UTC), Jacek Osiecki napisał(a):
> Mam problem ze zbudowaniem w MSSQLu takiego view, które by prawidłowo
> działało również przy zapisie...
[ciach]
> No dobra, teraz mogę w końcu przejść do sedna. Problem polega na tym, że co
> prawda
> zapytania typu update działają dobrze (wpisy w pracownicy_specjalizacje są
> tworzone
> tudzież zmieniane), ale... jest dziwny efekt:
>
> UPDATE pr_ps
> SET uprawnienia = 1
> WHERE (id_pracownika = 1) AND (id_specjalizacji = 3)
>
> Wynik:
>
> "2 rows affected by last query".
No to skoro nikt nie odpisał - pozwolę sobie dodać, że sprawę rozwiązuje
dopisanie na początku triggera:
SET NOCOUNT ON;
Natomiast problem z update'owaniem tylko jednego wiersza rozwiązałem może w
rzeźniczy, ale działający sposób:
DECLARE @tablica TABLE(<definicja kolumn jak w VIEW>)
INSERT INTO @tablica SELECT <istotne kolumny VIEW> FROM inserted
WHILE (SELECT count(*) FROM @tablica) > 0
BEGIN
SELECT <potrzebne kolumny> FROM tablica
<Tu robię to co miałem robić>
DELETE FROM @tablica WHERE <kolumny ustalające jednoznacznie wiersz>
END
Nie wiem czy da się prościej - pewnie można by próbować z tworzeniem kursora
itp, ale kombinacji dużo więcej a przykładom MSDNowym niestety daleko do
ideału...
Pozdrawiam,
--
Jacek Osiecki joshua@ceti.pl GG:3828944
"Poglądy polityczne mają takie znaczenie w sejmie jak upierzenie u krokodyla"
(c) Tomasz Olbratowski 2004
Paweł Filipiak - 04-08-2006 01:22
"Jacek Osiecki" <joshua@ceti.pl> wrote in message
news:slrnecrkfh.i3o.joshua@tau.ceti.pl...
[...]
1. problem z mylnym wskazaniem "affected rows" już rozwiązałeś - tak jak
zresztą zaleca ms w bol w opisie polecenia "create view" w sekcji "view
limitations"
"If variable assignment must occur in a trigger, use a SET NOCOUNT statement
at the beginning of the trigger to eliminate the return of any result sets.
"
2. Problem z działaniem triggera przy modyfikowaniu więcej niż jednego
wiersza wynika z faktu, że stosujesz konstrukcję "select @id = id from
inserted...", która nie zadziała według oczekiwań, gdy w tabeli "inserted"
będzie więcej niz 1 wiersz - co prawda mssql nie zgłasza wówczas błędu
(niektóre inne bazy owszem), natomiast w zmiennej ląduje pierwszy wybrany
wiersz - można powiedzić, że losowy (nie specyfikujesz jawnie porządku
sortowania przy wybieraniu danych)
3. Twoje rozwiązanie "rzeźnicze" można spokojnie zamienić na kursor (efekt
będzie podobny, obróbka wiersz po wierszu), ale można to spróbować zastąpić
dwoma podzapytaniami (kody pisane w notepadzie, więc niekoniecznie
zadziałają po przeklejeniu, jednak sama idea wydaje mi się poprawna):
/*najpierw aktualizujemy istniejące rekordy*/
update pracownicy_specjalizacje set
pracownicy_specjalizacje.uprawnienia = inserted.uprawnienia
pracownicy_specjalizacje.szkoleniowiec = inserted.szkoleniowiec
from inserted
where inserted.id_pracownika = pracownicy_specjalizacje.id_pracownika
and inserted.id_specjalizacji = pracownicy_specjalizacje.id_specjalizacji
/*teraz wstawiamy rekordy, których nie ma w pracownicy_specjalizacje. W tym
celu robimy select
z tabeli inserted złączonej zewnętrznie z pracownicy_specjalizacje i
łączymy po polach, po których rozpoznajesz, czy rekord jest czy go nie ma w
pracownicy_specjalizacje.
Wybieramy te rekordy, które mają null w id_pracownika (założenie niejawne,
ze pole to ma nałożone ograniczenie "not null", jeżeli masz w
pracownicy_specjalizacje
rekordy z id_pracownika równym null to poniższa konstrukcja zwiedzie)
Nie można tu uzyc konstrukcji "not in (...)" ponieważ warunkiem złączenia są
dwa pola - zresztą dla pojednycznczego pola
taka konstrukcja jest optymalniejsza - warunek "in" jest jednym z
najbardziej obciążających mssql*/
insert into pracownicy_specjalizacje (id_pracownika, id_specjalizacji,
uprawnienia,szkoleniowiec)
select i.id_pracownika, i.id_specjalizacji, i.uprawnienia,i.szkoleniowiec
from inserted i
left join pracownicy_specjalizacje ps on ps.id_pracownika = i.id_pracownika
and ps.id_specjalizacji = i.id_specjalizacji
where ps.id_pracownika is null
/*w where mozna tez uzyc ps.id_specjalizacji is null - najlepiej tego pola,
na którym masz indeks*/
--
pozdrawiam,
Paweł Filipiak
Jacek Osiecki - 04-08-2006 01:23
Dnia Wed, 2 Aug 2006 21:44:13 +0200, Paweł Filipiak napisał(a):
> "Jacek Osiecki" <joshua@ceti.pl> wrote in message
> news:slrnecrkfh.i3o.joshua@tau.ceti.pl...
> [...]
> 1. problem z mylnym wskazaniem "affected rows" już rozwiązałeś - tak jak
> zresztą zaleca ms w bol w opisie polecenia "create view" w sekcji "view
> limitations"
> "If variable assignment must occur in a trigger, use a SET NOCOUNT statement
> at the beginning of the trigger to eliminate the return of any result sets.
> "
No tak - nie doczytałem tego...
> 2. Problem z działaniem triggera przy modyfikowaniu więcej niż jednego
> wiersza wynika z faktu, że stosujesz konstrukcję "select @id = id from
> inserted...", która nie zadziała według oczekiwań, gdy w tabeli "inserted"
> będzie więcej niz 1 wiersz - co prawda mssql nie zgłasza wówczas błędu
I długo właśnie kombinowałem, aż doszedłem do mojego rzeźniczego rozwiązania :)
> 3. Twoje rozwiązanie "rzeźnicze" można spokojnie zamienić na kursor (efekt
> będzie podobny, obróbka wiersz po wierszu), ale można to spróbować zastąpić
> dwoma podzapytaniami (kody pisane w notepadzie, więc niekoniecznie
> zadziałają po przeklejeniu, jednak sama idea wydaje mi się poprawna):
Hmm, ciekawe - jakoś o tym nie pomyślałem, wstyd się przyznać...
Dzięki za podpowiedź, spróbuję to zrobić właśnie w ten sposób :)
Pozdrawiam,
--
Jacek Osiecki joshua@ceti.pl GG:3828944
"Poglądy polityczne mają takie znaczenie w sejmie jak upierzenie u krokodyla"
(c) Tomasz Olbratowski 2004
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[MSSQL2000] Problem z =?ISO-8859-2?Q?tabel=B1/indeksem/zapytanie?==?ISO-8859-2?Q?m_czy_b=B3=B1d_w_bazie_danych=2E=2E=2E?=
mysql i mysql-front, problem
String line; if (line=="cos"){...}....problem
Problemy z =?ISO-8859-2?Q?instalacj=B1_PostgreSQL_na_syste?==?ISO-8859-2?Q?mach_Windows?=
[postgres] Problem z =?ISO-8859-2?Q?zmian=B1_struktury_i_z?==?ISO-8859-2?Q?ale=BFno=B6ciami=2E?=
[oracle] =?ISO-8859-2?Q?zmia=BFd=BFony_przez_problem=3A_za?==?ISO-8859-2?Q?pytanie_do_hierarchi?=
Problem z wartościami w MySQL :( [ MySQL and ASP and VBScript ]
[PGSQL] czy ktos mial problemy z initdb pgsql 8.1 ?
[MySQL] Problem z zapisem danych w bazie danych
Problem z mysql - can't connect to MySQL/nietypowo...
zanotowane.pldoc.pisz.plpdf.pisz.plmorebeer.opx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
