I'm not the end-all be-all of Oracle expertise, so ITaking Tom's answers out of context don't invalidate the problems of a trigger"less" approach.
refer to those who are:
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 argueStill, what happens if you want to user other methods of access, for example sqlldr/external tables, ODBC/.NET, etc.?
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/scriptsTranslated: 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.
away from my data if possible :).
But on the chanceOr your's if other applications start generating error messages.
that they do go selecting from incorrect sequences it
will be their own problem.
It'll be refused insertFinding the source of the problem might be where unnecessary work has to be put in.
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 aCan't follow your argument, really. Maybe another agree to disagree topic?
trigger, I've done it myself. But on the whole, it's
best to avoid it when possible.
Taking Tom's answers out of context don't invalidateThe 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.
the problems of a trigger"less" approach.
Still, what happens if you want to user other methodsThey 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.
of access, for example sqlldr/external tables,
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 generatingThat'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 anotherWell I was pretty sure my statement summed it up quite well: "But on the whole, it's best to avoid it when possible."
agree to disagree topic?
Hi Ryan,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!
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
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.
CREATE OR REPLACE TRIGGER my_trg[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]
BEFORE INSERT OR UPDATE ON my_table
FOR each ROW
SELECT pk_seq.nextval INTO :new.rec_id FROM dual;
:new.inst_dt := SYSDATE;
:new.inst_usr := NVL(v('APP_USER'),USER);
:new.last_updt_dt := SYSDATE;
:new.last_updt_usr := NVL(v('APP_USER'),USER);