Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011

    Question Unanswered: Duplicate key - Trigger

    Good morning. I'm having a big problem.
    I'll try to explain the problem.
    I am currently migrating a legacy system that has over 2000 tables.
    The tables do not have sequence linked to the primary key. When an insertion is made, the application performs a "MAX (id)" and insert triggers. That's too bad, because the generation IDs are linked to the application and not the bank.
    Our company, which is responsible for this migration is reshaping the entire database by removing inconsistent data, creating FK's.
    The database has two tables, the current table (which is old table) and the new table (remodeled), and these two tables must contain the same data, because we will release the new system modules.
    When launching a new module, both the old system as the new system may use a single table.
    When recording in the table of old application, the data should also be recorded on the new table.
    I'm passing the following problem:
    When I do an insert for the current application and at the same time I do an insert in the old application, in the same table, the ID ends up being "lost."
    Old app
    Insert table in doc
    Before trigger set id = max (id) +1
    After insert trigger in new table => id = max (id +1)

    Insert table in doc
    Before trigger set id = max (id) +1
    After insert trigger in old table => id = max (id +1)

    If the insert is done at the same time, there will be replication ids.
    Remember that you can not put a SEQUENCE in the old table, because the moorings of the system are made via the JPA and not internally in the bank.
    Has anyone gone through this problem? Do you have any possible solution to share?

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    I'm assuming that you don't really need consecutive IDs. You could allocate a pool of IDs for one of the tables, e.g. in the new app
    Before trigger set id = max (id) +1000001
    Just make sure that the number is large enough to accommodate insertions in the old table.

  3. #3
    Join Date
    Mar 2011


    Your idea would work if the data in a table were not synchronized, ie the old record in the table has a trigger that writes the same data in new table.
    MAX takes the highest value and adds 1, ie, will give the same problem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts