Witajcie,

Mam 3 tabele:
sprzet: id_sprzetu, nazwa, indeks, JIM, ilosc
pomieszczenia: id_pomieszczenia, nr_pomieszczenia, opis
lok_sprzetu: id_l_s, id_sprzet, id_pom, iloscloksprzetu, uwagi

W tabeli sprzet mam informacje ile sprzętu i w jakiej ilości mam na stanie (obciążeniu).
W tabeli lok_sprzetu mam dane jaki sprzęt w jakiej ilości znajduje sie w danym pomieszczeniu.

Chciałbym teraz na stronie głównej mieć info - jaka ostatnia operacja została przeprowadzona w bazie danych tzn. delete, update, insert - czego dotyczyła i kiedy to miało miejsce? Już wiem, że powinna być tabela historyczna i triggery.
Zacząłem coś kombinować:

[SQL] pobierz, plaintext 
CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON `lok_sprzetu`
FOR EACH ROW BEGIN
    INSERT INTO `lok_sprzetu_history` (id,id_sprzet,id_pom,iloscloksprz,id_miary,uwagi,change_type,change_date) 
    VALUES
    (OLD.id,OLD.id_sprzet,OLD.id_pom,OLD.iloscloksprz,OLD.id_miary,OLD.uwagi,0,NOW());
END;
[SQL] pobierz, plaintext 

Nie wiem czy to dobry trop?

Trigger - trop dobry. Tylko to. Reszta do poprawki.
Mając tabelę taką:
`lok_sprzetu_history` (id,id_sprzet,id_pom,iloscloksprz,id_miary,uwagi,change_type,change_date)
w triggerze możesz skopiować cały rekord do niej. i OK. Ale... załóżmy, że zmieniło ci się tylko pole id_sprzet. Kopiujesz cały rekord stary. Teraz aby wykazać jakie zmiany zaszły to musisz tak:
1. Znaleźć w tej tabeli ostatni rekord (w tej strukturze brakuje jakiegoś autonumeru, bo przecież dany rekord może być wiele razy modyfikowany, więc samo id nic nie daje) dla danego id.
2. porównać WSZYSTKIE pola z tego rekodu ze WSZYSTKIMI polami rekordu bieżącego
3. Wtedy masz informację, które pola OSTATNIO były zmienione.
Trochę to skomplikowane, nieprawdaż...
A może takie rozwiązanie:
1. tabela:
`lok_sprzetu_history` (auto_id, id, field, old_value, new_value, change_user, change_date)
auto_id - autoinc
id - id zmienianego rekordu (z tabeli lok_sprzetu)
field - nazwa zmienianego pola
old_value, new_value - poprzednia wartosc i aktualna wartosc
change_user i change_date - zmieniacz i data zmieniacza
2. przykładowe dane w tabeli:
1, 1, id_sprzet, 1, 10, user1, 2013-01-01 12:34:56
2, 1, id_pom, 1, 2, user1, 2013-01-01 12:34:56
3, 1, id_pom, 2, 1, user2, 2013-01-02 22:33:44
3. troche skomplikowany trigger wychodzi, bo mniej więcej taki:
IF OLD.id_sprzet<>NEW.id_sprzet THEN
INSERT INTO lok_sprzetu_history(id, field, old_value, new_value, change_user, change_date)
VALUES (OLD.id, 'id_sprzet', OLD.id_sprzet, NEW.id_sprzet, NEW.change_user, NEW.change_date);
END IF;
IF OLD.id_pom<>NEW.id_pom THEN
INSERT INTO lok_sprzetu_history(id, field, old_value, new_value, change_user, change_date)
VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom, NEW.change_user, NEW.change_date);
END IF;
...
Dla każdego interesującego cię pola musisz to napisać. Robisz to raz i masz spokój. Do zmiany struktury. Wtedy ten fragment dopisujesz w triggerze i po sprawie.
W tym pierwszym przypadku ze zmianą struktury wiąże się zmiana struktury obu tabel (historii również). Oczywiście na czas zmiany struktury w obu przypadkach powinieneś triggery wyłączyć (postgreSQL) lub zdropować.

