Can I see the DML inside an Oracle trigger?

Question

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 changes), 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???

Answer 1

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

Answer 2

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.

Answer 3

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

1. 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.

2. 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.)

1. 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.

2. 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.