How does Oracle Streams prevent the execution of code during the distribution process?

bitschnau asked:

I thought about Oracle Streams and stumpled into a question.

Oracle Streams is used on the database I am programming for, but I am not the DBA. Therefore I am not familiar with the configuration of the database. I just have a “simple” idea, of what Streams actually does.

In my sight it preserves data consistence among several distributed databases. Now for my question:

Let’s assume we have to databases A and B matched by Oracle Streams. Both are built from the same schemas, tables and triggers. If there is an insert on Table T1 in database A, the data is distributed to Table T1 in database B via Streams.

But what if there is an insert-trigger TR1 for Table T1. It is sureley executed on database A on the insertion in T1, but what happens on database B, if streams distributes the data form A to B? Is the trigger executed? I guess not, because there is a whole lot of trouble possible in this.

Does Streams prevent any execution of code during data distribution through itself?

My answer:

What happens depends on whether the trigger is set to fire once.

If the trigger is set to fire once, then the changes get applied to database A and Streams faithfully replicates them to database B. This sounds like what you want.

Otherwise, the trigger fires again on database B after Streams replicates the change that caused the trigger to fire on database A.

Which method is appropriate for any particular case depends on what the trigger does.

See Oracle’s documentation for a fuller explanation and another example.

View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.