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