Blokowanie UPDATE w triggerze Opcje

[Nexces]

Witam,
sprawa ma się następująco:
mam dwie tabele o identycznej strukturze:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `trtest1` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `value` varchar(30) character SET utf8 collate utf8_polish_ci NOT NULL,
  `ser` int(11) UNSIGNED NOT NULL,
  `time` int(11) NOT NULL,
  `info` text character SET utf8 collate utf8_polish_ci,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
[SQL] pobierz, plaintext 

różnią się tylko nazwą...
mam trigger'a który w momencie modyfikacji wiersza/wierszy z pierwszej tabeli poprzednie "wersje" przenosi do drugiej (śledzenie zmian swoiste)

pole `ser` które jest w tym przypadku numerem seryjnym nie może zostać w żasdnym przypadku zmodyfikwane...
pókico wyprodukowałem coś takiego:

[SQL] pobierz, plaintext 
DELIMITER //
CREATE TRIGGER `test`.`backup` BEFORE UPDATE ON `test`.`trtest1`
 FOR EACH ROW begin IF new.ser!=old.ser then
SET new.info:=concat_ws('\n',
old.info,
'------------------------------------',
NOW(),
'Próba modyfikacji numeru seryjnego!!',
'------------------------------------\n'
);
SET new.ser:=old.ser;
SET new.value:=old.value;
SET new.time:=old.time;
else 
INSERT INTO trtest2 (value,ser,time,info) SELECT value,ser,time,info FROM trtest1 WHERE id=NEW.id;
SET new.time=UNIX_TIMESTAMP();
end IF;
end
//
DELIMITER ;
[SQL] pobierz, plaintext 

sprawuje się genialnie natomiast jest to app rozwijany ciągle i muszę mieć możliwość dość lekkiego dodawania kolejnych kolumn do tych tabel (w tej chwili jest raptem 20)...

zastanawiam się czy można tak opisać tego triggera, żeby:
po 1:
w przypadku próby zmiany numeru seryjnego zablokował akcję modyfikując tylko pole info
lub
przywrócił poprzednie wartości wszystkim kolumnom (w tej chwili jest tak to zrobione, jednak jest to o tyle niewygodne, że każde pole muszę osobno wpisać, a chcę tego uniknąć)

po 2:
wygodniejsze kopiowanie... znowuż w tej chwili muszę wszystkie pola wypisać, bo id jest auto_increment
insert into trtest2 select * from trtest2;
daje niezbyt przyjemny efekt w postaci dublowania klucza...

