Blog > Komentarze do wpisu
Zmienne kontekstowe sesji i triggery (Oracle)
Najprawdopodobniej w ‘żyjącym’ (produkcyjnym) systemie każdy z nas od czasu do czasu musi poprawić jakieś dane. Błędy zdażają się w kodzie, podczas wdrażania nowych wersji aplikacji, a najczęściej to sami użytkownicy znajdują sprytne sposoby na obejście dodanych walidacji danych. Dziś pokażę jak poprawić dane, których czas modyfikacji śledzony jest z poziomu triggera bez wpływu na datę i czas modifikacji danych (przykład na pewno wyjaśni to lepiej :)...

 

 

>>> ZACZYNAMY <<<

 

 

Najprawdopodobniej w ‘żyjącym’ (produkcyjnym) systemie każdy z nas od czasu do czasu musi poprawić jakieś dane. Błędy zdażają się w kodzie, podczas wdrażania nowych wersji aplikacji, a najczęściej to sami użytkownicy znajdują sprytne sposoby na obejście dodanych walidacji danych.



Każda z naszych tabel poza kluczowymi kolumnami posiada cztery dodatkowe. Rozważmy przykładową tabelę.

 

 

CREATE TABLE OUR_TEST_TABLE
(
  NAME		VARCHAR2(30 BYTE)	NOT NULL,
  CREATEDBY	VARCHAR2(30 BYTE)	NOT NULL,
  TIMECREATED	DATE			NOT NULL,
  MODIFIEDBY	VARCHAR2(30 BYTE)	NOT NULL,
  TIMEMODIFIED DATE			NOT NULL
);
/




Dane to tak naprawdę pierwsza kolumna, cztery kolejne to podstawowe informacje, które pomagają nam rozwiązywać problemy zgłaszane przez użytkowników. Są standardem dla wszystkich naszych tabel i wszystkie cztery obsługiwane są przez trigger podobny do poniższego (również standardowy dla każdej z tabel).




CREATE OR REPLACE TRIGGER "OUR_TEST_TABLE_BR_IU"
   BEFORE INSERT OR UPDATE
   ON OUR_TEST_TABLE
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
BEGIN
   IF INSERTING
   THEN
      :NEW.CREATEDBY := SUBSTR (USER, 1, 30);
      :NEW.TIMECREATED := SYSDATE;
   END IF;

   :NEW.MODIFIEDBY := SUBSTR (USER, 1, 30);
   :NEW.TIMEMODIFIED := SYSDATE;
END;
/



 

Oczywiście skutkuje to tym, że każda zmiana (data fix) powoduje zmianę wartości pól modifiedby i timemodified, co w większości wypadków jest niepożądane. Dlatego też podczas aplikowania takich patchy, trigger ten jest wyłączany. Rozwiązanie to nie jest oczywiście najlepsze, bo biorąc pod uwaga to, w jaki sposób zdefiniowane są nasze tabele, zablokowany trigger uniemożliwia dodawanie nowych rekordów.



W rzeczywistości zmuszało nas to do wykonywania skryptów poza regularnymi godzinami pracy, co generalnie prowadziło do frustracji nie tylko naszej, ale przede wszystkim naszych użytkowników, ponieważ musieli oni czekać na poprawkę do kolejnego dnia. Postanowiliśmy, więc to zmienić. Z pomocą przyszły nam zmienne kontekstowe sesji.



Dodajmy do naszej przykładowej tabeli 3 wiersze:




INSERT INTO OUR_TEST_TABLE (NAME) VALUES('Bob Marley');
INSERT INTO OUR_TEST_TABLE (NAME) VALUES('Michael Jakobson');
INSERT INTO OUR_TEST_TABLE (NAME) VALUES('Jimi Hendrix');

COMMIT;

SELECT * FROM OUR_TEST_TABLE;



Dane wyjściowe skryptu są następujące:



1 row created.
1 row created.
1 row created.
Commit complete.

NAME                           CREATEDBY MODIFIEDBY TIMECREATED          TIMEMODIFIED
------------------------------ --------- ---------- ------------------- -------------------
Bob Marley                     USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13
Michael Jakobson               USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13
Jimi Hendrix                   USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13

3 rows selected.


Ups. Popełniliśmy literówkę. Szybki UPDATE powinien rozwiązać sprawę:




UPDATE	OUR_TEST_TABLE
   SET		NAME = 'Michael Jackson'
 WHERE		NAME = 'Michael Jakobson';


SELECT * FROM OUR_TEST_TABLE;



 

