Historia działań w tabeli i tablica planów Opcje

[Mastahnish]

Witam serdecznie!
Na początku się przedstawię:

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 z 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ł.

[SQL] pobierz, plaintext 
INSERT INTO plan_list (Stat, opis) VALUES('O','Zmiana parametru NE'); 
[SQL] pobierz, plaintext 

Stat Plan_no Opis
W 1 Zmiana nazwy
W 2 Zmiana statusu
O 3 Zmiana parametru NE



W tabeli ten opis otrzymuje swój numerek (kolejny index w Plan_no)



Tablica elementy wygląda tak:



Stat Plan_no Name NE elementy_id
A 0 N1 NE1 1
A 0 N2 NE2 2
A 0 N3 NE3 3




2. Następnie aktualizuję tablicę elementy.

[SQL] pobierz, plaintext 
UPDATE elementy SET stat='N', plan_no=3, NE='new_NE' WHERE NE='NE3';  
[SQL] pobierz, plaintext 

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

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


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



Jak to napisać?

Ten post edytował Mastahnish 3.08.2011, 12:34:23