Can I see the DML inside an Oracle trigger?

Is it possible to see the DML (SQL Statement) that is being run that caused a trigger to be executed?

For example, inside an INSERT trigger I would like to get this:

"insert into myTable (name) values ('Fred')"

I read about ora_sql_txt(sql_text) in articles such as this but couldn't get it working - not sure if that is even leading me down the right path?

We are using Oracle 10.

Thank you in advance.

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

[EDITED] MORE DETAIL: We have the need to replicate an existing database (DB1) into a classified database (DB2) that is not accessible via the network. I need to keep these databases in sync. This is a one-way sync from (DB1) to (DB2), since (DB2) will contain additional tables and data that is not contained in the (DB1) system.

I have to determine a way to sync these databases without bringing them down (say, for a backup and restore) because it needs to stay live. So I thought that if I can store the actual DML being run (when data chang开发者_如何学Goes), I could "play-back" the DML on the new database to update it, just like someone was hand-entering it back in.

I can't bring over all the data because of the sheer size of it, and I can't just copy over the changed records because of FK constraints and the order in which I insert/update records. I figured that if I could "play-back" a log of what happened, using the exact SQL that changed the master, I could keep the databases in sync.

My current plan of attack was to keep a log of all records that were changed, inserted, and deleted and when I want to sync, the system generates DML to insert/update/delete those records. Then I just take the .SQL file to the classified system and run the script. The problem I'm running into are FKs. (Because when I generate the DML I only know what the current state of the data is, not it's path to get there - so ordering of statements is an issue). I guess I could disable all FK's, do the merge, then re-enable all FK's...

So - does my approach of storing the actual DML as-it-happens suck pondwater, or is there a better solution???

"does my approach of storing the actual DML as-it-happens suck pondwater?" Yes..

3. Strict ordering of the DML on your DB1 does not really exist. Multiple processes, muiltiple cores, things essentially happening at the essentially the same time.

4. And the DML, even when it happens sequentially doesn't act like it. Say the following two update statements run in seperate processes with seperate transactions, where the update in transaction 2 starts before transaction 1 commits:

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

Since the changes made in the first transaction are not visibible to the second transaction, the rows changed by the second transaction will not include those of the first. But if you manage to capture the DML and replay it sequentially, transaction 1's changes will be visible, so transaction 2's changes will be different. (See pages 40 and 41 of Tom Kyte's Expert Oracle Database Architecture Second Edition.)

3. Hopefully you are using bind variables, so the DML by itself wouldn't be meaningful: update table_a set col_a = :col_a where id = :id Now what? Ok, so you want the DML with it's variable bindings.

4. Do you use sequences? If so, the next_val will not stay in synch between DB1 and DB2. (For example, instance failures can cause lost values, are both systems going to fail at the same time?) And if you are dealing with RAC, where the next_val varies depending on node, forget it.

I would start by investigating Oracle's replication.

I had a situation where I needed to move metadata/configuration changes (stored in a handful of tables) from a development environment to a production environment once tested. Something like Goldengate is the product to use for this but this can be costly and complicated to set up and administer.

The following procedure generates a trigger and attaches it to a table that needs the DML saved. The trigger re-creates the DML and in the following case saves it to an audit table - its up to you what you do with it. You can use the statements saved to the audit table to replay changes from a given point in time (cut and paste or develop a procedure to apply them to the target).

Hope you find this useful.

    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;

*/

That function only works for 'event' triggers as discussed here. You should look into Fine-Grained Auditing as a mechanism for this. Details here

When the trigger code runs don't you already know the dml that caused it to run?

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

In this case it must have been an insert or an update statement on the emp_tab table.

To find out if it was an update or an insert

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

The exact column values are available in the :old and :new pseudo-columns.