CODEWITHSUNDEEP
oracle-databasetriggersnextval
Layla
Layla

Reputation: 5446

oracle issue with trigger and nextval

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

Upvotes: 1

Views: 2747

Answers (1)

DazzaL
DazzaL

Reputation: 21973

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).

Upvotes: 1

Related Questions