W takim układzie zapisujesz sobie zmiany tylko interesujących cię rekordów (zmiana np. timestampa cię nie obchdzi). W tej pierwszej też, ale też musisz napisać IFy.
Każde rozwiązanie ma swoje zady i walety...
Wadę "mojego" rozwiązania jest to, że pola old_vlue i new_value muszą być na tyle uniwersalne, by pomieściły każde inne pole z tabeli. Czyli TEXT.
I dodatkowo w tabeli lok_sprzetu powinny być pola change_user i change_date modyfikowalne z kodu przy każdej zmianie. Wtedy masz info kto ostatnio zmieniał bez 'zaglądania do historii'.
Rozwiązanie to daje również szybki dostęp do np. informacji kto jakie pola i ile razy zmieniał... Zresztą plusó jest więcej... tylko trzeba tego triggera napisać... Ja mam na to sposób i kilka lat temu napisałem do najważniejszych tabel. rozwiązanie działa do dziś. i wszyscy są zadowoleni...

Dziękuję za informacje i metody, które mi bardzo mam nadzieję pomogą.
W mojej ewidencji nie ma użytkowników, wszystkie dane wprowadzam i modyfikuje sam. Więc pola userów odchodzą.
Natomiast kiedy aktualizuję sprzęt w różnych pomieszczeniach to po pewnym czasie nie pamietam czy dokonałem zmian w pomieszczeniu x czy y i co zmieniłem. Dlatego ten raport będzie mi potrzebny plus mozliwośc odtworzenia stanu sprzed jakiejs daty.



Rozwiązanie powyższe czy coś innego?



Proszę o pomoc. Tabela lok_sprzetu_history wygląda tak:

http://zapodaj.net/94578d47ed893.png.html

Czy coś trzeba zmienic?

Trigger:

[SQL] pobierz, plaintext 
IF OLD.id_sprzet<>NEW.id_sprzet THEN
INSERT INTO lok_sprzetu_history(id, FIELD, old_value, new_value, change_date)
VALUES (OLD.id, 'id_sprzet', OLD.id_sprzet, NEW.id_sprzet,  NEW.change_date);
END IF;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
IF OLD.id_pom<>NEW.id_pom THEN
INSERT INTO lok_sprzetu_history(id, FIELD, old_value, new_value,  change_date)
VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom,  NEW.change_date);
END IF;
[SQL] pobierz, plaintext 

Takie rozwiązanie u mnie działa. No, może bardzo podobne - nazwy pól są inne, nazwy pól są w grawisach (zwłaszcza że nazwa FIELD jest zarezerwowana) oraz przy porównniu zastosowałem Coalesce (bo nulla nie można porównac przez =), czyli:

[SQL] pobierz, plaintext 
IF Coalesce(OLD.id_pom,0)!=Coalesce(NEW.id_pom,0) THEN
INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`,  `change_date`)
VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom,  NEW.change_date);
END IF;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON `lok_sprzetu`
FOR EACH ROW BEGIN
 IF Coalesce(OLD.id_pom,0)!=Coalesce(NEW.id_pom,0) THEN
    INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`)
    VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom, NEW.change_date);
    END IF;
[SQL] pobierz, plaintext 

Błąd SQL syntax. Motam się ze składnią...

DELIMITER //
CREATE TRIGGER...

[SQL] pobierz, plaintext 
Wygląda na TO, że w twoim zapytaniu SQL jest błąd. W znalezieniu przyczyny problemu może pomóc także - jeśli się pojawi - poniższy opis błędu serwera MySQL.
 
ERROR: Nieznany znak przestankowy @ 11
STR: //
SQL: DELIMITER //CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON `lok_sprzetu`
FOR EACH ROW BEGIN
 IF Coalesce(OLD.id_pom,0)!=Coalesce(NEW.id_pom,0) THEN
    INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`)
    VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom, NEW.change_date);DELIMITER //CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON `lok_sprzetu`
FOR EACH ROW BEGIN
 IF Coalesce(OLD.id_pom,0)!=Coalesce(NEW.id_pom,0) THEN
    INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`)
    VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom, NEW.change_date);DELIMITER //CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON `lok_sprzetu`
FOR EACH ROW BEGIN
 IF Coalesce(OLD.id_pom,0)!=Coalesce(NEW.id_pom,0) THEN
    INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`)
    VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom, NEW.change_date);
 
 
zapytanie SQL:
 
DELIMITER //CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON `lok_sprzetu` FOR EACH ROW BEGIN IF Coalesce(OLD.id_pom,0)!=Coalesce(NEW.id_pom,0) THEN INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`) VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom, NEW.change_date);
 
