Mam dość ciekawy problem do rozwiązania.
Potrzebuję napisać trigger do mysql'a wykonywanego za każdym update na jednej tabeli... Ale, żeby nie było za prosto, cała operacja ma wyglądać tak:
1. Select na jednej tabeli, w wyniku jedno pole z ID rekordu (zwykły select, zwraca kilkaset rekordów).
2. Dla każdego ze zwróconych rekordów kolejny select, gdzie kluczem jest właśnie pobrana wartość z pierwszego zapytania 1, zwracane jest jedno pole z wartością INTEGER.
3. Update na dwóch tabelach z ID z pierwszego zapytania i wartością otrzymaną z wyniku z drugiego zapytania zapytania.
Więc problemem dla mnie jest zrobienie odpowiednich pętli, gdzie wynik zapytań będzie przekazywany do kolejnych zapytań...
Całość w VB6 wygląda tak:
sSQL = "Select KitID FROM tblstockkits"
rsS.OpenRs sSQL, cnM, adOpenStatic, adLockReadOnly
Do While Not rsS.EOF
sSQL = "SELECT MIN(((tblstock.StockQty - tblstock.DueOut) / tblstockkitsconts.Qty)) as Available " & _
"FROM (`roads-prod`.tblstockkitsconts tblstockkitsconts INNER JOIN`roads-prod`.tblstock tblstock " & _
"ON (tblstockkitsconts.StockID = tblstock.StockID)) INNER JOIN`roads-prod`.tblstockkits tblstockkits " & _
"ON (tblstockkits.KitID = tblstockkitsconts.KitID) Where tblstockkits.KitID = " & rsS.Fields(0)
rsK.OpenRs sSQL, cnM, adOpenStatic, adLockReadOnly
If Not rsK.EOF Then
If rsK.Fields(0) > 0 Then
sSQL = "UPDATE tblstockkits SET FreeStock = " & Int(rsK.Fields(0)) & " WHERE KitID = " & rsS.Fields(0)
cnM.Execute sSQL
sSQL = "UPDATE tblebayitems SET FreeStock = " & Int(rsK.Fields(0)) & " WHERE KitID = " & rsS.Fields(0)
cnM.Execute sSQL
End If
rsK.CloseRecordset
rsS.MoveNext
Loop
rsS.CloseRecordset
Obecnie całość tej pętli zajmuje ok 4-6s (wykonuje około 650 update'ów), celem jest przerzucenie tej roboty na serwer.
Co powiesz na takie rozwiązanie:
1. Utworzenie widoku , opartego na zapytaniu:
CREATE VIEW Widok1 AS
SELECT
tblstockkitsconts.KitID
,MIN(((tblstock.StockQty - tblstock.DueOut) / tblstockkitsconts.Qty)) as Available
FROM tblstockkitsconts tblstockkitsconts
INNER JOIN tblstock tblstock
ON tblstockkitsconts.StockID = tblstock.StockID
INNER JOIN tblstockkits tblstockkits
ON tblstockkits.KitID = tblstockkitsconts.KitID
(bez warunku WHERE, dla wszystkich KitID)
2. Wewnątrz triggera dwa UPDATE:
UPDATE tblstockkits SET FreeStock = Widok1.Available FROM tblstockkits JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID
i drugie analogicznie.
Zapytanie do triggera zwraca błąd:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tblstockkits JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID' at line 3
i faktycznie jakoś dziwnie wygląda...
Piszę trochę na "sucho", bo nie mam dostępu do serwera MySQL, a na dodatek już minęło trochę czasu, odkąd miałem do czynienia z jego składnią :)
Chodzi mi w każdym razie o wykonanie UPDATE na podstawie złączenia z zewnętrzną tabelą/widokiem.
Spróbuj:
UPDATE tblstockkits
JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID
SET FreeStock = Widok1.Available FROM tblstockkits
To jest zgodne ze składnią na forum MySQL:
http://forums.mysql.com/read.php?97,45724,45724
Mi cały czas to "FROM" nie pasuje, zakończyłem tak:
CREATE TRIGGER bar AFTER UPDATE ON tblstock
FOR EACH ROW BEGIN
UPDATE tblstockkits JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available;
UPDATE tblebayitems JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available FROM tblstockkits ;
END
DELIMITER ;
Jak widzisz, w 3 lini skasowałem "FROM", ale teraz dostaję dziwny error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
Czyli z pierwszym update'em dalej coś nie tak (z drugim też, ale jeszcze do tego nie doszedł).
Widok zrobiłem wg Twojego przepisu i działa (chyba) poprawnie.
Co ciekawe, update poza triggerem działa ok (ale zmienia tylko jeden rekord, co być może jest dobrze, nie mam jak sprawdzić w tej chwili).
Cytat: krzyszp w 04 Grudzień 2012, 14:05
Co ciekawe, update poza triggerem działa ok (ale zmienia tylko jeden rekord, co być może jest dobrze, nie mam jak sprawdzić w tej chwili).
To może wywołania UPDATE umieścisz wewnątrz kodu Visual Basic?
Czy przeniesienie pętli na serwer SQL miało na celu skrócenie czasu wykonania?
Myślę, że te dwa UPDATE pójdą błyskawicznie . Pętle działały wolno, ponieważ zapytanie, które umieściłem w widoku wykonywane było tyle razy, ile jest wierszy w tblstockkits.
Moje UPDATE redukują to do dwóch razy, niezależnie od zawartości tblstockkits.
Tak, chcę przenieść na serwer, bo to jest średnio 650 update'ow co kilka minut - za każdym razem, jak któryś z userów doda jakiś produkt (spośród 3k produktów) do zamówienia, zmienia się ilość towaru do wykorzystania (wolngo) na magazynie. Dużą rolę odgrywa tu właśnie używanie tzw. "kitów" - są to grupy produktów tworzące nowy produkt - więc za każdą zmianą ilości jakiegoś towaru musi być także aktualizowana wartość dla kitów, które zawierają ten produkt.
W efekcie powstaje "pętla w pętli w pętli" ;) i tę robotę chcę przenieść na serwer.
Spróbuj w takim razie zamienić kod z pętlami na moje dwa UPDATE. Liczba 650 zredukuje się do 2 :)
Jeśli tak czy inaczej będziesz chciał pracować raczej na serwerze, to już nie mogę Ci pomóc, bo nie mam serwera MySQL w podorędziu. Trzeba będzie trochę poszperać w internecie i poeksperymentować z definicją triggera, żeby zadziałał.
Generalnie dobry pomysł (przeniesienie tych update do vb i korzystanie z widoku.
Niemniej, pozostaje kwestia widoku - jednak nie wykonuje się ok... Mam tylko jeden rekord z niego, więc stany się nie przeliczają.
Bezprym, jak masz ochotę pomóc, to dam Ci dostęp do PHPMyAdmina u mnie na testowym serwerze z tą bazą...
Dodaj:
GROUP BY tblstockkitsconts.KitID
na koniec zapytania tworzącego widok - teraz widzę, że to pominąłem :)
Pewnie że pomogę, ale będę mógł dopiero za 2-3 godziny.
Poszły dane na PW - group pomógł, testuję teraz, czy wszystko się prawidłowo aktualizuje.
Niemniej, taki trigger jak napisałem na początku by mnie uszczęśliwił, bo bazując na nim wyeliminowałbym bardzo dużo zapytań z aplikacji, co przy połączeniu zdalnym robi ogromną różnicę.
Wykonałem takie polecenie:
delimiter |
CREATE TRIGGER ag_bar AFTER UPDATE ON tblstock
FOR EACH ROW BEGIN
UPDATE tblstockkits JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available;
UPDATE tblebayitems JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available ;
END
|
delimiter ;
i trigger został utworzony.
Widok zdaje się też działać prawidłowo.
Przetestuj, może mamy już to, czego szukamy :)
DZIAŁA!!!
Dzięki wielkie :)
No to super :p_arr: