0

  • I have a table with a Trigger that make a Auto_increment. 

    CREATE TABLE Auto 
   (    Auto_id NUMBER(*,0), 
        model_id NUMBER(*,0)
   );

    --Trigger

     CREATE OR REPLACE TRIGGER  tr_Auto_autoident 
  BEFORE INSERT ON  Auto 
  FOR EACH ROW 
   BEGIN  
     IF inserting THEN 
       IF :NEW.Auto_id IS NULL THEN 
           SELECT seq_auto__id.NEXTVAL INTO :NEW.Auto_id FROM dual; 
       END IF; 
    END IF; 
END;

  ALTER TRIGGER  tr_Auto_autoident ENABLE;

  • I create the sequence too.

-- Sequence 

   CREATE SEQUENCE seq_auto__id ;

I would like to set the autoident to a fix value to start , because my table had previos values before the trigger Auto_incremente were set.

Thanks , any help is apreciated.

Enrique Benito Casado
  • 1,914
  • 1
  • 20
  • 40
  • @ÁlvaroGonzález thanks !! i see it , I was looking for a command but i see that the solution ist just drop and create again. – Enrique Benito Casado Jan 19 '16 at 16:23
  • Or recreate the sequence with the [`start with` argument](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314), as the docs show. – Alex Poole Jan 19 '16 at 16:25
  • 2
    Dropping can have side effects (dependant objects get invalidated, grants are lost...) so I prefer ALTER SEQUENCE. – Álvaro González Jan 19 '16 at 16:28
  • 1
    Yes, I agree in general, this just looked like a new sequence/trigger being created to start auto-incrementing, so I'd build it into the initial object creation. As a separate issue, (a) the `IF :NEW.Auto_id IS NULL` means people can still insert without using the sequence, which may cause problems when the sequence reaches the manually-entered value; and (b) from 11g you can do `:NEW.Auto_id := seq_auto__id.NEXTVAL` instead of selecting from dual. – Alex Poole Jan 19 '16 at 16:48

0 Answers0