1

I have a table called employees with the following data:

  Employee_ID     Employee_Salary       Employee_HireDate
  100              ----                  -----
  101              ----                  -----

Now I want to create a trigger that allows me to fetch the new employee_id and to increment it by one so it can be put in the table. I have found an example like this:

 CREATE OR REPLACE TRIGGER employee_b
 BEFORE INSERT ON employee
 FOR EACH ROW
 DECLARE
      v_employee_id employee.employee_id%TYPE;
 BEGIN
       SELECT employee_ID_SEQ.NEXTVAL
       INTO v_employee_id
           FROM dual;
       :NEW.employee_id:=v_employee_id;
 END;

But the error that I get is:

 ERROR at line 4: PL/SQL: ORA-02289: sequence does not exist

I want to use that trigger before an insert event like:

 INSERT INTO employee(employee_salary,employee_hiredate) VALUES (-----,------)

Any help with this? Thanks

Layla
  • 5,234
  • 15
  • 51
  • 66
  • It sounds like your sequence does not exist... Can you post the results of `select * from all_sequences where sequence_name = upper('employee_ID_SEQ')`? – Ben Nov 12 '12 at 20:56
  • Thanks for the comment, I have run that query and appears that "Data not found", how I can create a sequence in that case? – Layla Nov 12 '12 at 21:00
  • 1
    [The documentation](http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6015.htm#SQLRF01314) has a good description. – Ben Nov 12 '12 at 21:06
  • 1
    possible duplicate of [Auto Increment for Oracle](http://stackoverflow.com/questions/9733085/auto-increment-for-oracle) – Ben Nov 12 '12 at 21:06
  • How I can create a sequence based on the data of employee_id by using only SQL? – Layla Nov 12 '12 at 21:08
  • Thanks for the help, but it is possible to create a sequence in an already filled up table, the examples that I see refers only to new created tables – Layla Nov 12 '12 at 21:10
  • yes its possible. see my answer below. just set an appropriate start with value (ie max(employee_id) + 1) – DazzaL Nov 12 '12 at 21:11

1 Answers1

1

run

create sequence employee_ID_SEQ start with 100 increment by 1;

then compile the trigger and try it. read up on sequences and set an appropriate CACHE etc value (default cache of 20 is probably ok, if you're doing a ton of rapid inserts, you may want more).

DazzaL
  • 21,638
  • 3
  • 49
  • 57