Updating unique key

[anniez]

Two files have the same unique idfield (each has many other fields - TableA has statewide data/TableB has locally maintained fields). Several other files have multiple records with this idfield in them.
Often - A temporary IDfield is assigned on admission. When the real IDnumber is assigned the tempID has to be changed in all files.
Can you tell me the trigger syntax/statement order
A) if the Real_ID_already existed. I'd have to update the real_id record with the new data then delete the temp_id record and
B) if it's a new ID.

 

[jee]

I assume you mean that other tables have foreign key constraints defined that reference TableA or Table B???

The first couple of solutions I would propose involve redesigning your database a little, which may not be possible.

1) I would use "artificial" keys that don't change in your TableA and TableB (values can be generated using a Sequence) - and put the ID that does change in another column in TableA and TableB. Then you only have to change this ID value once (well, twice, since it is in two tables) which requires only two update statements, no triggers, etc.

2) Another way is to create the foreign key constraints as "deferrable" and to do a "Deferred" update. This means that when your ID changes from a temporary value to a permanent value, you can just update all the rows in all tables that use the temporary value to the permanent value and then commit - and the FK constraint will not be enforced until the commit - so you can simply make the change using update statements - and do so in any order. You can read more on deferred updates in the "Oracle 8i Application Developer's Guide - Fundamentals".

3) If you can't "redesign" you could disable the FK constraints, make the changes using updates and then enable the FK constraints again when done.

4) If you don't have the privs to muck with the constraints, or it will be too big of a performance hit you can do this (assuming there is no row yet for the real ID):

- Add a new row in TableA and TableB for the Real ID, copying data from the old rows.
- Update rows in other tables that reference TableA and TableB via the tempID to the realID.
- Delete the tempID row from TableA and TableB

What you would do if there is a row already for the permanent ID would depend on your business rules - I would assume that at the least you would want to

- Possibly update the realID rows in TableA and TableB from the tempID rows???
- Update rows in other tables that reference TableA and TableB via the tempID to the realID.
- Delete the tempID row from TableA and TableB.


I am not sure how you would do all of this using triggers - possibly you could use an "Instead of" trigger?

 

[anniez]

Thanks for the suggestions. The best solutions aren't always feasible. It'll have to be #4.
Can you Help with syntax for the trigger?

Before update (?)
If the realid exists in tablea (?)
update fields x,y,z where it matches :new(ok)
update other files (ok)
delete oldid record (ok)
else (?)
update other files
delete oldid record - can I delete it if the trigger is before update (???)
end if (?)


Thank you