MySQL zwrócił komunikat: Dokumentacja
#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 'DELIMITER //CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON' at line 1 
[SQL] pobierz, plaintext 

Brakuje END IF; i END;
Działająca całość (zaremowana):

[SQL] pobierz, plaintext 
/*
drop table lok_sprzetu
CREATE  TABLE `test`.`lok_sprzetu` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_pom` INT NOT NULL,
  `id_sprzet` INT NOT NULL,
  `iloscloksprzetu` INT NOT NULL,
  `uwagi` TEXT NULL,
  `change_date` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
 
CREATE TABLE `test`.`lok_sprzetu_history`
(
  `autoid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id` INT NOT NULL ,
  `field` VARCHAR(250),
  `old_value` TEXT,
  `new_value` TEXT,
  `change_date` DATETIME
);
 
 
delimiter //
CREATE TRIGGER `lok_sprzetu_history_update_trigger` BEFORE UPDATE ON `lok_sprzetu`
FOR EACH ROW BEGIN
 
IF Coalesce(OLD.id_pom,0)!=Coalesce(NEW.id_pom,0) THEN
    INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`)
    VALUES (OLD.id, 'id_pom', OLD.id_pom, NEW.id_pom, NEW.change_date);
END IF;
IF Coalesce(OLD.id_sprzet,0)!=Coalesce(NEW.id_sprzet,0) THEN
    INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`)
    VALUES (OLD.id, 'id_sprzet', OLD.id_sprzet, NEW.id_sprzet, NEW.change_date);
END IF;
IF Coalesce(OLD.iloscloksprzetu,0)!=Coalesce(NEW.iloscloksprzetu,0) THEN
    INSERT INTO lok_sprzetu_history(`id`, `field`, `old_value`, `new_value`, `change_date`)
    VALUES (OLD.id, 'iloscloksprzetu', OLD.iloscloksprzetu, NEW.iloscloksprzetu, NEW.change_date);
END IF;
 
END;
*/
INSERT INTO lok_sprzetu(id_sprzet, id_pom, iloscloksprzetu) VALUES(1,1,1), (2,2,2), (3,3,3);
 
UPDATE lok_sprzetu SET id_sprzet=id_sprzet+1, id_pom=id_pom-1;
 
SELECT * FROM lok_sprzetu_history;
[SQL] pobierz, plaintext 

Nie wiem co jest grane, ale nie "wchodzi". Wywala błędy.


phpMyAdmin - 2.10.3
Wersja klienta MySQL: 5.0.51a

Może nie masz praw do tworzenia triggerow? Nie wiem, bo komunikatu błędu nie podałeś... Powyższy kod NA PEWNO działa. Sprawdzałem na moim lokalu w MyWorkbench

[SQL] pobierz, plaintext 
Błąd
 
zapytanie SQL:
 
DROP TABLE lok_sprzetu CREATE TABLE `test`.`lok_sprzetu` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id_pom` INT NOT NULL ,
`id_sprzet` INT NOT NULL ,
`iloscloksprzetu` INT NOT NULL ,
`uwagi` TEXT NULL ,
`change_date` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
MySQL zwrócił komunikat: Dokumentacja
#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 'CREATE TABLE `test`.`lok_sprzetu` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY ' at line 2 
[SQL] pobierz, plaintext 

Kurde, małpa... normalnie małpa. Przepisałeś bez sensu. Masz choć bazę `test`?
zamiast:

[SQL] pobierz, plaintext 
DROP TABLE lok_sprzetu CREATE TABLE `test`.`lok_sprzetu` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id_pom` INT NOT NULL ,
`id_sprzet` INT NOT NULL ,
`iloscloksprzetu` INT NOT NULL ,
`uwagi` TEXT NULL ,
`change_date` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
[SQL] pobierz, plaintext 

daj

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `lok_sprzetu` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id_pom` INT NOT NULL ,
`id_sprzet` INT NOT NULL ,
`iloscloksprzetu` INT NOT NULL ,
`uwagi` TEXT NULL ,
`change_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
[SQL] pobierz, plaintext 

Małpa jak diabli... Tabele są już OK, ale nieszczęsny trigger ciągle:


ERROR: Nieznany znak przestankowy @ 11

Już patrzyłem w manualu i za diabły,,,

Mysql Server 5.6 śmga aż miło! Trigger działa. Wielkie, wielkie dzieki!

