PL/Sql query execution error

Question

This is my query. 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). It shows error like this

ERROR at line 23: PL/SQL: ORA-00942: table or view does not exist
1. create or replace trigger filltable
2. after insert on salestransaction
3. for each row
4. declare

The code for the trigger is:

create or replace trigger filltable
after insert on salestransaction
for each row
declare
aname varchar2(25);
modname varchar2(25);
manfname varchar2(25);
askpr number;
agrpr number;
begin
select sa.name,m.name,mf.name
into   aname,modname,manfname
from   manufacturer mf,model m, car c, salestransaction st, salesagent sa
where mf.manufacturerid = m.manufacturerid and
m.modelno = c.modelno and
c.vin = st.vin and
st.agentid = sa.agentid;

select askingprice,agreedprice
into askpr,agrpr
from car c,salestransaction st
where c.VIN = St.vin;

if(agrpr > askpr*1.2) then

insert into excellentsales values(agent_seq.nextval,aname,modname,manfname);

end if;

end filltable;

/


create table excellentsales
(agentid varchar2(5) not null,
agentname varchar2(25),
carmodel varchar2(25),
mfname varchar2(25),
primary key(agentid))

CREATE SEQUENCE agent_seq START WITH 1 INCREMENT BY 1;

First create the table then sequence and finally execute your trigger. — Sagar Joon
– Sagar Joon, Commented May 27, 2015 at 16:40
does the user has insert, select previleges for all the tables specified? — Sachu
– Sachu, Commented May 27, 2015 at 16:40
How many questions about that trigger have you asked till now? 3? 4? Better stick to one question and improve it until it works. — Erich Kitzmueller
– Erich Kitzmueller, Commented May 27, 2015 at 17:13
Different users (here and here), or at least accounts; actually one person, or different people tackling the same assignment? — Alex Poole
– Alex Poole, Commented May 27, 2015 at 17:42

Bob Jarvis - Слава Україні · Accepted Answer · 2015-05-27 17:09:10Z

You don't need to query SALESTRANSACTION within a row trigger on SALESTRANSACTION - you should instead be using the :NEW values on the SALESTRANSACTION row for which the trigger was invoked. I suggest rewriting your trigger as:

create table excellentsales
(EXCELLENTSALES_ID  NUMBER
   CONSTRAINT PK_EXCELLENTSALES
   PRIMARY KEY
     USING INDEX,
agentid varchar2(5) not null,
agentname varchar2(25),
carmodel varchar2(25),
mfname varchar2(25))

CREATE SEQUENCE EXCELLENTSALES_SEQ
  START WITH 1
  INCREMENT BY 1;

create or replace trigger filltable
  after insert on salestransaction
  for each row
declare
  aname varchar2(25);
  modname varchar2(25);
  manfname varchar2(25);
  askpr number;
  agrpr number;
begin    
  SELECT m.NAME, mf.NAME, c.ASKINGPRICE
    INTO modname, manfname, askpr
    FROM CAR c
    INNER JOIN MODEL m
      ON m.MODELNO = c.MODELNO
    INNER JOIN MANUFACTURER mf
      ON mf.MANUFACTURERID = m.MANUFACTURERID
    WHERE c.VIN = :NEW.VIN;

  SELECT sa.NAME
    INTO aname
    FROM SALESAGENT sa
    WHERE sa.AGENTID = :NEW.AGENTID;

  agrpr := :NEW.AGREEDPRICE;

  if agrpr > askpr * 1.2 then
    insert into excellentsales (EXCELLENTSALES_ID, AGENTID, AGENTNAME, CARMODEL, MFNAME)
      values(EXCELLENTSALES_SEQ.nextval, :NEW.AGENTID, aname, modname, manfname);
  end if;
end filltable;

I also redesigned your EXCELLENTSALES table to include a primary key which is not the AGENTID. As originally designed EXCELLENTSALES did not record the actual AGENTID (which should apparently be coming from SALESTRANSACTION), which in the real world might be needed, for example, for paying commissions.

It shows error when I enter values into excellentsales table.. ORA-00984: column not allowed here

Collectives™ on Stack Overflow

PL/Sql query execution error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related