Ciekawy TRIGGER Opcje

[Mastahnish]

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10 :27:29 Copyright &copy; 1988-2008 Microsoft Corporation Express Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

Chciałbym prosić o pomoc w stworzeniu triggera służącego do stworzenia swoistej historii w każdej tabeli.


Istnieje tablica główna o nazwie Plan_list. Ona zawiera pięć kolumn:
Stat
Plan_no (PK)
Opis
Autor
Data

Jest ona połączona Tabelą Elementy, która zawiera kolumny
Stat
Plan_no (FK)
Name - element unikalności
atm - element unikalności
vpi - element unikalności
NE
Elementy_id (PK)

Możliwe statusy w tabeli Plan_list to O lub W (Otwarty - Wykonany).
Chodzi o to, żeby przy update'cie tabeli Elementy (przykładowo zmiany NE) wiersz w którym to NE jest zmieniane został skopiowany (ze stanem Przed Updatem) i wklejony na dół tabeli z niezmienionymi wartościami oprócz Stat, który miałby mieć wartość 'FC'. Plan_no tego wiersza,który był kopiowany zmienia się na ten, który użytkownik podał w zapytaniu update (Plan_no = 'x') Wiersz będący wynikiem kopiowania jest czystą kopią wiersza kopiowanego, z wyjątkiem kolumny Stat='FC' .
Dodaktowo urzytkownik musi wpisać stan tego update'owanego wiersza jako 'N'. To się dzieje dla statusu 'O' w tabeli planów.
DANY REKORD NIE MOŻE BYĆ UPDATE'owany KILKA RAZY PODCZAS GDY JEGO STATUS JEST 'N', czyli plan, który został wykonany na danym wierszu jest Otwarty.
Updateuję sobie wiersz w tabeli Elementy, która to ma trzy wiersze zapełnione.:
1. Najpierw wpisuję do tabeli Plan_list co będę robił.

Kod

INSERT INTO plan_list (Stat, opis) VALUES('O','Zmiana parametru NE');

[HTML] pobierz, plaintext 
Stat Plan_no      Opis 
W     1          Zmiana nazwy 
W     2          Zmiana statusu 
O     3          Zmiana parametru NE 
[HTML] pobierz, plaintext 

W tabeli ten opis otrzymuje swój numerek (kolejny index w Plan_no)
Tablica elementy wygląda tak:

[HTML] pobierz, plaintext 
Stat Plan_no Name NE   elementy_id 
A      0    N1    NE1    1 
A      0    N2    NE2    2 
A      0    N3    NE3    3
 
[HTML] pobierz, plaintext 

2. Następnie aktualizuję tablicę elementy.

Kod

UPDATE elementy SET stat='N', plan_no=3, NE='new_NE' WHERE NE='NE3';

w wyniku tego oczekuję, że w tabelce Elementy pokaże mi się:

[HTML] pobierz, plaintext 
Stat Plan_no Name     NE   elementy_id 
A         0    N1    NE1      1
A         0    N2    NE2      2
N         3    N3   new_NE    3 
FC        0    N3    NE3      4 
[HTML] pobierz, plaintext 