Kontynuując wątek działającego triggera chciałbym wyświetlićw tabeli info jaki sprzęt został zmieniony, kiedy i z jakiej wartości na jaką.

Zrobiłem nowy widok historia gdzie w tabeli wyświetlam dane:
id, stara wartość, nowa wartość, kiedy zmiana. Teraz mam $query = "SELECT * FROM jos_lok_sprzetu_history";
Wszystko ładnie, tylko jak w zapytaniu uzyskać nazwę sprzętu zmienianego? Na razie mam: 642, 300, 60, 2013-10-28 14:27:00.
Chciałby powiązać id z tabeli jos_lok_sperzetu_history z id_l_s jos_lok_sprzetu i uzyskać nazwę sprzętu.

połącz joinem te pola.
Ale musisz uważać. Bo jeśli we field jest, że pole `id` się zmieniło, to kicha... (ale takich sytuacji nie powinno być)

No właśnie kombinuję z joinem, ale jakieś błędy i bzdury wychodzą.

Pokż dane z obu tabel dla jedngo rekordu. I pokaż co kombinujesz...

Przykład dla rekordu o id=642

jos_lok_sprzetu
http://www.fotosik.pl/pokaz_obrazek/a421ca369562013f.html


jos_lok_sprzetu_history
http://www.fotosik.pl/pokaz_obrazek/8cfaebaef7211abd.html

[SQL] pobierz, plaintext 
 $query = "SELECT id.a, field.a, old_value.a, new_value.a, kiedy_zmiana.a, id_sprzet.b, nazwa.c
	  FROM jos_lok_sprzetu_history
	  LEFT JOIN jos_sprzet as sprzet b.id_sprzet=sprzet.nazwa
	  LEFT JOIN jos_lok_sprzetu as lok a.id=lok.id_sprzet";
[SQL] pobierz, plaintext 

Bez sensu te zapytanie.
Motam się jak ryba w sieci.

Nie wiem czy to w ogóle możliwe. Chciałbym zrobić tabelę gdzie byłyby takie informacje.

Ostatnie zmiany w systemie:

Pomieszczenie - 500 sprzęt - komputer x stara wartość 23 - nowa wartość - 20

Zaczynasz od rekordó, które na pewno masz. Czyli z tabel: jos_sprzet i jos_lok_sprzetu. A skoo masz, to laczysz JOINem. Czyli:

[SQL] pobierz, plaintext 
SELECT * /* nie pisz * - dla uproszczenia dalem */
	  FROM jos_sprzet AS sprzet 
	  JOIN jos_lok_sprzetu AS lok ON lok.id_sprzet=sprzet.id_sprzet
[SQL] pobierz, plaintext 

Jak to sie zgadza, to dodajesz tabele ze zmianami (ale moze tych zmian nie byc, wiec LEFT JOIN)

[SQL] pobierz, plaintext 
SELECT sprzet.nazwa, sprzet.id_sprzet, lok.nazwa AS nazwa_lokalu, hist.FIELD, hist.old_value, hist.new_value, hist.kiedy_zmiana
	  FROM jos_sprzet AS sprzet 
	  JOIN jos_lok_sprzetu AS lok ON lok.id_sprzet=sprzet.id_sprzet
	  LEFT JOIN jos_lok_sprzetu_history AS hist ON  hist.id=sprzet.id_l_s
[SQL] pobierz, plaintext 

PS. Sorry, jeśli coś źle pisze, ale pisze po omacku, a na laptopie mi klawiatura szwankuje (lew strona zjada, 'W', 'A', 'E' i 'R') Taka sytuacja...

Hmmm...
Takie zapytanie nie działa. Może coś źle wytłumaczyłem.

Cała baza to 5 tabel (link na końcu).
W tabeli jos_pomieszczenia przechowuję numery i opisy wszystkich pomieszczeń.
W tabeli jos_sprzetu mam cały sprzęt jaki jest na moim obciążeniu. Np. lampki - 24 szt. Ten cały sprzęt jest gdzieś porozdzielany n aposzczególne pomieszczenia.
Tabela jos_lok_sprzętu przedstawia lokalizację sprzętu w poszczególnych pomieszczeniach.
Tabela jos_miary to rodzaj miary czyli sztuka, m2.
Tabela jos_lok_sprzetu_history - historia zmian.

Model:

http://www.fotosik.pl/pokaz_obrazek/ee2053f453ef5c54.html

mmmmmmm możesz pomóc?