我可以看到 Oracle 触发器内的 DML 吗？

Question

是否可以看到正在运行的导致执行触发器的 DML（SQL 语句）？

例如，在 INSERT 触发器内，我想得到这样的信息：

“插入 myTable (name) 值 ('Fred')”

我在诸如 这个 但无法让它工作 - 不确定这是否会导致我陷入困境正确的道路？

我们正在使用 Oracle 10。

提前谢谢您。

=========================

[编辑]更多细节：我们需要复制现有数据库（DB1）存储到无法通过网络访问的分类数据库 (DB2)。我需要保持这些数据库同步。这是从 (DB1) 到 (DB2) 的单向同步，因为 (DB2) 将包含 (DB1) 系统中未包含的其他表和数据。

我必须确定一种方法来同步这些数据库而不将其关闭（例如，用于备份和恢复），因为它需要保持活动状态。所以我想，如果我可以存储正在运行的实际 DML（当数据更改时），我可以在新数据库上“回放”DML 来更新它，就像有人手动将其重新输入一样。

我可以”由于数据量巨大，我无法将所有数据都带过来，并且由于 FK 约束以及插入/更新记录的顺序，我无法复制更改的记录。我想，如果我可以使用更改主服务器的确切 SQL“回放”所发生事件的日志，我就可以保持数据库同步。

我当前的攻击计划是保留所有已更改、插入和删除的记录的日志，当我想要同步时，系统会生成 DML 来插入/更新/删除这些记录。然后我只需将 .SQL 文件传输到分类系统并运行脚本即可。我遇到的问题是 FK。 （因为当我生成 DML 时，我只知道数据的当前状态是什么，而不知道到达那里的路径 - 所以语句的顺序是一个问题）。我想我可以禁用所有 FK，进行合并，然后重新启用所有 FK...

那么 - 我存储实际 DML 的方法是否会吸水池水，或者是否有更好的解决方案？？？

Answer 1

“我存储实际 DML 的方法是否会浪费池水？”是的..

3. 您的 DB1 上的 DML 严格排序实际上并不存在。多个进程、多个核心，事情基本上同时发生。

4. 而 DML，即使它是按顺序发生的，也不会像它那样工作。假设以下两个更新语句在具有单独事务的单独进程中运行，其中事务 2 中的更新在事务 1 提交之前开始：

    update table_a set col_a = 10 where col_b = 'A' -- 事务 1
    update table_a set col_c = 'Error' where col_a = 10 -- 事务 2
   

由于第一个事务中所做的更改对第二个事务不可见，因此第二个事务更改的行将不包括第一个事务的行。但是，如果您设法捕获 DML 并按顺序重播它，则事务 1 的更改将可见，因此事务 2 的更改将不同。 （请参阅 Tom Kyte 的 Oracle 数据库架构专家的第 40 和 41 页第二版。）

3. 希望您使用绑定变量，因此 DML 本身没有意义：update table_a set col_a = :col_a where id = :id 现在做什么？好的，所以您需要带有变量绑定的 DML。

4. 你使用序列吗？如果是这样，next_val 将不会在 DB1 和 DB2 之间保持同步。 （例如，实例故障可能会导致值丢失，两个系统都会同时故障吗？）如果您正在处理 RAC，其中 next_val 根据节点而变化，那就忘记它吧。

我将首先研究 Oracle 的复制。

Answer 2

我遇到过这样的情况：在测试后，我需要将元数据/配置更改（存储在几个表中）从开发环境移动到生产环境。像 Goldengate 这样的产品就是用于此目的的产品，但设置和管理可能成本高昂且复杂。

以下过程生成一个触发器并将其附加到需要保存 DML 的表。触发器重新创建 DML，并在以下情况下将其保存到审核表中 - 这取决于您如何处理它。您可以使用保存到审计表的语句来重播给定时间点的更改（剪切和粘贴或开发一个过程将它们应用到目标）。

希望您觉得这很有用。

    procedure gen_trigger( p_tname in varchar2 )
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_query         varchar2(1000) default 'select * from ' || p_tname;
    l_colCnt        number := 0;
    l_descTbl       dbms_sql.desc_tab;
    trg             varchar(32767) := null;
    expr            varchar(32767) := null;
    cmd             varchar(32767) := null;

