Oracle Autoincrement with Sequence and Trigger Is Not Working Correctly

oracle autoincrement with sequence and trigger is not working correctly

Specifying the SEQUENCE with NOCACHE will stop a session caching 20 numbers at a time and help.

create sequence test_seq
start with 1 
increment by 1
NOCACHE;

However, if you're hoping for a completely contiguous sequence this is very difficult to achieve - numbers taken from the sequence are "lost" if (for example) an insert is rolled back.

---

Based on your comment, I wonder if you're forgetting to COMMIT?

Autoincrement in oracle with seq and trigger - invalid sql statement

I suspect the problem is that your client tool is reading every semicolon as the end of a command, causing PL/SQL code (which requires semicolons as statement terminators) to be incorrectly transmitted to the server.

When you remove the semicolon, the statement is correctly sent to the server, but you end up with an invalid object because the PL/SQL is incorrect.

I duplicated your problem on SQL Fiddle. Then I change the statement terminator to / instead of ; and changed the code to use a slash to execute each statement, and it worked without error:

CREATE TABLE proposals (
    proposal_id INT NOT NULL,
    target_audience VARCHAR2(50) NOT NULL,
    line_of_business VARCHAR2(50),
    activity_description VARCHAR2(250) NOT NULL,
    cost NUMBER(19, 4),
    comments VARCHAR2(250),
    objectives_and_results VARCHAR2(250),
    PRIMARY KEY (proposal_id)
)
/

CREATE SEQUENCE proposals_seq MINVALUE 1 
START WITH 1 INCREMENT BY 1 CACHE 10
/

CREATE OR REPLACE TRIGGER proposals_before_insert
BEFORE INSERT ON proposals FOR EACH ROW
BEGIN
    select proposals_seq.nextval into :new.proposal_id from dual;
END;
/

Oracle auto increment trigger issue

The correct way of doing this to modify your trigger as below:

CREATE OR REPLACE TRIGGER SPSSQUOTELINEITEMS_ON_INSERT
BEFORE INSERT ON SPSS_QUOTE_LINE_ITEMS
FOR EACH ROW
BEGIN

 :new.line_num := SPSS_QUOTE_LINE_ITEMS_SEQ.NEXTVAL;
 --No need to write any Select Into statement here.
END;

Or if i follow your way then it goes like

CREATE OR REPLACE TRIGGER SPSSQUOTELINEITEMS_ON_INSERT
BEFORE INSERT ON SPSS_QUOTE_LINE_ITEMS
FOR EACH ROW
declare
var number;

BEGIN

SELECT SPSS_QUOTE_LINE_ITEMS_SEQ.NEXTVAL
 INTO   var
 FROM   dual;

 :new.line_num :=var;

END;

You normally use the terms in a trigger using :old to reference the old value of the column and :new to reference the new value.

Oracle: auto-increment trigger.

Okay, I think I get what's going on. The answer to your question is an absolutely massive yes. There can be a large impact if you disable this trigger.

The reason this trigger seems to exist is to deal with the situation where a primary key value is not provided on an insert into your table. If this occurs anywhere in your code them removing the trigger will break those inserts.

You have to do two things.

1. Correct the trigger, it's obviously broken; fix it:

   CREATE OR REPLACE TRIGGER  BI_PRIVILEGE
     before insert on PRIVILEGE              
     for each row  
   begin   
     if :NEW.PRIVILEGE_ID is null then 
       select PRIVILEGE_SEQ.nextval into :NEW.PRIVILEGE_ID from dual; 
     end if; 
   end; 
   

   If you're using Oracle 11G or greater then you can use this instead:

     if :NEW.PRIVILEGE_ID is null then 
       :NEW.PRIVILEGE_ID := PRIVILEGE_SEQ.nextval; 
     end if; 
   

2. Work out whether this actually happens. If you do insert records without a primary key you need to find out why this happening and whether the behaviour is correct. If it is you're stuck with the trigger, otherwise fix that. If you never insert records without a primary key then you can disable the trigger.

   The quickest way of finding out may be to disable the trigger anyway but it would break your inserts. If this is a production database only you can tell whether it's worth it. I wouldn't personally.

ORACLE 10g auto increment trigger/sequence doesn't increment as programmed

Oracle sequences are not guaranteed to be gap-free. In fact, you can all but guarantee that there will occasionally be gaps.

In your case, you're using the default cache size of 20. When the database is shut down or the sequence cache is aged out of memory, whatever values were part of the sequence cache will be lost. So if the cache holds the numbers 1-20 initially, you do a nextval that returns a value 1, and the cache ages out of SGA before you call nextval again, you'd expect to get a value of 21 the next time you call nextval.

You can reduce the frequency of gaps by reducing the CACHE size on the sequence to 1. That will not eliminate gaps-- there will still, for example, be cases where a transaction gets rolled back, for example. And it will decrease performance. In general, you shouldn't be concerned with gaps since sequence-generated primary keys are, by definition, merely supposed to be unique identifiers.

Trigger in Oracle to auto-increment a field and insert into association table

You can use returning into clause of the insert statement to return users_id value after a new record has been inserted into table1. Also you can use user_seq.currval to get the current value of the sequence. Here is an example (In this example a simple stored procedure has been implemented to demonstrate the usage of insert into clause. You can implement a similar stored procedure according to your requirements):

SQL> create table Tb_table_1(
  2    user_id number primary key,
  3    field_1 number
  4  );

Table created

SQL> 
SQL> create table Tb_table_2(
  2    user_id number references tb_table_1(user_id),
  3    name1 varchar2(17)
  4  );

Table created

SQL> create sequence user_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue;

Sequence created

SQL> 
SQL> create trigger user_trigger
  2  before insert on tb_table_1
  3  for each row
  4  begin
  5    select user_seq.nextval into :new.user_id from dual;
  6  end;
  7  /

Trigger created

  SQL> create or replace procedure Insert_Record
  2  is
  3    l_cur_id number;
  4  begin
  5    insert into Tb_table_1(Field_1)
  6      values(123)
  7    returning user_id into l_cur_id; -- store user_id of the new inserted record
  8    for i in 1..5                    -- in a local variable for later use  
  9    loop
 10      insert into tb_table_2(user_id, name1)  -- insert a bunch of sample data into table2 using previously stored user_id.
 11        values(l_cur_id, dbms_random.string('l', 7));
 12    end loop
 13    commit;
 14  end;
 15  /

Procedure created

SQL> select * from tb_table_1;

   USER_ID    FIELD_1
---------- ----------

SQL> select * from tb_table_2;

   USER_ID NAME1
---------- -----------------

SQL> exec insert_record;

PL/SQL procedure successfully completed

SQL> select * from tb_table_1
  2  ;

   USER_ID    FIELD_1
---------- ----------
         1        123

SQL> select * from tb_table_2;

   USER_ID NAME1
---------- -----------------
         1 jzsdbna
         1 ozbibgs
         1 btxrxcm
         1 hxwwpzc
         1 sdjbwzi

SQL> 

In Oracle 11g onward you can directly assign sequence value to a variable:

:new.users_id := user_seq.nextval;

Auto-increment in Oracle without using a trigger

As far as I can recall from my Oracle days, you can't achieve Auto Increment columns without using TRIGGER. Any solutions out there to make auto increment column involves TRIGGER and SEQUENCE (I'm assuming you already know this, hence the no trigger remarks).

---

Related Topics