Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Automatic Value for Key - Trigger

    I'm fairly new to Oracle and have inherited a very non-normalized database.
    In adding tables, I need to have Record IDs that are unique created upon each insert.
    The value should be sequential.
    I assume I need a Trigger, based upon another table I have inherited.
    The trigger code looks like this:
    BEGIN
    SELECT prod1.S_11440_1_Claim_Input.nextval
    INTO :new.ID
    FROM dual;
    END;

    To me this says that it's storing a value in a prod1 Table called Claim_input
    but I can't see the table (I use TOAD and have admin rights).

    How do I set it up for a new table called CLIENT that has a CLIENT_ID?
    Could someone point me in the right direction?

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What the trigger is doing is to get the next available sequence number from the oracle sequence S_11440_1_Claim_Input and save it in the column ID in the new row that it is inserting.

    Code:
    CREATE OR REPLACE TRIGGER CLAIM_INPUT_T1
    Before INSERT
    ON CLAIM_INPUT
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    BEGIN
      SELECT prod1.S_11440_1_Claim_Input.nextval
      INTO :new.ID
      FROM dual;
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    OK - but if I have the new table CLIENT with CLIENT_ID,
    where do I get the value from?

    I'm assuming there's a separate (hidden?) lookup table for each table.
    The S_11440_1_Claim_Input sounds to me like a table. Is it automatically created?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It is not a hidden lookup table, it is an oracle sequence. The sequence generator provides a sequential series of numbers. The sequence generator is especially useful in multiuser environments for generating unique sequential numbers without the overhead of disk I/O or transaction locking. For example, assume two users are simultaneously inserting new employee rows into the employees table. By using a sequence to generate unique employee numbers for the employee_id column, neither user has to wait for the other to enter the next available employee number. The sequence automatically generates the correct values for each user. Therefore, the sequence generator reduces serialization where the statements of two transactions must generate sequential numbers at the same time. By avoiding the serialization that results when multiple users wait for each other to generate and use a sequence number, the sequence generator improves transaction throughput, and a user's wait is considerably shorter.
    Sequence numbers are Oracle integers of up to 38 digits defined in the database. A sequence definition indicates general information, such as the following:
    • The name of the sequence
    • Whether the sequence ascends or descends
    • The interval between numbers
    • Whether Oracle should cache sets of generated sequence numbers in memory
    Oracle stores the definitions of all sequences for a particular database as rows in a single data dictionary table in the SYSTEM tablespace. Therefore, all sequence definitions are always available, because the SYSTEM tablespace is always online.
    Sequence numbers are used by SQL statements that reference the sequence. You can issue a statement to generate a new sequence number or use the current sequence number. After a statement in a user's session generates a sequence number, the particular sequence number is available only to that session. Each user that references a sequence has access to the current sequence number.
    Sequence numbers are generated independently of tables. Therefore, the same sequence generator can be used for more than one table. Sequence number generation is useful to generate unique primary keys for your data automatically and to coordinate keys across multiple rows or tables. Individual sequence numbers can be skipped if they were generated and used in a transaction that was ultimately rolled back. Applications can make provisions to catch and reuse these sequence numbers, if desired.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Thanks Bill.
    The other item I found out is that it is independent of the table. It appears you can add any sequence var name you wish. All you need make sure of is in the trigger(s) you only utilize a specific sequence var name.

    I think I got it now.

    Dont understand why Oracle doesn't just use Sequence as a var type in the tables in the first place. Sure would avoid a lot of set up.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have total flexability using sequences. If you want to have something like a microsoft autonumber column, you can by using a table trigger and a sequence. If you want to use the same number across 20 tables you can. I have used both types and while an identity type column is easier to use, a sequence is much more powerful.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Identity columns are a pain if you try a restore from a logical backup, and forget to supress it's autonumbering behavior. It's frustrated me several times, highlighting a lack of thoroughness on my part. I prefer just having to disable a trigger.
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •