This discussion is archived
11 Replies Latest reply: Jan 24, 2007 10:17 AM by VANJ RSS

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

437868 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    60660 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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