This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Feb 26, 2013 2:26 AM by Paul Horth Go to original post RSS
  • 15. Re: Trigger on a table doesnt update last inserted
    Paul Horth Expert
    Currently Being Moderated
    Matt Valkonn wrote:
    BluShadow wrote:
    I can't quite tell what your trigger is trying to do, but it looks something like you are trying to create some sort of sequence on your records.... let me guess... you're trying to create a gapless sequence for a group of records?
    Exactly. I need to have gapless sequence of inserted rows.
    Unfortunately every young enthusiastic programmer who has ever tried to do so scaleably and reliably has ended up an old embittered programmer who tells everyone else not to try. :-)

    Seriously, you don't want to do that. Whatever you think you need gapless sequences for, you don't.
  • 16. Re: Trigger on a table doesnt update last inserted
    Mihael Pro
    Currently Being Moderated
    No. What benefit would that give?
    He will be able to select from base table.
    Would you care to demonstrate how that's going to help him create a gapless sequence for his records?
    OKay. This was only the method how to avoid "mutating table" errors instead of using autonomous transaction.
  • 17. Re: Trigger on a table doesnt update last inserted
    991289 Newbie
    Currently Being Moderated
    BluShadow wrote:
    Ah, you replied while I was typing... :)
    Matt Valkonn wrote:
    Why i use pragma autonomous_transaction? Because i get mutating trigger error otherwise.
    Then that's a good indication that you shouldn't be using a trigger, and you're doing your process in the wrong place.
    What i need to do is update each record with an id_nr which is a number (without gaps) after each record is inserted.
    When you try and create an id that is 'gapless' then you are going to have to account for the fact that this prevents the application from working in a multi-user environment (albeit temporarily). In which case you will need to, in essence, lock the table (or group of records) to prevent other users from accessing them, update your id's as required, then commit and release the records for others.

    But firstly, a question... why do the id's need to be gapless? What purpose would a gapless id serve?
    I know that using sequesnce is the proper aproach but i am building a report which has to have a proper numbering of the records for further processing (i need to know and show on the report from which line i am dedusting values or adding them ... ).

    I solved that "multi-user" problem so thats not an issue.

    So so far you advice agains using this kind of an update in trigger ... i should put it back to procedure as it was before and work from there on?
  • 18. Re: Trigger on a table doesnt update last inserted
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Matt,

    I have succesfully used a 'before insert' trigger, where I simply check on the new record that is about to be inserted if the ID has a value or not. If it doesn't, I use the next value form the sequence, if it does, I leave it alone and don't do anything (because in that case the application or user that is doing the insert has already taken care of it).

    You could extend that check with a quick check on sequentiality I suppose.

    HTH,
    Stefan
  • 19. Re: Trigger on a table doesnt update last inserted
    Paul Horth Expert
    Currently Being Moderated
    Matt Valkonn wrote:
    BluShadow wrote:
    Ah, you replied while I was typing... :)
    Matt Valkonn wrote:
    Why i use pragma autonomous_transaction? Because i get mutating trigger error otherwise.
    Then that's a good indication that you shouldn't be using a trigger, and you're doing your process in the wrong place.
    What i need to do is update each record with an id_nr which is a number (without gaps) after each record is inserted.
    When you try and create an id that is 'gapless' then you are going to have to account for the fact that this prevents the application from working in a multi-user environment (albeit temporarily). In which case you will need to, in essence, lock the table (or group of records) to prevent other users from accessing them, update your id's as required, then commit and release the records for others.

    But firstly, a question... why do the id's need to be gapless? What purpose would a gapless id serve?
    I know that using sequesnce is the proper aproach but i am building a report which has to have a proper numbering of the records for further processing (i need to know and show on the report from which line i am dedusting values or adding them ... ).

    I solved that "multi-user" problem so thats not an issue.

    So so far you advice agains using this kind of an update in trigger ... i should put it back to procedure as it was before and work from there on?
    What further processing? Could those line numbers be generated "on the fly" by row_number?
  • 20. Re: Trigger on a table doesnt update last inserted
    991289 Newbie
    Currently Being Moderated
    Ok guys,

    i have fixed the problem by creating procedure instead of trigger and so far it works as intended.

    Thank you all for your time!
  • 21. Re: Trigger on a table doesnt update last inserted
    Paul Horth Expert
    Currently Being Moderated
    Matt Valkonn wrote:
    Ok guys,

    i have fixed the problem by creating procedure instead of trigger and so far it works as intended.

    Thank you all for your time!
    So, you have a locking mechanism to prevent multiple users from inserting/updating the table at the same time?

    How many concurrent users do you expect on that table?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points