Python241820 Python241820 - 6 months ago 11
SQL Question

How to select a column inserted using trigger?

I created the following tables:

create table people (
ID varchar2(9),
name varchar2(20),
CONSTRAINT pk_ID PRIMARY KEY (ID)
);

create table cars (
license_plate varchar2(9),
ID varchar2(9),
CONSTRAINT pk_license_plate PRIMARY KEY (license_plate)
);

create table accidents (
code varchar2(9),
license_plate varchar2(9),
CONSTRAINT pk_code PRIMARY KEY (code)
);


I inserted the following data:

insert into people(ID, name) values('0x1','Louis');
insert into people(ID, name) values('0x2','Alice');
insert into people(ID, name) values('0x3','Peter');

insert into cars(license_plate, ID) values('001','0x1');
insert into cars(license_plate, ID) values('002','0x2');
insert into cars(license_plate, ID) values('003','0x1');
insert into cars(license_plate, ID) values('004','0x3');

insert into accidents(code, license_plate) values('fd1','001');
insert into accidents(code, license_plate) values('fd2','004');
insert into accidents(code, license_plate) values('fd3','002');


The question is: How to make a trigger that after adding a new car , Oracle displays a message type : " The X client has bought the car whose license plate is Y". ?

For example, i inserted the following data:

insert into cars(license_plate, ID) values('005','0x1');


Oracle:


"The client "Louis" has bought the car whose license plate is 005".


I have tried the following trigger:

create or replace trigger
after insert on cars
for each row
declare
cursor c_trigger is
select p.name, c.license_plate
from people p, cars c
where p.ID=c.ID
and c.license_plate=:new.licenseplate;
v_trigger c_trigger%rowtype;
begin
open c_trigger;
dbms_output.put_line('The client' || v_trigger.name || 'has bought the car whose license plate is' || v_trigger.license_plate);
fetch c_trigger into v_trigger;
close c_trigger;
end trigger;
/

Answer

Your trigger doesn't have a name; you're producing your output after you open the cursor but before you fetch so there is no current row; and your cursor is trying to query the table the trigger is against, which will cause a mutating-table error. You also need some whitespace in your output.

You don't need to look at the cars table as you have all the data you need in the new pseudorecord.

create or replace trigger cars_ai_trig
after insert on cars
for each row
declare
  cursor c_trigger is
    select p.name 
    from people p
    where p.ID = :new.ID;
  v_trigger c_trigger%rowtype;
begin
  open c_trigger;
  fetch c_trigger into v_trigger;
  dbms_output.put_line('The client ' || v_trigger.name || ' has bought the car whose license plate is ' || :new.license_plate);
  close c_trigger;
end trigger;
/

Trigger CARS_AI_TRIG compiled

set serveroutput on
insert into cars(license_plate, ID) values('005','0x1');

1 row inserted.
The client Louis has bought the car whose license plate is 005

You don't really need a cursor here, you could have a simple scalar variable and select directly in to that:

create or replace trigger cars_ai_trig
after insert on cars
for each row
declare
  v_name people.name%type;
begin
  select p.name
  into v_name
  from people p
  where p.id = :new.id;

  dbms_output.put_line('The client ' || v_name || ' has bought the car whose license plate is ' || :new.license_plate);
end trigger;
/

I'd also consider changing the column name 'ID' to something more descriptive; in the cars table it isn't obvious that it's a person ID. You should also probably have a foreign key constraint on that, and on accidents.license_plate. Without that FK relationship you can insert an ID into cars which doesn't exist in people; and in that case the trigger will get a no-data-found exception.