CODEWITHSUNDEEP
oracle-database
Rodi Marcus
Rodi Marcus

Reputation: 107

oracle triggers :new context variable

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.

Upvotes: 0

Views: 207

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

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);

Upvotes: 1

Related Questions