създаване на одитна пътека с помощта на оракул тригер

Опитвам се да създам тригер, който ще одитира всички нови и стари колони или всеки актуализиран, вмъкнат ред. ето я таблицата

ID  NUMBER(10,0)    No      1   
ENTITY_ID   NUMBER(10,0)    No      2   
LOCATION_ID NUMBER(10,0)    Yes     3   
NAME    VARCHAR2(128 CHAR)  No      4   
CREATED_BY  VARCHAR2(255 CHAR)  No  'unknown'   5   
UPDATED_BY  VARCHAR2(255 CHAR)  No  'unknown'   6   
TS_CREATED  TIMESTAMP(6)    No  "SYSTIMESTAMP
   "    7

Не съм сигурен как да напиша тригера.

CREATE OR REPLACE TRIGGER audit_security_zones
  BEFORE DELETE OR INSERT OR UPDATE ON security_zones
  FOR EACH ROW
DECLARE

BEGIN

END;

Също така искам да използвам клиентски идентификатор sentbt от приложението. Ето моята таблица с одитна пътека.

ID  NUMBER(10,0)    No      1   
ACTION  VARCHAR2(20 BYTE)   Yes     2   
TABLE_ID    VARCHAR2(100 BYTE)  Yes     3   
OLD_VALUE   VARCHAR2(1000 BYTE) Yes     4   
NEW_VALUE   VARCHAR2(1000 BYTE) Yes     5   
USERNAME    VARCHAR2(100 BYTE)  Yes     6   
TS_UPDATED  TIMESTAMP(6)    No  systimestamp    7

Това добър начин ли е да направите това? След това ще направя изглед за потребителя въз основа на всяка таблица, за да им покажа какво се е променило.

АКТУАЛИЗАЦИЯ

Опитвам се да постигна нещо подобно на следното

CREATE OR REPLACE TRIGGER audit_security_zones
  BEFORE DELETE OR INSERT OR UPDATE ON security_zones
  FOR EACH ROW
DECLARE
    var_action CHAR(1);
BEGIN
    IF INSERTING   THEN var_action := 'INSERT';
    ELSIF UPDATING THEN var_action := 'UPDATE';
    ELSE                var_action := 'DELETE';
    END IF;

    /* loop eahc column in the update or insert or delete statement executed */
    for each col in stmt/row{
    INSERT INTO audit_table
    (
    ACTION,
    TABLE_ID,
    OLD_VALUE,
    NEW_VALUE,
    USERNAME,
    TS_CREATED
    ) VALUES (
    var_action,
    :OLD.ID,
    :OLD.columnVALUE,
    :new.COLUMNVALUE,
    ociidentifier,
    systimestamp
    )
    }
END;

АКТУАЛИЗАЦИЯ 2

CREATE OR REPLACE TRIGGER audit_security_zones
  BEFORE DELETE OR INSERT OR UPDATE ON security_zones
  FOR EACH ROW
DECLARE
    var_action CHAR(6);
BEGIN
    IF INSERTING   THEN var_action := 'INSERT';
    ELSIF UPDATING THEN var_action := 'UPDATE';
    ELSE                var_action := 'DELETE';
    END IF;

    IF var_action = 'DELETE' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,
            O_ENTITY_ID,
            O_LOCATION_ID,
            O_NAME,
            O_CREATED_BY,
            O_UPDATED_BY,
            O_TS_CREATED            
        ) VALUES (
            var_action,
            USERNAME,
            :OLD.ID,
            :OLD.ENTITY_ID,
            :OLD.LOCATION_ID,
            :OLD.NAME,
            :OLD.CREATED_BY,
            :OLD.UPDATED_BY,
            :OLD.TS_CREATED         
        );
    ELSEIF var_action = 'INSERT' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,         
            N_ENTITY_ID,
            N_LOCATION_ID,
            N_NAME,
            N_CREATED_BY,
            N_UPDATED_BY
            N_TS_CREATED
        ) VALUES (
            var_action,
            USERNAME,
            :NEW.ID,            
            :NEW.ENTITY_ID,
            :NEW.LOCATION_ID,
            :NEW.NAME,
            :NEW.CREATED_BY,
            :NEW.UPDATED_BY,
            :NEW.TS_CREATED
        );
    ELSEIF var_action = 'UPDATE' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,
            O_ENTITY_ID,
            O_LOCATION_ID,
            O_NAME,
            O_CREATED_BY,
            O_UPDATED_BY,
            O_TS_CREATED,
            N_ENTITY_ID,
            N_LOCATION_ID,
            N_NAME,
            N_CREATED_BY,
            N_UPDATED_BY
            N_TS_CREATED
        ) VALUES (
            var_action,
            USERNAME,
            :OLD.ID,
            :OLD.ENTITY_ID,
            :OLD.LOCATION_ID,
            :OLD.NAME,
            :OLD.CREATED_BY,
            :OLD.UPDATED_BY,
            :OLD.TS_CREATED,
            :NEW.ENTITY_ID,
            :NEW.LOCATION_ID,
            :NEW.NAME,
            :NEW.CREATED_BY,
            :NEW.UPDATED_BY,
            :NEW.TS_CREATED
        );
    END IF;
