CODEWITHSUNDEEP
oracle-databaseplsqltriggers
user3617831
user3617831

Reputation: 28

Oracle Trigger for comparing a rows

I have the following two tables:

persons(Pname, SocStatus, Age)
donates(donor, receiver, dateofgoft)

I need to make a trigger on Donates (after insert), which compares if donor and receiver have the same age, then print about it. I wrote a trigger but something gives an error ORA00942 in declaring variables. Can you check it please?

create trigger STEP5
after insert on donates2
for each row
declare donage number(3);
recage number(3);
ndonor varchar(30);
nreceiver varchar(30);
begin
    select donor into ndonor from new.donor;
    select receiver into nreceiver from new.receiver;
    select distinct B.Age into donage
        from donates2 A
        inner join persons B on B.pname = A.donor
        where Pname = ndonor;
    select distinct C.Age into recage
        from donates2 A
        inner join persons C on C.pname = A.receiver
        where Pname = nreceiver;
    if (donage =  recage) then 
        dbms_output.put_line( ndonor|| ' and ' ||  nreceiver || ' is peers');
    end if;
end;

Upvotes: 0

Views: 250

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Try this:

create trigger STEP5
  after insert on donates2
  for each row
declare
  donage number;
  recage number;
begin
    SELECT b.AGE
      INTO DONAGE
      FROM PERSONS b
      WHERE b.PNAME = :NEW.DONOR;

    SELECT c.AGE
      INTO RECAGE
      FROM PERSONS c
      WHERE c.PNAME = :NEW.RECEIVER;

    if donage = recage then 
        dbms_output.put_line(:NEW.DONOR|| ' and ' || :NEW.RECEIVER || ' ARE peers');
    end if;
end STEPS;

Best of luck.

Share and enjoy.

Upvotes: 2

Related Questions