SELECT MAX() vs Oracle_SID

[sutherland]

I have an Oracle backend for an ASP application. I want to know about using SELECT MAX vs using Oracle_SID. I'm particularly interested in issues of concurrent users inserting records. From an SQL statement standpoint, SELECT MAX is easy to use. If I were to use Oracle_SID, what would be the steps involved? Would I have to create a pre-insert trigger for the Oracle_SID? How does this effect the SQL INSERT statement? Does the trigger handle creating the PK Index entry or do I still have to account for it in the SQL statement? Thanks!

[marist89]

Not really sure where ORACLE_SID comes in, but I am guessing you are referring to a sequence. Using SELECT MAX() poses one big problem with multi user apps; you can't GUARANTEE that two users won't get the same result for MAX(). This would lead to identical PK values.

The best solution is to use a BEFORE INSERT trigger and let the trigger pick a value off the sequence and insert that value into the table.