Changing the same column in two tables at the same time

user635344

Hi,


Oracle 10g2

I have 2 tables:

1. tab1 : Stores people info

2. tab2 : Stores people insurance info


Both have an address field.


What I need to do is when tab1.address is updated I want the tab2.address to update automatically and
when tab2.address is updated I want tab1.address to update.
Is there a way to do this using triggers without having these triggers mutate?

Thanks,
Oleg

Edited by: user635344 on Oct 8, 2009 11:04 AM

Centinul

I know this may not answer your question, but I have the following suggestions (in order of how I would do it):
<ol><li>Analyze the current design and see if there is a need to store two separate address fields that have the same data in them.</li>
<li>If you can't modify the design I would personally have the application call a stored procedure/package that I wrote that has the proper update statements in it because I don't like triggers.</li></ol>

Toon Koppelaars

Centinul wrote:
... I don't like triggers.

I'm with Centinul...

I don't like triggers that perform DML-statements (which would be the case if you do this with triggers).

I have no issues with triggers that perform Queries (usually to validate (non-declarable) constraints).

Toon

Centinul

Toon Koppelaars wrote:
I have no issues with triggers that perform Queries (usually to validate (non-declarable) constraints).

Good point! (Had to return the compliment)

Hoek

Agreeing fully with Centinul and Toon in the first place here, I'd make PEOPLE_ADDRESS an entity on it's own, with proper primary and foreign keys.
It's a quite common design (that's an understatement).
Your requirement is sort of 'screaming for that'.

Trying to get this done using triggers will get you/the users of the application in trouble sooner or later.
Why store the same data in more than one table?
You more or less want to 'update cascade' regardless whether parent or child table gets updated.
When such a requirement arises: just adjust your datamodel.

user635344

Hi,

It's working application.
Development team requires to add some functionality.

So I need to support the same value in 2 tables at the same time.

Thank you for your help,
Oleg

21205

user635344 wrote:
It's working application.

but not working very well?

Add the table, move the data from both tables into this new table and create foreign keys... if you are required to add some functionality, it might as well be this.

Centinul

Unless you can do what I suggested above (see #1, or #2) I think your "SOL."

Regularly defined triggers will fail with a mutating table error and autonomous triggers will generate a deadlock condition.

Hoek

Hi,

So I need to support the same value in 2 tables at the same time.

I'm very sorry, Oleg, but that goes into some modeling rules/principles.
If there's no possibility to put ADDRESS into a separate entity, in the end it's asking for trouble.
A 'trigger solution' will kill you or the ones maintaining that code after you.
You (or your superior) are asking for an update casecade that works both ways: parent => child and child => parent.
That's a design flaw.
It just won't work, it won't perform, it is asking for trouble.
Fix the datamodel.

Perhaps this might be of use:
http://tkyte.blogspot.com/search?q=update+cascade (scroll down a bit)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034

edit

Don't even try it. Just fix the model.

Edited by: hoek on Oct 8, 2009 9:00 PM

Toon Koppelaars

user635344 wrote:
So I need to support the same value in 2 tables at the same time.

Too bad is has to be "at the same time"...

Otherwise you could have opted for some daily batch-job that synchronized the lot
(saying this without enough detailed knowledge of your requirements).

What you are trying to implement is called Master-Master replication. A very tough problem to get 100% right. And certainly not one that we can quickly and easily provide you with example triggercode for.

Centinul

Toon Koppelaars wrote:
Otherwise you could have opted for some daily batch-job that synchronized the lot
(saying this without enough detailed knowledge of your requirements).

This begs the question which table is the "master" table if they can both update each other based on the OP?