NAME                           CREATEDBY MODIFIEDBY TIMECREATED          TIMEMODIFIED
------------------------------ --------- ---------- ------------------- -------------------
Bob Marley                     USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13
Michael Jackson                USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:39:18
Jimi Hendrix                   USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13

3 rows selected.



Niestety, pole timemodified od razu nas zdradza. Wykonujemy więc równie szybki ROLLBACK.




SELECT * FROM OUR_TEST_TABLE;



 

NAME                           CREATEDBY MODIFIEDBY TIMECREATED           TIMEMODIFIED
------------------------------ --------- ---------- ------------------- -------------------
Bob Marley                     USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13
Michael Jakobson               USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13
Jimi Hendrix                   USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13

3 rows selected.

 

Od razu lepiej. Spróbujmy teraz podmienić nasz trigger na poniższy:




CREATE OR REPLACE TRIGGER "OUR_TEST_TABLE_BR_IU"
   BEFORE INSERT OR UPDATE
   ON OUR_TEST_TABLE
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   LN_ISDATAFIX   NUMBER;                            -- DATAFIX 1, OTHERWISE 0
BEGIN
   --ALLOW FOR ROW/ROW DISABLING OF THE TRIGGER FOR DML UPDATES.
   SELECT   SYS_CONTEXT ('CLIENTCONTEXT', 'DATAFIX')
     INTO   LN_ISDATAFIX
     FROM   DUAL;

   --ONLY DO THIS IF THE DATAFIX FLAG IS NOT SET
   IF LN_ISDATAFIX IS NULL
   THEN
      IF INSERTING
      THEN
         :NEW.CREATEDBY := SUBSTR (USER, 1, 30);
         :NEW.TIMECREATED := SYSDATE;
      END IF;

      :NEW.MODIFIEDBY := SUBSTR (USER, 1, 30);
      :NEW.TIMEMODIFIED := SYSDATE;
   /* OTHER LOGIC AS REQUIRED */

   END IF;
/* OTHER LOGIC AS REQUIRED */

END;
/



 

A zamiast zwykłego polecenia UPDATE wykonajmy poniższy skrypt:

 

 

EXEC DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT', 'DATAFIX', '1');

SELECT   SYS_CONTEXT ('CLIENTCONTEXT', 'DATAFIX') FROM DUAL;


UPDATE	OUR_TEST_TABLE
   SET		NAME = 'Michael Jackson'
 WHERE		NAME = 'Michael Jakobson';


EXEC DBMS_SESSION.CLEAR_CONTEXT('CLIENTCONTEXT', 'DATAFIX');

SELECT   SYS_CONTEXT ('CLIENTCONTEXT', 'DATAFIX') FROM DUAL;

SELECT * FROM OUR_TEST_TABLE;



 

PL/SQL procedure successfully completed.

SYS_CONTEXT('CLIENTCONTEXT','DATAFIX')                                         
--------------------------------------------------------------------------------
1                                                                              
1 row selected.
1 row updated.
PL/SQL procedure successfully completed.

SYS_CONTEXT('CLIENTCONTEXT','DATAFIX')                                         
--------------------------------------------------------------------------------
                                                                               
1 row selected.

NAME                           CREATEDBY MODIFIEDBY TIMECREATED           TIMEMODIFIED
------------------------------ --------- ---------- ------------------- -------------------
Bob Marley                     USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13
Michael Jackson                USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13
Jimi Hendrix                   USERNAME  USERNAME   07/11/2011 11:33:13 07/11/2011 11:33:13

3 rows selected.

 

Voila :) Dane poprawione, timestamp niezmieniony, możemy więc wykonać spokojnie COMMIT. W tej chwili zmieniliśmy triggery na kluczowych tabelach i zaktualizowaliśmy nasz DML template. Z biegiem czasu inne tabele również zostaną objęte tym rozwiązaniem. Oczywiście chcemy, aby nasi programiści ciągle myśleli o tym czy blokowanie triggera jest konieczne czy też nie, dlatego nasza ‘flaga’ DATAFIX jest wyłączona domyślnie.



Jest to prosta i praktyczna rzecz dająca nam znacznie większe pole do manewru w momencie, gdy musimy zaaplikować jakiś produkcyjny data-patch.



Może komuś jeszcze się przyda.


poniedziałek, 07 listopada 2011, m0rt1m3r
Tagi: pl/sql oracle

Related Posts Plugin for WordPress, Blogger...

Polecane wpisy





PowerBuilder Tetris
D - Tetris



Programowanie iOS

C# ToolBox

SQL / TSQL / PLSQL ToolBox

Linux / Unix ToolBox





Zaprzyjaznione Strony

Sprite Bandits

Cake Time