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');
W tabeli ten opis otrzymuje swój numerek (kolejny index w Plan_no)
Tablica elementy wygląda tak:
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ę:
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$:
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
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.