END;

Получавам следните грешки

Error(31,9): PLS-00103: Encountered the symbol "VAR_ACTION" when expecting one of the following:     := . ( @ % ; 
Error(42,4): PLS-00103: Encountered the symbol "N_TS_CREATED" when expecting one of the following:     . ) , @ The symbol "." was substituted for "N_TS_CREATED" to continue. 
Error(54,12): PLS-00103: Encountered the symbol "VAR_ACTION" when expecting one of the following:     := . ( @ % ; 
Error(71,4): PLS-00103: Encountered the symbol "N_TS_CREATED" when expecting one of the following:     . ) , @ The symbol "." was substituted for "N_TS_CREATED" to continue. 
Error(90,4): PLS-00103: Encountered the symbol ";" when expecting one of the following:     if

АКТУАЛИЗАЦИЯ 3

CREATE OR REPLACE TRIGGER audit_security_zones
  BEFORE DELETE OR INSERT OR UPDATE ON security_zones
  FOR EACH ROW
DECLARE
    var_action CHAR(6);
BEGIN
    IF INSERTING   THEN var_action := 'INSERT';
    ELSIF UPDATING THEN var_action := 'UPDATE';
    ELSE                var_action := 'DELETE';
    END IF;

    IF var_action = 'DELETE' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,
            O_ENTITY_ID,
            O_LOCATION_ID,
            O_NAME,
            O_CREATED_BY,
            O_UPDATED_BY,
            O_TS_CREATED            
        ) VALUES (
            var_action,
            USERNAME,
            :OLD.ID,
            :OLD.ENTITY_ID,
            :OLD.LOCATION_ID,
            :OLD.NAME,
            :OLD.CREATED_BY,
            :OLD.UPDATED_BY,
            :OLD.TS_CREATED         
        );
    ELSE IF var_action = 'INSERT' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,         
            N_ENTITY_ID,
            N_LOCATION_ID,
            N_NAME,
            N_CREATED_BY,
            N_UPDATED_BY,
            N_TS_CREATED
        ) VALUES (
            var_action,
            USERNAME,
            :NEW.ID,            
            :NEW.ENTITY_ID,
            :NEW.LOCATION_ID,
            :NEW.NAME,
            :NEW.CREATED_BY,
            :NEW.UPDATED_BY,
            :NEW.TS_CREATED
        );
    ELSE IF var_action = 'UPDATE' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,
            O_ENTITY_ID,
            O_LOCATION_ID,
            O_NAME,
            O_CREATED_BY,
            O_UPDATED_BY,
            O_TS_CREATED,
            N_ENTITY_ID,
            N_LOCATION_ID,
            N_NAME,
            N_CREATED_BY,
            N_UPDATED_BY,
            N_TS_CREATED
        ) VALUES (
            var_action,
            USERNAME,
            :OLD.ID,
            :OLD.ENTITY_ID,
            :OLD.LOCATION_ID,
            :OLD.NAME,
            :OLD.CREATED_BY,
            :OLD.UPDATED_BY,
            :OLD.TS_CREATED,
            :NEW.ENTITY_ID,
            :NEW.LOCATION_ID,
            :NEW.NAME,
            :NEW.CREATED_BY,
            :NEW.UPDATED_BY,
            :NEW.TS_CREATED
        );
    END IF;
END;

грешката, която получавам сега, е

Error(90,4): PLS-00103: Encountered the symbol ";" when expecting one of the following:     if

АКТУАЛИЗАЦИЯ4

    CREATE OR REPLACE TRIGGER audit_security_zones
  BEFORE DELETE OR INSERT OR UPDATE ON security_zones
  FOR EACH ROW
DECLARE
    var_action CHAR(6);
