how to define a trigger that gives id automatically when insert a new person?

Question

as I described on the title, I want to write a trigger that defines to add a new staff by all giving attributes except ID, I want to trigger generate and insert it automatically. How can I do that?

I've written a code like below in PL/SQL, but it's including the sequence and I couldn't find how can I get the current max ID of my staff with using the sequence, so could you please help me, with or without using the sequence?

CREATE SEQUENCE BEFORE_INSERTING START WITH 1000 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER NEW_ID_BEFORE_INSERTING
BEFORE INSERT ON STAFF
FOR EACH ROW
BEGIN
:NEW.STAFF_ID := BEFORE_INSERTING.nextval;
END;
/

By the way, this code works fine but as you see it's starting from 1000.

Answer 1

Perhaps you can use something like the following to find the maximum value for STAFF_ID and then redefine the sequence based on that value:

DECLARE
  nMax_staff_id  NUMBER;
BEGIN
  SELECT MAX(STAFF_ID)
    INTO nMax_staff_id
    FROM STAFF;

  EXECUTE IMMEDIATE 'DROP SEQUENCE BEFORE_INSERTING';

  EXECUTE IMMEDIATE 'CREATE SEQUENCE BEFORE_INSERTING START WITH ' ||
                    nMax_staff_id + 1 || ' INCREMENT BY 1';
END;

You only need to run the above once, just to get the sequence reset. After that the trigger will use the sequence to obtain each STAFF_ID value. Note that there are other ways to redefine a sequence's value, but here we'll do The Simplest Thing That Could Possibly Work, which is to drop the sequence and then recreate it with the new starting value.

Best of luck.

Answer 2

in order to find the max STAFF_ID you need the below select:

select max(STAFF_ID) from STAFF;

Once you have the highest STAFF_ID you can re-create the sequence as desired.

In any case you can increment a sequence like so:

ALTER SEQUENCE seq_name INCREMENT BY 1;

I hope that helps!

Please don't hesitate to leave a comment for any further clarifications.

Ted.

Answer 3

Using the sequence guarantees uniqueness of STAFF_ID but does not guarantee no gaps in assigning STAFF_ID. You might end up with STAFF_ID like 100, 101, 103, 106..

First, get the max(STAFF_ID) while the system is not running. Something like

select max(staff_id) from staff;

Then, create the sequence to start from the max staff_id. Something like

create sequence staff_sequence start with <max_id> + 1 increment by 1 nocache;

"NOCACHE" minimizes the chance of having gaps in the staff_id assigned

After, use the trigger that you created to get the nextval from the seuqnece

Note the following: - Once a sequence is invoked for nextval, that number dispatched cannot be returned to the sequnece - Any cached sequence values will be lost if oracle database was shutdown

If your requirement is not to have gaps between staff_ids, then sequence might not be used.