MSSQL, Trigger, rejestrowanie zmian, tabele updated i inserted
AMP
Z MSSQL mam do czynienia od niedawna - wcześniej był Firebird.
Natknąłem się na taki oto problem: mam kilkadziesiąt tabel, zaistniała
potrzeba monitorowania wprowadzanych i edytowanych danych.
Całość zrobiłem na triggerach i działa. Chciałbym jednak zrobić coś
uniwersalnego - gdyż każda zmiana w bazie pociąga za sobą edycję
triggera.
Najprościej by było jeśli trigger wywołałby procedurę z 3 parametrami
- nazwa tabelki, tabela inserted,tabela updated. Niestety nie wiem jak
przekazać w parametrach tabele tymczasowe inserted/updated.
Robiłem też próby w samym triggerze - wylistowanie wszystkich pól,
odszukanie tych które się zmieniły:
<CODE>
BEGIN
DECLARE @tabela varchar(64)
-- tutaj nazwa tabeli
set @tabela = 'areas'
-- zmienne pomocnicze
DECLARE @colname varchar(50)
DECLARE @opmode varchar(1)
DECLARE @oldValue VARCHAR(1024)
DECLARE @newValue VARCHAR(1024)
DECLARE cur INSENSITIVE CURSOR FOR
select col.name from sysobjects obj
inner join syscolumns col on obj.id = col.id
where obj.name = @tabela
-- zmienna pomocnicza
set @opmode='M'
OPEN cur
-- analiza wszystkich kolumn
WHILE 1 = 1
BEGIN
FETCH cur INTO @colname
IF @@fetch_status <0 BREAK
-- pobranie starej wartosci i nowej
SELECT @oldValue = (SELECT @colname FROM Deleted)
SELECT @newValue = (SELECT @colname FROM Inserted)
if (@oldValue!=@newValue)
EXECUTE InsertLog 1,@tabela,@colname,@opmode,@newValue,@oldValue
END
DEALLOCATE cur
END
</CODE>
Działa, ale nie do końca:) gdyż instrukcje SELECT @oldValue... oraz
SELECT @newValue nie zwracają wartości kolumny, lecz jej nazwę. Czy
da sie temu jakoś zaradzić?
Moje pytania:
1. Jak zapisać SELECT @old... aby pobrały prawidłowe wartości -
ZAKŁADAM, że za każdym razem aktualizowany/wkładany jest 1 wiersz?
2. Czy i jak można to wszystko zapisać w procedurze i np w kilku
tabelkach w triggerach tylko wywołać EXECUTE nazwaproc
nazwa_tabelki,updated,inserted ?
3. Jak pobrać wewnątrz triggera nazwę tabeli której on dotyczy (aby
weliminować zapis w stylu set @tabela = 'areas')?
pozdrawiam
AMP
Grzegorz Gruza
Na pewno trzeba użyć dynamicznego SQLa. Coś w stylu:
SET @oldValue = EXEC('SELECT ' + @colname + ' FROM Deleted')
> Moje pytania:
> 1. Jak zapisać SELECT @old... aby pobrały prawidłowe wartości -
> ZAKŁADAM, że za każdym razem aktualizowany/wkładany jest 1 wiersz?
> 2. Czy i jak można to wszystko zapisać w procedurze i np w kilku
> tabelkach w triggerach tylko wywołać EXECUTE nazwaproc
> nazwa_tabelki,updated,inserted ?
> 3. Jak pobrać wewnątrz triggera nazwę tabeli której on dotyczy (aby
> weliminować zapis w stylu set @tabela = 'areas')?
Ja niedawno podszedłem do problemu zupełnie inaczej. Generuję kod
trigera na podstawie nazwy monitorowanej tabeli, który nie odwołuje się
do tabel systemowych, nie potrzebuje dynamicznego SQLa, kursorów itp.
Pozdrawiam
--
Grzegorz Gruza
Odpowiadając usuń "spamerom_nie." z adresu!!!
Aleksander Nabagło
AMP napisał(a):
> Witam!
> Z MSSQL mam do czynienia od niedawna - wcześniej był Firebird.
>
> Natknąłem się na taki oto problem: mam kilkadziesiąt tabel, zaistniała
> potrzeba monitorowania wprowadzanych i edytowanych danych.
> Całość zrobiłem na triggerach i działa. Chciałbym jednak zrobić coś
> uniwersalnego - gdyż każda zmiana w bazie pociąga za sobą edycję
> triggera.
> Najprościej by było jeśli trigger wywołałby procedurę z 3 parametrami
> - nazwa tabelki, tabela inserted,tabela updated. Niestety nie wiem jak
> przekazać w parametrach tabele tymczasowe inserted/updated.
Nie rozumien w czym problem?
Odpowiednie tabele nazywaja sie juz inserted/updated/deleted;
nazwa tabeli na ktorej trigger dotyczny nie moze byc zmienna,
skladnia definicji triggera okresla czy jest on przy/po/zamiast
oraz jaki rodzaj akcji wstawienie/zmiana/usuniecie.
>
> Robiłem też próby w samym triggerze - wylistowanie wszystkich pól,
> odszukanie tych które się zmieniły:
>
> <CODE>
>
Gdzie
Create or alter
trigger nazwa_triggera
on nazwa_tabeli
after delete
?
Triggery trzeba projektowac bardzo ostroznie;
kod powinien byc jak najprostszy i minimalny
(czyli bez kursorow);
Funkcjonalnosci dla roznych wariantow
for/after/instead of oraz on insert/update/delete
powinny byc napisane w osobnych triggerach;
w przeciwnym wypadku bardzo spadnie wydajnosc
albo nawet bedzie sie zakleszczac.
--
A
.
AMP
> Na pewno trzeba użyć dynamicznego SQLa. Coś w stylu:
> SET @oldValue = EXEC('SELECT ' + @colname + ' FROM Deleted')
>
To już testowałem. EXEC nie "widzi" tabeli Deleted....
> - Pokaż cytowany tekst -
Skoro tak się nie da w ostateczności będę musiał wszystkie zapytania
do bazy robić za pomocą procedur - ale wtedy wracam do na start - bo
znów będzie tyle procedur ile monitorowanych tabel.
AMP
> Create or alter
> trigger nazwa_triggera
> on nazwa_tabeli
> after delete
Nie wkleiłem - myślałem, że to oczywiste...
>
> Triggery trzeba projektowac bardzo ostroznie;
> kod powinien byc jak najprostszy i minimalny
> (czyli bez kursorow);
> Funkcjonalnosci dla roznych wariantow
> for/after/instead of oraz on insert/update/delete
> powinny byc napisane w osobnych triggerach;
> w przeciwnym wypadku bardzo spadnie wydajnosc
> albo nawet bedzie sie zakleszczac.
Cenna uwaga - w sumie tego nie napisałem, ale taki miałem zamiar...
Może znacie inne rozwiązanie (prostsze):
- jest tabela z 'logami' - w niej są nast. kolumny: When (datetime),
UserID (int), What (int), TableName (varchar(80)), ColName
(varchar(80)), OldValue (varchar(1024)), NewVelue(varchar(1024))
Czyli chcę podłączyć "monitorowanie" zmian w kilkunastu tabelach
(chodzi o pola tekstowe) - tak aby wiedzieć które pole kiedy zostało
zmodyfikowane - tak aby potem bardzo szybko znaleźć wszystko co
modyfikował w zadanym okresie użytkownik itp..
Może da się prosciej logować zmiany?
>
> --
> A
> .- Ukryj cytowany tekst -
Aleksander Nabagło
AMP napisał(a):
> Może znacie inne rozwiązanie (prostsze):
> - jest tabela z 'logami' - w niej są nast. kolumny: When (datetime),
> UserID (int), What (int), TableName (varchar(80)), ColName
> (varchar(80)), OldValue (varchar(1024)), NewVelue(varchar(1024))
> Czyli chcę podłączyć "monitorowanie" zmian w kilkunastu tabelach
> (chodzi o pola tekstowe) - tak aby wiedzieć które pole kiedy zostało
> zmodyfikowane - tak aby potem bardzo szybko znaleźć wszystko co
> modyfikował w zadanym okresie użytkownik itp..
>
> Może da się prosciej logować zmiany?
W zasadzie tak trzeba -- jest to bardzo ogolny,
uniwersalny zestaw danych dla tabeli logu
(moze tylko zamienic What(int) na What(char(1));
rozumiem, ze w tym polu bedzie ktores
z 'I', 'U', 'D' -- zawsze lepiej czytelne,
niz liczby).
Ponadto zalecalbym profilaktycznie we wszystkich
(lub wiekszosci) tabelach dodawac od poczatku
kolumne typu timestamp/rowversion; wtedy do logu
zapisywac stara i nowa wartosc rowversion oraz
tylko jedna wartosc tekstowa -- odpowiednio wybrana
sposrod OldValue i NewValue zaleznie od rodzaju
operacji I/U/D.
Z tych danych bedzie mozna wykryc takze przerwy
w historii logowania (np. wylaczony przez pewien
czas trigger logujacy), zas objetosc samego logu
moze byc mniejsza.
--
A
.
Aleksander Nabagło
Aleksander Nabagło napisał(a):
> zapisywac stara i nowa wartosc rowversion oraz
... chyba obie to nie byloby latwo,
czyli tylko nowa.
--
A
.
wloochacz
> Odpowiednie tabele nazywaja sie juz inserted/updated/deleted;
A gdzie ja mogę o tym przeczytać? ZTCW to w triggerze After Update, jest
dostęp zarówno do deleted (rekordy przed zmianą) jak i inserted (rekordy
po zmianie), ale updated?
[ciach]
--
wloochacz
AMP
> A gdzie ja mogę o tym przeczytać? ZTCW to w triggerze After Update, jest
> dostęp zarówno do deleted (rekordy przed zmianą) jak i inserted (rekordy
> po zmianie), ale updated?
Napisało mi się z rozpędu :)
Grzegorz Gruza
Mimo, że nie jestem miłośnikiem trigerów to uważam, że monitorowanie
lepiej jest robić na trigerach niż przez dodawanie kodu monitorującego
do procedur, bo monitorowanie jest ortogonalne do realizowanej
funkcjonalności biznesowej - więc najlepiej je jest też zaimplementować
w jakiś ortogonalny sposób.
Nie rozumiem dlaczego nie chcesz użyć trigerów napisanych w klasyczny
sposób - proponowany przez Aleksandra/mnie wcześniej.
AMP
Dziękuję za zainteresowanie się tematem. Na razie napisałem triggery
dla każdej z tabel indywidualnie - wszystko działa, jest OK - czyli
wszystko zostało po staremu.
> Nie rozumiem dlaczego nie chcesz użyć trigerów napisanych w klasyczny
> sposób - proponowany przez Aleksandra/mnie wcześniej.
Nie chciałem robić tego "standardowo" - bo wydawało mi się mało
wygodnie pisanie dla każdej z tabel innego kodu triggera (czyli
sprawdzanie innych warunków - inne nazwy kolumn). Chciałem mieć po
prostu "uniwersalny" trigger który sam wylistuje wszystkie kolumny,
sprawdzi co się zmieniło i zaloguje co trzeba. Taki trigger któremu
"nie przeszkodzi" to, że nagle w tabeli dodamy nową kolumnę - trigger
także ją będzie monitorował. :))