Invalid identifier in trigger in my Oracle database

Question

I want to add an entry to the table, created a trigger so that it generates an id. But I get an error.

create or replace TRIGGER EMPLOYEE_ON_INSERT
  BEFORE INSERT ON "EMPLOYEE"
  FOR EACH ROW
BEGIN
  SELECT next_id("SEQ$SYS_BUYER_SEQUENCE".nextval, 'EMPLOYEE')
  INTO :NEW."ID"
  FROM dual;
END;

INSERT INTO EMPLOYEE (ID, FIRST_NAME, LAST_NAME) 
    SELECT KPFUDB.EMPLOYEE_ON_INSERT(), 'Ivanov', ' Ivan' 
    FROM dual;

Answer 1

You want:

create or replace TRIGGER EMPLOYEE_ON_INSERT
  BEFORE INSERT ON EMPLOYEE
  FOR EACH ROW
BEGIN
  :NEW.ID := SEQ$SYS_BUYER_SEQUENCE.NEXTVAL;
END;
/

then do not include the ID column in the INSERT as the trigger will overwrite any value you provide (or the default if you do not provide a value):

INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME)
SELECT 'Ivanov',' Ivan' from dual;

---

Or, from Oracle 12, you do not need a trigger and can use an IDENTITY column:

CREATE TABLE employee(
  id         NUMBER
             GENERATED ALWAYS AS IDENTITY
             PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name  VARCHAR2(20)
);

And a similar INSERT statement.

db<>fiddle here