BEGIN
    IF INSERTING   THEN var_action := 'INSERT';
    ELSIF UPDATING THEN var_action := 'UPDATE';
    ELSE                var_action := 'DELETE';
    END IF;

    IF var_action = 'DELETE' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,
            O_ENTITY_ID,
            O_LOCATION_ID,
            O_NAME,
            O_CREATED_BY,
            O_UPDATED_BY,
            O_TS_CREATED            
        ) VALUES (
            var_action,
            'test',
            :OLD.ID,
            :OLD.ENTITY_ID,
            :OLD.LOCATION_ID,
            :OLD.NAME,
            :OLD.CREATED_BY,
            :OLD.UPDATED_BY,
            :OLD.TS_CREATED         
        );
    ELSIF var_action = 'INSERT' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,         
            N_ENTITY_ID,
            N_LOCATION_ID,
            N_NAME,
            N_CREATED_BY,
            N_UPDATED_BY,
            N_TS_CREATED
        ) VALUES (
            var_action,
            'test',
            :NEW.ID,            
            :NEW.ENTITY_ID,
            :NEW.LOCATION_ID,
            :NEW.NAME,
            :NEW.CREATED_BY,
            :NEW.UPDATED_BY,
            :NEW.TS_CREATED
        );
    ELSIF var_action = 'UPDATE' THEN
        INSERT INTO audit_table
        (
            ACTION,
            USERNAME,
            ID,
            O_ENTITY_ID,
            O_LOCATION_ID,
            O_NAME,
            O_CREATED_BY,
            O_UPDATED_BY,
            O_TS_CREATED,
            N_ENTITY_ID,
            N_LOCATION_ID,
            N_NAME,
            N_CREATED_BY,
            N_UPDATED_BY,
            N_TS_CREATED
        ) VALUES (
            var_action,
            'test',
            :OLD.ID,
            :OLD.ENTITY_ID,
            :OLD.LOCATION_ID,
            :OLD.NAME,
            :OLD.CREATED_BY,
            :OLD.UPDATED_BY,
            :OLD.TS_CREATED,
            :NEW.ENTITY_ID,
            :NEW.LOCATION_ID,
            :NEW.NAME,
            :NEW.CREATED_BY,
            :NEW.UPDATED_BY,
            :NEW.TS_CREATED
        );
    END IF;
END;

нова грешка получавам

    Error(9,3): PL/SQL: SQL Statement ignored
Error(19,4): PL/SQL: ORA-00904: "O_TS_CREATED": invalid identifier
Error(32,3): PL/SQL: SQL Statement ignored
Error(42,4): PL/SQL: ORA-00904: "N_TS_CREATED": invalid identifier
Error(55,9): PL/SQL: SQL Statement ignored
Error(71,4): PL/SQL: ORA-00904: "N_TS_CREATED": invalid identifier

oracle triggers audit-trail
person shorif2000    schedule 28.06.2017    source източник
comment
Вижте този отговор за пример на тригер за проверка.   -  person Tony Andrews    schedule 28.06.2017
comment
Актуализирах въпроса си   -  person shorif2000    schedule 28.06.2017
comment
Всичко, което виждам грешно в UPDATE2, е липсваща запетая в последното INSERT между N_UPDATED_BY и N_TS_CREATED   -  person Tony Andrews    schedule 28.06.2017
comment
актуализирах го, можете ли да проверите update3? правилна ли е логиката ми със старите и новите при различни действия?   -  person shorif2000    schedule 28.06.2017
comment
Вместо ELSE IF напишете ELSIF (една дума).   -  person Tony Andrews    schedule 28.06.2017
comment
моля вижте актуализация 4   -  person shorif2000    schedule 28.06.2017
comment
Трябва да погледнете тези грешки, вместо просто да питате! Това е доста просто, въпрос на поставяне на нещо, което не е име на колона, в списъка с колони в първия ви INSERT...   -  person Tony Andrews    schedule 28.06.2017
comment
работя сега, имах грешна маса   -  person shorif2000    schedule 28.06.2017
comment
имах грешна таблица LOL   -  person APC    schedule 28.06.2017

Отговори (1)


arrow_upward
1
arrow_downward

Не можете да "циклите" през СТАРИ и НОВИ стойности, трябва да посочите изрично всяка от тях, т.е.

INSERT INTO audit_table
    (
    ACTION,
    TABLE_ID,
    OLD_VALUE,
    NEW_VALUE,
    USERNAME,
    TS_CREATED
    ) VALUES (
    var_action,
    :OLD.ID,
    :OLD.column1VALUE,
    :new.COLUMN1VALUE,
    ociidentifier,
    systimestamp
    );
INSERT INTO audit_table
    (
    ACTION,
    TABLE_ID,
    OLD_VALUE,
    NEW_VALUE,
    USERNAME,
    TS_CREATED
    ) VALUES (
    var_action,
    :OLD.ID,
    :OLD.column2VALUE,
    :new.COLUMN2VALUE,
    ociidentifier,
    systimestamp
    );
... etc.

Може да успеете да опростите донякъде кода, като напишете процедура за обвиване на израза INSERT и го извикате много пъти вместо самия оператор INSERT.

person Tony Andrews    schedule 28.06.2017