z góry dzięki za wszelkie propozycje (:

[Cezar708]

zamiast TRIGGERA w takim przypadku proponuję napisać ZASADĘ (ang RULE), nadpisującą UPDATE, czyli:

[SQL] pobierz, plaintext 
CREATE RULE update_trtest1 AS UPDATE ON UPDATE TO trtest1
DO
  INSERT INTO trtest1(id, value, time, info)
  VALUES (new.id, new.value, new.time, new.info)
;
[SQL] pobierz, plaintext 

zauważ, że nie ma w `nowym` insercie ani słowa o `ser`, więc nie będzie nadpisany.

PS: Ja to robiłem na PostgreSQL nie wiem czy składnia jest poprawna dla MySQL, ewentualnie trochę wg specyfikacji MySQL

[Nexces]

mysql chyba niezbyt potrafi coś takiego... poza tym :
- wygląda na to, że rekord nie zostanie wypchnięty d drugiej tabeli
- brak przechwycenia informacji o próbie modyfikacji
- następuje insert i nowy rekord, a nie faktyczny update... (zapomniałem dodać, że `ser` czyli numer seryjny jest unique i musi być unique w pierwszej tabeli, nie może się zdażyć sytuacja, że będą dwa rekordy z tym samym numerem seryjnym w pierwszej tabeli)
- dodatkowo mimo wszystko muszę wypisać kolejno kolumny... czego jak pisałem chciałbym uniknąć...

[Cezar708]

mysql chyba niezbyt potrafi coś takiego...

.. no fakt, też nic na ten temat nie znalazłem (może warto przejść na PostgreSQL?)

- wygląda na to, że rekord nie zostanie wypchnięty d drugiej tabeli

a dlaczego nie, to jest tylko ON UPDATE więc Twój insert działa dokładnie tak jak działał

- brak przechwycenia informacji o próbie modyfikacji

nie ma problemu, możesz i tę informację funkcjonalność do roli dopisać, żaden problem

- następuje insert i nowy rekord, a nie faktyczny update... (zapomniałem dodać, że `ser` czyli numer seryjny jest unique i musi być unique w pierwszej tabeli, nie może się zdażyć sytuacja, że będą dwa rekordy z tym samym numerem seryjnym w pierwszej tabeli)

faktycznie mój błąd, w roli po prostu zmień INSERT (...) na UPDATE (...) SET (...)

- dodatkowo mimo wszystko muszę wypisać kolejno kolumny... czego jak pisałem chciałbym uniknąć...

hmm tu można się zastanowić nad jedną sprawą, w MySQL jest coś takiego jak SHOW FIELDS (czy tam COLS lub FOR EACH ROW, nie pamiętam dokładnie) a potem poiterować po tego wyniku i odseparować tylko `ser`.. nie wiem czy da radę, nigdy nie próbowałem, może warto pomyśleć

Natomiast Twoje podejście ma jedną wadę, co by nie było UPDATE i tak się wykona.. więc jak ktoś by chciał zmienic ser to i tak by się zmienił.

Może również rozpatrzysz jedno podejście, tylko znowu nie jestem pewien, czy MySQL da sobie z tym radę
1. Użytkownikowi który łączy się z bazą poprzez skrypt PHP odbierasz prawa UPDATE do tej tabeli
2. Tworzysz widok, składający się tylko z kolumn, różnych od `ser`
3. Tworzysz ROLE lub TRIGGERA, na UPDATE (wiem, wiem, widoków nie można updatować, ale można na nie założyć triggery lub role, na pewno w PostgreSQL, w MySQL musisz sprawdzić tę możliwość)
4. Użytkownik łączący się przez skrypt może tylko wykonywać "UPDATE na WIDOKU", więc odcinasz go od możliwości zniszczenia kolumny `ser`

... nie wiem, czy pomogłem, ale niestety z MySQL zatrzymałem się w rozwoju, od dłuższego czasu go nie używam. Jeśli to nie jest problem to przenieś się na PostgreSQL i te wszystkie wymienione tutaj sprawy na pewno tam załatwisz,

Pozdrawiam

[Nexces]

nie będę się przesiadał na inny system bazodanowy, którego absolutnie nie znam z tytułu jednej napotkanej niewygody (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)

dwa... nie mogę się przesiąść... to nie jest domowy projekcik (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)

zastanawiam się jeszcze czy nie dało by się transakcji zatrudnić do obsłużenia tego "przywrócenia" wiersza...
z tym, że po raz N-ty musiałbym się przebijać przez doc'sy do mysql'a i ćwiczyć...

próbowałem procedurki potworzyć, ale jak zobaczyłem jak mysql marnie sobie radzi z "odgadywaniem" moich intencji to zostawiłem to w cholerę na inny czas...

no nic... wątek zostawiam otwarty, a póki co opiszę tego triggera korzystająć z mojego początkowego pomysłu...

a i jeszcze jedno... nie mogę zdjąć uprawnienia UPDATE bo to app w phpie naskrobany i jakoś tak nie szczególnie chce mi się bawić w przelogowanie user'a do bazy na konkretne akcje...

trigger z tym przechwytem jest tylko po to, żeby ktoś kto nie daj boże ominął zabezpieczenia wewnętrzene appa nie mógł jakiejś dewastacji popełnić za dużej (zamierzam jeszcze parę rzeczy dorzucić do informacji o próbie zmiany numeru seryjnego)...

ps:
zawsze można php'a zatrudnić do sprawdzenia poprawności wszystkiego, ale wydaje mi się zbyteczne przeżucanie danych mysql<->php po parę razy żeby stwierdzić, że wszystko jest ok... mało wydajne...