oracle triggers :new context variable

Question

Question:

A Trigger which automatically stores in a separate table called ‘ExcellentSale’ the Sales Agent name, car model and manufacturer name, each time the agreed price of a SalesTransaction is more than 20% of the car’s asking price. (Note: You need to create the ‘ExcellentSale’ table before implementing this trigger. To create the primary key, use a sequence that starts at 1 and increments by 1).

I am using these tables

Manufacturer(manufacturerID, name, region)

Model(modelNo, name, type, previousModel, manufacturerID)

Car(VIN, dateAcquired, yearBuilt, purchasedPrice, askingPrice,
currentMileage, modelNo)

SalesAgent(agentID, name, DOB)

SalesTransaction(VIN, custID, agentID, dateOfSale, agreedPrice)

Here is my attempt

create sequence generateKey
start with 1
increment by 1;
CREATE TABLE ExcellentSale(
recordNo NUMBER,
agentName VARCHAR2(20) NOT NULL,
modelName VARCHAR2(20) NOT NULL,
manufacturerName VARCHAR2(20) NOT NULL,
PRIMARY KEY(recordNo));
create or replace trigger AutoStore
before insert on SalesTransaction
for each row
declare
SAname varchar2(50);
carModel varchar2(50);
manufacturerName varchar2(50);
askingprice number;
agreedprice number;
begin
select sa.name, mo.name, mu.name, c.askingprice, st.agreedprice
into SAname, CarModel, manufacturerName, askingprice, agreedprice
from manufacturer MU, Model MO, Car C, SalesAgent SA, SalesTransaction ST
where mu.manufacturerid = mo.manufacturerid
and st.vin = c.vin
AND c.vin = :new.vin
AND sa.agentID = :new.agentID;
IF :new.agreedPrice > (1.2 * askingPrice) THEN 
INSERT INTO ExcellentSale
VALUES
(generateKey.nextval, agentName, modelName, manufacturerName);
END IF; 
end AutoStore;
/

and the error I am getting is

PL/SQL: ORA -00984: Column not allowed here

please help, thank you in advanced.

Once you've fixed that, you're missing some join conditions, e.g. between C and MO. It would be more obvious those were missing if you used ANSI join syntax, rather than the old implicit joins. — Alex Poole
– Alex Poole, Commented May 26, 2015 at 7:18

Erich Kitzmueller · Accepted Answer · 2015-05-26 07:51:44Z

1

In the VALUES clause of the insert statement, you have go use the variables that hold the values you need instead of the column names.

INSERT INTO ExcellentSale
VALUES
(generateKey.nextval, SAname, CarModel, manufacturerName);

edited May 26, 2015 at 7:51

answered May 26, 2015 at 5:44

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

oracle triggers :new context variable

Question:

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Question:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related