begin

    dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    trg := q'#
        create or replace trigger <%TABLE_NAME%>_audit
        after insert or update or delete on <%TABLE_NAME%> for each row
        declare
        qs  varchar2(20) := q'[q'^]';
        qe  varchar2(20) := q'[^']';
        command   clob;
        nlsd      varchar2(100);
        begin
            select value into nlsd from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
            execute immediate 'alter session set nls_date_format = ''YYYY/MM/DD hh24:mi:ss'' ';
            if inserting then
                command := <%INSERT_COMMAND%>; 
            end if;
            if updating then
                command := <%UPDATE_COMMAND%>;
            end if;
            if deleting then
                command := <%DELETE_COMMAND%>;
            end if;
            insert into x_audit values (systimestamp, command);
            execute immediate q'+alter session set nls_date_format = '+'|| nlsd || q'+'+';
        end;
    #';

    -- Create the insert command 
    cmd := q'#'insert into <%TABLE_NAME%> (<%INSERT_COLS%>) values ('||<%INSERT_VAL%>||')'#';
    -- columns clause
    for i in 1 .. l_colCnt loop
        if expr is not null then
            expr := expr || ',';
        end if;
        expr := expr || l_descTbl(i).col_name;
    end loop;
    cmd := replace(cmd,'<%INSERT_COLS%>',expr);

    -- values clause
    expr := null;
    for i in 1 .. l_colCnt loop
        if expr is not null then
            expr := expr || q'#||','||#';
        end if;
        expr := expr || 'qs||:new.' || l_descTbl(i).col_name || '||qe';
    end loop;
    cmd := replace(cmd,'<%INSERT_VAL%>',expr);
    trg := replace(trg,'<%INSERT_COMMAND%>',cmd);

    -- create the update command
    -- set clause
    expr := null;
    cmd := q'#'update <%TABLE_NAME%> set '||<%UPDATE_COLS%>||' where '||<%WHERE_CLAUSE%>#';
    for i in 1 .. l_colCnt loop
        if expr is not null then
            expr := expr || q'#||','||#';
        end if;
        expr := expr || q'#'#' || l_descTbl(i).col_name || q'# = '||#'|| 'qs||:new.'||l_descTbl(i).col_name || '||qe';
    end loop; 
    null;
    cmd := replace(cmd,'<%UPDATE_COLS%>',expr);
    trg := replace(trg,'<%UPDATE_COMMAND%>',cmd);

    -- create the delete command
    expr := null;
    cmd := q'#'delete <%TABLE_NAME%>  where '||<%WHERE_CLAUSE%>#';
    trg := replace(trg,'<%DELETE_COMMAND%>',cmd);

    -- where clause using primary key columns (used by update and delete)
    expr := null;
    for pk in (SELECT column_name FROM all_cons_columns WHERE constraint_name = (
                  SELECT constraint_name FROM user_constraints 
                  WHERE UPPER(table_name) = UPPER(p_tname) AND CONSTRAINT_TYPE = 'P'
                )) loop

        if expr is not null then            
            expr := expr || q'#|| ' and '||#';
        end if;

        expr := expr || q'#'#' || pk.column_name || q'# = '||#'|| 'qs||:old.'|| pk.column_name || '||qe';
    end loop;
    if expr is null then -- must have a primary key
        raise_application_error(-20000,'The table must have a primary key defined');
    end if;

    trg := replace(trg,'<%WHERE_CLAUSE%>',expr);

    trg := replace(trg,'<%TABLE_NAME%>',p_tname);

    execute immediate trg;

    null;

exception
    when others then
        execute immediate 'alter session set nls_date_format=''YYYY/MM/DD'' ';
        raise;
end;

/* Example 

create table t1 (
col1    varchar2(100),
col2    number,
col3    date,
constraint pk_t1 primary key (col1)
)
/

BEGIN
  GEN_TRIGGER('T1');
END;
/

-- Trigger generated ....

create or replace trigger t1_audit after
    insert or
    update or
    delete on t1 for each row
declare
    qs      varchar2(20) := q'[q'^]';
    qe      varchar2(20) := q'[^']';
    command clob;
    nlsd    varchar2(100);
begin
    select value into nlsd from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
    execute immediate 'alter session set nls_date_format = ''YYYY/MM/DD hh24:mi:ss'' ';
    if inserting then
        command := 'insert into T1 (COL1,COL2,COL3) values ('||qs||:new.col1||qe||','||qs||:new.col2||qe||','||qs||:new.col3||qe||')';
    end if;
    if updating then
        command := 'update T1 set '||'COL1 = '||qs||:new.col1||qe||','||'COL2 = '||qs||:new.col2||qe||','||'COL3 = '||qs||:new.col3||qe||' where '||'COL1 = '||qs||:old.col1||qe;
    end if;
    if deleting then
        command := 'delete T1  where '||'COL1 = '||qs||:old.col1||qe;
    end if;
    insert into x_audit values
        (systimestamp, command
        );
    execute immediate q'+alter session set nls_date_format = '+'|| nlsd || q'+'+';            
end;

*/

Answer 3

该函数仅适用于讨论的“事件”触发器 这里。
您应该查看 细粒度审核 作为一种机制。详细信息此处

Answer 4

当触发代码运行时，您是否已经知道导致其运行的 dml？

    CREATE OR REPLACE TRIGGER Print_salary_changes
      BEFORE INSERT OR UPDATE ON Emp_tab
      FOR EACH ROW
      ...

在这种情况下，它一定是 emp_tab 表上的插入或更新语句。

确定它是更新还是插入

if inserting then
...
elsif updating then
...
end if;

确切的列值可在 :old 和 :new 伪列中找到。