Natomiast dla statusu 'W' należy zaktualizować UPDATE'owany wcześniej wiersz oraz 'skasować' poprzedni stan (ten ze statusem 'FC'). Aktualizacja polega na zmianie statusu z 'N' na 'A' a skasowanie z 'FC' na 'D'.
Dopiero po zaakceptowaniu zmian ('N'->'A') wiersz może być kolejny raz zmieniony.
Nie chciałym, żeby wiersz-historia ze statusem 'FC' tworzył się dla zmiany stanów z 'N' na 'A' i z 'FC' na 'D' (a więc dla statusu W w plan_list).
Na koniec zależy mi na tym, żeby w przypadku skasowania wiersza w plan_list (czyli skasowania jakiegoś planu) wszystkie dotychczasowe zmiany we wierszach, które były 'podjęte' tym planem zostały wycowane, włącznie z 'posprzątaniem' czyli usunięciem historii.
Jak ja to widzę? Po prostu kiedy plan zostaje usunięty zostaje przeszukana cała baza danych w celu znalezienia tablic gdzie dany plan był wykonywany (z tego co wiem należałoby tu skorzystać z tablicy sys.tables i stworzyć jakiś kursor, albo po prostu trigger, który przelatuje po wszystkich tabelach w poszukiwaniu tablic z kolumną Plan_no w której są przechowywane numery planów, następnie wchodzi w te tabele po kolei i wykonuje... no właśnie, wykonuje sprzątanie czyli:
-> Znalazienie wiersza z danym planem
-> znalazienie wiersza-historii (tego z wartościami przed zastosowaniem planu) odpowiadającego danemu wierszowi. (Myślałem, że mógłbym znaleźć ten wiersz po unikalności danych szukając w tablicy DELETED, ale ta unikalność w tej tablicy jest mało zdatna, bo ta unikalność która będzie dla wierszów właściwych, w przypadku wierszów-historycznych nie istnieje, bo mogą się powtarzać te wartości. Możliwe, że unikalność da się stworzyć jeśli weźmiemy również pod uwagę numer planu, który powinien być n-1 dla n=numer_planu_który_został_skasowany )
-> nadpisanie na wiersz aktualny albo nowy (zależy w którym momencie się kasuje plan) dane z wiersza-historii (który ma status 'FC' albo 'D')
-> usunięcie wiersza-historii, z którego braliśmy nadpisujące dane historyczne.
FC-for cancelation
N-new
D-deleted
A-actual
Póki co napisałem taki wyzwalacz dla realnej tabeli:

Tabela VP$:

[HTML] pobierz, plaintext 
Stat NE tp_ATM  VPI      VPL_TRDE                     remarks VP$_id    Plan_no 
A RN3102XX MS-24-1  1    AtmTrafficDescriptor=C1P42000 NULL      1        NULL 
A RN3102XX MS-25-1  1    AtmTrafficDescriptor=C1P42000 NULL      2        NULL 
A RN3102XX MS-24-1  7    AtmTrafficDescriptor=C1P29000 NULL      3        NULL 
A RN3102XX MS-25-1  7    AtmTrafficDescriptor=C1P29000 NULL      4        NULL 
A RN3102XX MS-24-1  8    AtmTrafficDescriptor=C1P11000 NULL      5        NULL 
A RN3102XX MS-25-1  8    AtmTrafficDescriptor=C1P11000 NULL      6        NULL 
A RN3102XX MS-24-1  9    AtmTrafficDescriptor=C1P11000 NULL      7        NULL 
A RN3102XX MS-25-1  9    AtmTrafficDescriptor=C1P11000 NULL      8        NULL 
A RN3102XX MS-24-1  11  AtmTrafficDescriptor=C1P11000 NULL       9        NULL 
A RN3102XX MS-25-1  11  AtmTrafficDescriptor=C1P11000 NULL       10       NULL
[HTML] pobierz, plaintext 

Trigger tablicy VP$ tworzący 'wiersz-historię' dla edytowanego wiersza.
Na dole stworzyłem Trigger, który działa tak, że Pobierając ostatnio update'owane wiersze z tablicy deleted przepisuje je do nowo stworzonego wiersza ze statusem 'FC'.

Kod

CREATE TRIGGER [dbo].[VP$_trigger]
ON [dbo].[VP$]
After UPDATE
AS
BEGIN

declare @NE varchar(10)
declare @tp_atm varchar(10)
declare @VPI smallint
declare @VPL_TRDE varchar(50)
declare @remarks varchar(30)
declare @Plan_no int

select @NE=D.NE, @tp_atm=D.tp_atm, @VPI=D.VPI,          @VPL_TRDE=D.VPL_TRDE, @remarks=D.remarks,          @plan_no=D.plan_no from DELETED D

/*
Ta pętla sprawdza czy nie istnieje w tabeli VP$ wiersz ze statusem "FC", którego parametry NE, TP_ATM oraz VPI tworzące unikalność akurat w tej tabeli były ostatnio dodanymi do tabeli deleted, czyli pochodzą zwiersza przed chwilą update'owanego. Wydaje mi się, że tak musi być ponieważ warunek-założenie, że nie można kilka razy edytować wiersza z niezaakceptowaną, wcześniej zrobioną zmianą powoduje, że wiersz z 'FC' i tymi parametrami się nie powtórzy. Mimo to myślę, że trzeba byłoby wziąć pod uwagę również kolumnę Plan_no.No ale póki co:Kiedy ten IF znajdzie taki wiersz to:- Dodaje do tablicy VP$ taki sam wiersz jak ten znaleziony (INSERT), ale z wartością Statusu 'FC' (SELECT).*/ IF NOT EXISTS   (select * from VP$ where Stat='FC' and NE=@NE and tp_atm=@tp_atm and VPI=@VPI)
BEGIN
INSERT INTO VP$ (Stat, NE, tp_atm, VPI, VPL_TRDE, remarks, plan_no)
SELECT 'FC', @NE, @tp_atm, @VPI, @VPL_TRDE, @remarks, @plan_no;
END

/*
Zadaniem tych IFów jest Nie tworzenie wiersza-historii ze statusem 'FC' kiedy zmieniamy status wiersza-historii z FC na D.
*/
IF EXISTS   (select * from VP$ where Stat='D' and NE=@NE and tp_atm=@tp_atm and VPI=@VPI)
BEGIN
  IF EXISTS   (select * from VP$ where Stat='FC' and NE=@NE and tp_atm=@tp_atm and VPI=@VPI)
  BEGIN
   DELETE from VP$ where Stat='FC' and NE=@NE and tp_atm=@tp_atm and VPI=@VPI
  END
END
/*
Analogiczny i równie mało wykwintny IF może być stworzony dla sytuacji zmiany statusu z N na A.
*/
IF EXISTS     (select * from VP$ where Stat='A' and NE=@NE and tp_atm=@tp_atm and VPI=@VPI)
BEGIN
  IF EXISTS    (select * from VP$ where Stat='FC' and NE=@NE and tp_atm=@tp_atm and VPI=@VPI)
  BEGIN
   DELETE from VP$ where Stat='FC' and NE=@NE and tp_atm=@tp_atm and VPI=@VPI
  END
END


END

Te Dwa ostatnie sety IFów chyba trzeba jakoś zreperować albo w ogóle wymyslić coś innego, bo kiedy problemem największym jest to, że za każdym razem ja odwołuję się do OSTATNIO update'owanego wiersza w tabeli DELETED, a chciałbyłm to zrobić tak, że wybieram z DELETED takie wartości jakie mnie interesują w danej chwili. Czyli jak w stanie O w plan_list mam sytuację wiersza w stanie N i jego wiersza-historii w stanie FC to nie muszę natychmiast ich akceptować tylko mogę wprowadzić aktualizację w innych wierszach również nadając im status N i tworząc histortczne wiersze z FC. Chcę po jakimś czasie wrócić to pierwszego edytowanego wiersza i zmienić mu stan z N na A i jego wiersza-historii z FC na D i chcę, żeby trigger wiedział wiersz wybrać. Stąd konieczne jest przegrzebywanie całej tabeli DELETED (najprawdopodobniej po jakimś kluczu składającym się z NE, tp_atm, VPI (te 3 na pewno) i być może jeszcze właśnie tego plan_no).
Poza tym uważam i chciałbym, żeby ten trigger wyzwalał się tylko wtedy kiedy ktoś 'grzebie' przy Statusie danego wiersza. W innym przypadku można sobie edytować wartości innych kolumn.