How to use a trigger that gets the next value in a sequence

Question

I'm just learning Oracle and I understand the concept of triggers and sequences. What I can't seem to find is how they are used together in an auto-increment fashion similar to a SqlServer Identity setting. In a really simple example, I have a table called Employees that has three fields (all required): EmployeeID (PK), FirstName, LastName. I have created a sequence to get the next value for the ID field. I then created a trigger that looks like so:

CREATE OR REPLACE TRIGGER MyFirstTrigger
 BEFORE INSERT ON EMPLOYEES  FOR EACH ROW
BEGIN
   Select EMP_SEQ.NextVal into :NEW.EMPLOYEEID from dual;
END MyFirstTrigger;
/

However, how is this used in an insert statement? An insert statement with only the FirstName and LastName values fails for "Not enough values" (I'm using Toad, by the way). If I have to include EMP_SEQ.NextVal as the first value of the insert statement, what's the point of the trigger?

Hopefully, this makes sense. Thanks in advance.

Answer 1

This should work if not i will describe the possible reason of that and usage.

INSERT INTO EMPLOYEES (FirstName, LastName) VALUES ('Name', 'LastName')

and for sure You will be able to do this

   INSERT INTO EMPLOYEES (EMPLOYEEID , FirstName, LastName) VALUES (null, 'Name', 'LastName')

Answer 2

You can omit the ID from the insert, but you must specify the target columns:

insert into employees (firstname, lastname) values ('Tony', 'Andrews');

You should not have inserts without a column list in production code; however if you did you could just pass a NULL for the D column:

insert into employees values (null, 'Tony', 'Andrews');