代码之家 › 专栏 › 技术社区 › shorif2000

使用oracle触发器创建审计跟踪

audit-trail triggers oracle

-1

shorif2000 · 技术社区 · 9 年前

我正在尝试创建一个触发器,该触发器将审核所有新的和旧的列或更新、插入的每一行。这是桌子

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;

我还想使用应用程序中的客户端标识符senbt。这是我的审计跟踪表。

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

    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

1 回复 | 直到 9 年前

Tony Andrews 9 年前

不能“循环”新旧值,必须明确引用每一个值,即。

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声明本身,从而在某种程度上简化代码。