时间戳

首页 > 解决方案 > oracle 中的通用触发器

oracle - oracle 中的通用触发器

问题描述

我正在编写一个通用的 oracle 触发器。假设有许多主表，如 PERSON_INFO、EMPLOYEE_INFO 等，以及它们对应的审计表，如 PERSON_INFO_AUDIT、EMPLOYEE_INFO_AUDIT。结构如下。

PERSON_INFO有列：-

            ------------------------------------------------
            |                 PERSON_INFO                  |
            ------------------------------------------------
            |  PERSON_ID   |   FIRST_NAME     |  LAST_NAME |
            |   (NUMBER)   |   (VARCHAR2)     |  (VARCHAR2)|
            ------------------------------------------------
            |    1         |   Andrew         |  Jack      |
            ------------------------------------------------

PERSON_INFO_AUDIT 具有PE​​RSON_INFO的所有列以及另外两个列 OPERATIONS 和 AUDIT_DATE。

要求是，如果任何主表被更新或主表中的任何行被删除，则主表的旧条目应插入其相应的审计表中。

然后我写了一个更新，比如：-

更新PERSON_INFO SET FIRST_NAME= '约翰';

那么PERSON_INFO的旧值应插入PERSON_INFO_AUDIT表中，如下所示：-

PERSON_INFO_AUDIT现在应该包含：-

-------------------------------------------------------------------------
|                         PERSON_INFO_AUDIT                              |
-------------------------------------------------------------------------
|  PERSON_ID   |   FIRST_NAME     |  LAST_NAME |  AUDIT_DATE | OPERATIONS|
|   (NUMBER)   |   (VARCHAR2)     |  (VARCHAR2)| (TIMESTAMP) |   (CHAR)  |
-------------------------------------------------------------------------
|    1         |   Andrew         |  Jack      |  30-08-2019 |     U     |
-------------------------------------------------------------------------

这里 audit_date 是今天的日期，操作指定主表中的行是被删除（D）还是更新（U）。为了方便上述场景，我编写了以下触发函数。

CREATE OR replace TRIGGER trig_PERSON_INFO_deleteupdate
    after UPDATE OR DELETE 
    ON PERSON_INFO
    FOR EACH ROW
DECLARE
     base_table_name clob;
     audit_table_name clob;
     base_table_cols_in_string clob;
     audit_table_cols_in_string clob;
     operation char;
     final_query clob;    
BEGIN
     base_table_name:= 'PERSON_INFO';
     audit_table_name := base_table_name || '_AUDIT';

     IF UPDATING THEN 
          operation:= 'U';
     ELSE
          operation:= 'D';
     END IF;

     SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY column_id)
     INTO base_table_cols_in_string
     FROM ALL_TAB_COLUMNS
     WHERE TABLE_NAME= 'PERSON_INFO';

     audit_table_cols_in_string:= base_table_cols_in_string || ',AUDIT_DATE,OPERATIONS';

     final_query:= 'INSERT INTO ' ||  audit_table_name || '(' || audit_table_cols_in_string || ') VALUES(' || ':OLD.PERSON_ID,:OLD.FIRST_NAME,:OLD.LAST_NAME,' || SYSDATE || ',''' || operation || ''');';

     dbms_output.put_line(final_query); 
     EXECUTE IMMEDIATE final_query;
END;

形成的查询是：

INSERT INTO PERSON_INFO_AUDIT(PERSON_ID,FIRST_NAME,LAST_NAME,AUDIT_DATE,OPERATIONS) VALUES(:OLD.PERSON_ID,:OLD.FIRST_NAME,:OLD.LAST_NAME,30-10-19,'U');

但是，当我尝试使用 EXECUTE IMMEDIATE final_query 执行查询时，我收到了一个错误

标签： oracleplsqldatabase-trigger

解决方案

这个错误的原因是：

  1. 动态查询末尾有分号。删除那个分号。
  2. 在准备动态查询时，表的旧值应写为变量/列名，而不是常量（不应在 '' 中写旧值）。
  3. Sysdate 应该正确写入。

请在下面找到更正的代码：

create or replace TRIGGER trig_PERSON_INFO_deleteupdate
after UPDATE OR DELETE 
ON PERSON_INFO
FOR EACH ROW
DECLARE

 base_table_name clob;
 audit_table_name clob;
 base_table_cols_in_string clob;
 audit_table_cols_in_string clob;
 operation char;
 final_query clob;      
BEGIN

 base_table_name:= 'PERSON_INFO';
 audit_table_name := base_table_name || '_AUDIT';

 IF UPDATING THEN 
      operation:= 'U';
 ELSE
      operation:= 'D';
 END IF;

 SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY column_id)
 INTO base_table_cols_in_string
 FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME= 'PERSON_INFO';

 audit_table_cols_in_string:= base_table_cols_in_string || ',AUDIT_DATE,OPERATIONS';

 final_query:= 'INSERT INTO ' ||  audit_table_name || '(' || audit_table_cols_in_string 
    || ') VALUES(''' || :OLD.PERSON_ID || ''',''' || :OLD.FIRST_NAME || ''',''' || :OLD.LAST_NAME 
    || ''',date ''' || to_char(SYSDATE,'yyyy-mm-dd)' || ''',''' || operation || ''')';

 dbms_output.put_line(final_query); 
 EXECUTE IMMEDIATE final_query;
END;

希望这对你有帮助:)

推荐阅读