11 Replies Latest reply: Jan 24, 2007 12:17 PM by VANJ RSS

    Best Practice PK Generation: 'before insert on trig' vs. rolling yer own

    437868
      Experts,
      I have a form with the standard Create/Delete/Apply Changes buttons.

      When someone hits "Create", I have a trigger that fires on the table as "before insert on". It hits a sequence to get a new primary key for the record which is about to be created.

      Another way of doing it is to run a page-level PL/SQL computation, and get SEQUENCE_NAME.Nextval, and stuff it into the appropriate page-level item. "Create" will then fire the insert after this computation and it amounts to the same thing as the trigger.

      Loaded question: Which way is better?

      My DBA recommends to "roll my own" using the page-level computation. His reasoning is if my applications scale up to a lot of users running inserts at the same time, the trigger will be a bit of a drag.

      On the other hand, I like having the trigger "on the database" and not having to worry much about it. It is one less thing that I have on my page.

      Dan
        • 1. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer own
          477177
          Dan,
          You have to keep in mind always that you have a sequence and a trigger.
          When you create a form thru wizard and by mistake you choose the 'Source type' to be 'Existing Sequence', then you have double trouble. The process will get the primary from sequence.nextval and so does the trigger before insert, so bump the key by 2 than 1. Ran into lots of issues like this.

          I'll go with Page process. IMHO
          SUNDARK
          • 2. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
            135285
            Hi Dan,

            contrary to the advice of your DBA I would recommend using a trigger to fill the PK column.

            Why?
            -) If you have other programs/scripts/... which insert into this table, they don't have to worry about getting the right sequence to populate the primary key => the "table" knows which sequence has to be used

            -) I doubt that the page-level computation of APEX is faster than a table trigger. Keep in mind that APEX has to execute all the code with dynamic sql. That means the anonymous PL/SQL block it creates has to be parsed at runtime and the anonymous pl/sql code will trigger Oracle to parse your select for the sequence to be able to execute it. For the DB-trigger, the pl/sql code is pre-compiled and just the select for the sequence has to be parsed when the trigger is executed. So what will have less CPU impact on your database? :-)

            -) Another reason is to separate business logic from the GUI. So if you don't use packages to wrap your tables use at least the trigger to populate this technical attributes.

            Hope that gives you some arguments for your DBA :-)
            Patrick
            ------------------------------------------------------------------------------------
            Check out my APEX-blog: http://inside-apex.blogspot.com

            Message was edited by:
            patrick_wolf
            • 3. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer own
              437868
              Patrick,

              1. Regarding other programs/scripts doing CRUD...that is a real possibility. I have some projects coming up where I'm going to implement web services and let other applications "hook" into them. I'll also have APEX running as a quick/dirty admin tool for me or my dba.
              So I'll still have the APEX front-ends, but just about any application could be running the web service.

              2. Page Level computation vs. db trigger. I had not though of that. Both methods have to fire the "select sequence.nextval into blah from dual". So it boils down to how much work needs to be done to get to that point, and how much dynamic SQL needs to get parsed.

              Am I understanding that part correctly?

              3. Separation of concerns, yes that is my current reasoning for using the trigger. It is how I did things back when i was doing java stuff.

              Dan
              • 4. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
                135285
                Dan,

                you are correct. APEX has to use dynamic sql to issue the "select sequence.nextval into blah from dual", the db trigger has it already pre-compiled.

                But I think the other arguments are much more important why db triggers should be used. It's just a technical detail/background.

                Patrick
                • 5. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
                  ryan.reynolds
                  This is Dan's DBA - you thought you could escape me?? <evil laugh>

                  "page-level computation of APEX" aside (I'm not the apex expert, not sure of its design), the question is whether it's faster to modify your INSERT statement to include the one extra column and sequence.nextval versus having a trigger do it.

                  DON'T do:
                  1) Some dynamic PL/SQL block with only 'select sequence.nextval from dual into seqvariable' then,
                  2) Another dynamic PL/SQL block with 'insert into mytable (col1, col2, col3) values (seqvariable, val2, val3)'

                  Instead modify your insert to contain the seq.nextval:
                  'insert into mytable (col1, col2, col3) values (sequence.nextval, val2, val3)'
                  That will be the fastest way to do it.

                  I'm not the end-all be-all of Oracle expertise, so I refer to those who are:
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4343369880986#34562653805149
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4343369880986#76218085672062
                  A couple links I could quickly find on the subject. I'm sure there's plenty more out there.

                  As to the other concerns, personally I would argue that a separation between application logic and GUI shouldn't go so far as to remove all data management concerns, such as using primary keys (and otherwise indexed fields) to access data.

                  Also, I prefer to keep rogue applications/scripts away from my data if possible :). But on the chance that they do go selecting from incorrect sequences it will be their own problem. It'll be refused insert until it can (randomly, I don't care) provide a unique value for that column. Yay for constraints!


                  I agree there are a few valid uses for such a trigger, I've done it myself. But on the whole, it's best to avoid it when possible.
                  • 6. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
                    cd_2
                    I'm not the end-all be-all of Oracle expertise, so I
                    refer to those who are:
                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P1
                    1_QUESTION_ID:4343369880986#34562653805149
                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P1
                    1_QUESTION_ID:4343369880986#76218085672062
                    A couple links I could quickly find on the subject.
                    I'm sure there's plenty more out there.
                    Taking Tom's answers out of context don't invalidate the problems of a trigger"less" approach.
                    As to the other concerns, personally I would argue
                    that a separation between application logic and GUI
                    shouldn't go so far as to remove all data management
                    concerns, such as using primary keys (and otherwise
                    indexed fields) to access data.
                    Still, what happens if you want to user other methods of access, for example sqlldr/external tables, ODBC/.NET, etc.?
                    Also, I prefer to keep rogue applications/scripts
                    away from my data if possible :).
                    Translated: Everyone has to incorporate sequences in any INSERT statement. For BULK inserts, this maybe the way to go, but you can always check if the PK column is NULL, which should give the trigger a bit of performance improvement.
                    But on the chance
                    that they do go selecting from incorrect sequences it
                    will be their own problem.
                    Or your's if other applications start generating error messages.
                    It'll be refused insert
                    until it can (randomly, I don't care) provide a
                    unique value for that column. Yay for constraints!
                    Finding the source of the problem might be where unnecessary work has to be put in.
                    I agree there are a few valid uses for such a
                    trigger, I've done it myself. But on the whole, it's
                    best to avoid it when possible.
                    Can't follow your argument, really. Maybe another agree to disagree topic?

                    C.
                    • 7. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
                      ryan.reynolds
                      Taking Tom's answers out of context don't invalidate
                      the problems of a trigger"less" approach.
                      The primary question I was quoting Tom about was the performance issue, which he addresses directly. The context is: should I use a trigger or use the sequence in this INSERT statement I'm writing. It's quite the similar situation.

                      Still, what happens if you want to user other methods
                      of access, for example sqlldr/external tables,
                      ODBC/.NET, etc.?
                      They may be the exceptions, I'm not 100% familiar with them. Though I do know that sql*ldr can gen its own sequences, and I would be suprised to learn that you can't access seq.nextval through ODBC.
                      Translated: Everyone has to incorporate sequences in any INSERT statement.
                      If you're using a sequence for PK, and it's possible to do so, YES!
                      For BULK inserts, this maybe the way to go, but
                      you can always check if the PK column is NULL, which
                      should give the trigger a bit of performance improvement.
                      Or your's if other applications start generating
                      error messages.
                      That's a good point. But valid in either a trigger or a triggerless approach. Assuming that the trigger is going to fire 'when pkcol IS NULL', bad data (ie what could be a future sequence value) could still be provided.

                      Can't follow your argument, really. Maybe another
                      agree to disagree topic?

                      C.
                      Well I was pretty sure my statement summed it up quite well: "But on the whole, it's best to avoid it when possible."
                      More specifically, I think I am arguing for a "Triggerless approach with exceptions" whereas you are arguing for a "Trigger everything" approach. I apologize if I'm mischaracterizing your argument.
                      • 8. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
                        135285
                        Hi Ryan,

                        the original question was "page-level computation" or trigger. The option "modify your insert statement" is no real option if you want to use the wizard created DML processes of APEX. Sure you could replace them with your own PL/SQL code for insert/update/delete, optimistic locking check, ... but that is a lot of work. I think doing all this extra work will not be in any relation to the minimum performance improvement.

                        I also do not think that a table where users are manually entering data will have that huge performance impact if they use a db trigger or if they use the sequence in the insert statement.

                        For the other points I confirm with cd.

                        What is the reason why you want to avoid triggers?

                        Patrick
                        ------------------------------------------------------------------------------------
                        Check out my APEX-blog: http://inside-apex.blogspot.com
                        • 9. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
                          ryan.reynolds
                          Hi Ryan,

                          the original question was "page-level computation" or
                          trigger. The option "modify your insert statement" is
                          no real option if you want to use the wizard created
                          DML processes of APEX. Sure you could replace them
                          with your own PL/SQL code for insert/update/delete,
                          optimistic locking check, ... but that is a lot of
                          work. I think doing all this extra work will not be
                          in any relation to the minimum performance
                          improvement.

                          I also do not think that a table where users are
                          manually entering data will have that huge
                          performance impact if they use a db trigger or if
                          they use the sequence in the insert statement.
                          Ah I see. Makes sense now. The question as originally posed to me was, "all things being equal, which approach do you use." If, in using Apex, it would be a huge amount of extra work just to avoid the triggers, then I would probably use the triggers too!
                          • 10. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer own
                            437868
                            I appreciate the discussion, guys.

                            I'll use triggers for now. I'm mostly an ASP/SQL Server refugee. While a lot of this stuff makes sense I've got a ways to go on Oracle.

                            Dan
                            • 11. Re: Best Practice PK Generation: 'before insert on trig' vs. rolling yer ow
                              VANJ
                              For what it is worth, I agree with "cd" for the reasons already stated.

                              Tom's position on triggers is (IMHO) unnecessarily harsh/extreme. Triggers have their place and this is one of them. They shouldn't be abused to do things that are better suited to be in a API (which, IIRC, is how Tom prefers all front-end applications should do DML).

                              Generating a sequence-generated (surrogate) PK and stamping update user/date on a record is definitely best done in a trigger.
                              CREATE OR REPLACE TRIGGER my_trg
                              BEFORE INSERT OR UPDATE ON my_table
                              FOR each ROW
                              BEGIN
                                  IF (INSERTING)
                                  then
                                      SELECT pk_seq.nextval INTO :new.rec_id FROM dual;
                                      :new.inst_dt  := SYSDATE;
                                      :new.inst_usr := NVL(v('APP_USER'),USER);
                                  END IF;

                                  IF (UPDATING)
                                  THEN
                                      :new.last_updt_dt  := SYSDATE;
                                      :new.last_updt_usr := NVL(v('APP_USER'),USER);
                                  END IF;
                              END;
                              /
                              [I had this reply in a tab and got distracted and came back and hit the Post button, only to see that the discussion had already been taken to its conclusion. Sorry, didn't mean to re-open it]

                              Thanks