"SQL command not properly ended" with trigger

Question

Problem

No matter what I do, I can not get my trigger to work. It seems to always complain about SQL command not properly ended, ignoring SQL statement and so on.

Research

I have spent hours looking into why this issue is occurring and I still have come up empty handed. This is my first time creating a TRIGGER so I have a feeling it has something to do with the placing of things.

What I have tried

I have tried many many things. I have tried adding a comma to the last variable in the brackets, I have tried adding ; to random statements and so on. This is my trigger:

CREATE OR REPLACE TRIGGER create_base_invoice
AFTER INSERT ON reservation
BEGIN
    INSERT INTO 
        invoice 
    VALUES
        (i.resnum, i.bnum, i.rnum, c.cfname, c.clname, i.bstartdate, null, null)
    SELECT 
        i.resnum, i.bnum, i.rnum, c.cfname, c.clname, i.bstartdate
    FROM 
        INSERTED i, CUSTOMER c
    WHERE(
       (i.cfname = c.cfname)
       AND
       (i.clname = c.clname)
        );
END create_base_invoice;

I do not understand the point of the SELECT command in my case and I have tried removing it and I do not get a different error so I am not sure if I need that there or not.

The reason I added it was due to stumbling across a post with a very similar scenario of mine and seeing the answer contain that SELECT statement which at the time I was missing.

The reason I need the where claus is because the Reservation table does not actually hold the customer name and surname (which we need in this case). It just holds the customer number. Here is the reservation table:

RESNUM
CNUM
BNUM
RNUM
BSTARTDATE
BDURATION
BOCCUPANTS
BSTATUS
BPAYMENT

I removed the VALUES clause, and now the error is

3/78 PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification

I removed ,null ,null too. It then complains

missing VALUES keyword

---

So, what I am trying to do is create a invoice based off of a reservation. When someone books a reservation, the trigger then saves certain variables from the reservation (resnum, bnum, rnum, bstartdate, cnum) and then it finds the customers full name based off their customer number (cnum).

All of this is saved into invoice to make a base invoice. Then I have another procedure that adds the checkout date and calculates total price of stay. We do not save cnum into invoice.

Just to clarify, cnum is just used to essentially search for the customer and find their first and last name which is then saved onto the invoice.

Answer 1

(Edited following comments and after checking what INSERTED is in SQL Server)

The INSERTED table in SQL Server represents the rows that were inserted by the triggering statement. Oracle doesn't have this. Instead, a row-level trigger can refer to the column values of each row affected by the trigger, using :new and :old values. (Only :new values are populated for inserts, and only :old for deletes.) Therefore I think what you need is something like this:

create or replace trigger create_base_invoice
after insert on reservation
for each row
begin
    insert into invoice
         ( resnum
         , bnum
         , rnum
         , cfname
         , clname
         , bstartdate)
    select :new.resnum
         , :new.bnum
         , :new.rnum
         , c.cfname
         , c.clname
         , :new.bstartdate
    from   customer c
    where  c.cnum = :new.cnum;
end create_base_invoice;

This creates an invoice for each new reservation, including details looked up from the customer table.

I do not understand the point of the SELECT command

There are two flavours of insert:

insert into tablename (col1, col2, col3) values (val1, val2, col3)

and

insert into tablename (col1, col2, col3)
select t2.col1, t2.col2, t2.col3
from   othertable t2
where  ...

One is for inserting fixed values, the other is for loading the results from a query.