Database Administrators Asked by ExileVoid on September 28, 2020
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.
(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.
Correct answer by William Robertson on September 28, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP