Problems Creating a trigger using Oracle

Question

My question is:

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 theses 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 ggenerateKey
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
agentName varchar2(50);
modelName varchar2(50);
manufacturerName varchar2(50);
askingprice number;
agreedprice number;
begin
select sa.name, mo.name, mu.name, c.askingprice, st.agreedprice
into agentName, modelName, 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
(ggenerateKey.nextval, agentName, modelName, manufacturerName);
END IF; 
end AutoStore;
/

The trigger compiles and when I try to test this I use these values that will be inserted into SalesTransaction, this should then fire the trigger but shows as an error.

insert into SalesTransaction
values
('2B7JB33R9CK683376', '1', '1', to_date('01-02-2013','dd-mm-yyyy'), 586000 );

The error shown is this

insert into SalesTransaction
            *
ERROR at line 1:
ORA-01403: no data found 
ORA-06512: at "JTLA.AUTOSTORE", line 8 
ORA-04088: error during execution of trigger 'JTLA.AUTOSTORE'

Firstly, your trigger is a before insert trigger on the SalesTransaction table, but you're trying to join the SalesTransaction table in the select statement in your trigger body. Surely you know the agreedprice and vin? They're :new.agreedprice and :new.vin. Next, is SalesAgent really relevant to the other tables in the select? I think you would probably need to pull that table out into its own select statement. Finally, you would do well to consider what should happen if you don't get a row back from any of your selects. Should it error? Should you use default values? Something else? — Boneist
– Boneist, Commented May 26, 2015 at 10:29

Павел Нохрин · Accepted Answer · 2015-05-26 22:42:18Z

0

This error means that your select statement with "into" clause produces no rows. To avoid this error, I usualy use aggregate function MAX() on each column, it garante 1 row result set, generates null values in case of 'no matching rows', and no exception rised

answered May 26, 2015 at 22:42

Павел Нохрин

111 bronze badge

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

Collectives™ on Stack Overflow

Problems Creating a trigger using Oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related