Trigger dla leniwych programistów PL/SQL

  Tworząc pakiety, procedury, funkcje, wyzwalacze, modyfikując strukturę bazy danych nie zawsze mamy chęć i cierpliwość do dokumentowania naszych zmian... Nierzadko pojawia się problem - ostatnie poprawki więcej napsuły niż poprawiły, dodaliśmy do pewnych tabel nowe pola, ale jakie a do obiektów bazodanowych nowe uprawnienia... Najlepiej byłoby takie zmiany śledzić automatycznie bez rozpraszania programisty - poniżej przedstawiam wizję pewnego rozwiązania opartego na pewnym przykładzie dołączanym z dokumentacją Oracle....


Instalację elementów  należy dokonać w osobnym schemacie niedostępnym dla programistów... Trigger przekażmy do instalacji  DBA...
   Stwórzmy następujące elementy bazodanowe - świadomie nie podaję nazw schematów:

• sekwencję do indeksowania tabeli historii zmian

  CREATE SEQUENCE SEQ_DDL_CHANGE

     START WITH 0

     MAXVALUE 9999999999999

     MINVALUE 0

     NOCYCLE

     NOCACHE

     NOORDER;

•  tabelę zawierającą zmiany

CREATE TABLE TBL_DDL_LOG

(

  ID              NUMBER(10)                    NOT NULL,

  SYSEVENT        VARCHAR2(50 CHAR)             NOT NULL,

  DICT_OBJ_OWNER  VARCHAR2(30 CHAR)             NOT NULL,

  DICT_OBJ_NAME   VARCHAR2(30 CHAR),

  DDL_STATEMENT   CLOB,

  USER_NAME       VARCHAR2(30 CHAR)  DEFAULT USER         NOT NULL,

  SYSTEM_DATE     DATE               DEFAULT SYSDATE      NOT NULL

);

 CREATE UNIQUE INDEX PK_DDL_LOG ON TBL_DDL_LOG(ID);

 ALTER TABLE TBL_DDL_LOG ADD (CONSTRAINT PK_DDL_LOG PRIMARY KEY(ID));

• tabele z listą użytkowników, którzy są śledzić swoje zmiany. Nie umieszczajmy w jej rekordach osób mających perfekcjonizm  we krwi

 

CREATE TABLE TBL_DDL_USER

(

  USER_LOGIN   VARCHAR2(30 CHAR)    NOT NULL,

  USER_NAME    VARCHAR2(30 CHAR)    DEFAULT USER         NOT NULL,

  SYSTEM_DATE  DATE    DEFAULT SYSDATE     NOT NULL

)

•   oraz sam kod triggera - pole typu LOB zmieści wszelkie  bardzo rozbudowane programistyczne wizje. Trigger należy instalować jako użytkownik sys. Należy pamiętać o dodaniu nazw schematów do wstawianych tabel

CREATE OR REPLACE TRIGGER TRG_TEST

   AFTER DDL OR GRANT OR REVOKE

   ON DATABASE

DECLARE

   vc_SqlText   ora_name_list_t;

   vc_Stmt      CLOB;

   vn_Size         PLS_INTEGER;

BEGIN

   SELECT   COUNT ( * )

     INTO   vn_Size

     FROM   TBL_DDL_USER

    WHERE   USER_LOGIN = USER;

   IF vn_Size > 0

   THEN

      vn_Size := ora_sql_txt (vc_SqlText);

      FOR i IN 1 .. vn_Size

      LOOP

         vc_Stmt := vc_Stmt || vc_SqlText (i);

      END LOOP;

      INSERT INTO TBL_ddl_log (ID,

                                   SYSEVENT,

                                   DICT_OBJ_OWNER,

                                   DICT_OBJ_NAME,

                                   DDL_STATEMENT,

                                   USER_NAME,

                                   SYSTEM_DATE)

        VALUES   (SEQ_DDL_CHANGE.NEXTVAL,

                  ora_sysevent,

                  ora_dict_obj_owner,

                  ora_dict_obj_name,

                  vc_Stmt,

                  USER,

                  SYSDATE);

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN;

END;

/

W prosty sposób kod z własnego schematu można pobrać za pomocą poniższego zapytania:

SELECT OBJECT_NAME, OBJECT_TYPE,DBMS_METADATA.GET_DDL( REPLACE(OBJECT_TYPE,' ', '_'), OBJECT_NAME)

FROM

(SELECT DISTINCT  OBJECT_NAME, OBJECT_TYPE FROM USER_PROCEDURES)