PL/SQL Trigger Variable Problems

Question

I am relatively new to PL/SQL and i am trying to create a trigger that will alert me after an UPDATE on a table Review. When it is updated I want to ge the username(User table), score(Review Table), and product name (Product Table) and print them out:

This is what I have so far:

three tables:

Review: score, userid,pid, rid
Users: userid,uname
Product: pid,pname

So Review can reference the other tables with forigen keys.

    create or replace trigger userNameTrigger


    after insert on review
    for each row

    declare


    x varchar(256);
    y varchar(256);
    z varchar(256);


    begin


    select uname into x , pname into y  , score into z
from review r , product p , users u
where r.pid = p.pid and r.userid = u.userid and r.rid =new.rid;




    dbms_output.put_line('user: '|| X||'entered a new review for Product: '|| Y || 'with a review score of: '|| Z);


    end;

The problem I am having is I cannot seem to figure out how to store the selected fields into the variables and output it correctly.

DDL:

    Create Table Review
    (
        score    varchar2(100)
      , userid   varchar2(100)   
      , pid      varchar2(100) 
      , rid      varchar2(100) 
    );

Create Table Users
(
    userid   varchar2(100)
  , uname    varchar2(100)
);

Create Table Product
(
    pid      varchar2(100)
  , pname    varchar2(100)
);

Answer 1

It makes really no sense to use a trigger to notify themselves about changed rows. If you insert new rows into the table, then you have all info about them. Why not something like the block below instead a trigger:

create table reviews as select 0 as rid, 0 as userid, 0 as score, 0 as pid from dual where 1=0;
create table users as select 101 as userid, cast('nobody' as varchar2(100)) as uname from dual;
create table products as select 1001 as pid, cast('prod 1001' as varchar2(100)) as pname from dual;

<<my>>declare newreview reviews%rowtype; uname users.uname%type; pname products.pname%type; begin 
    insert into reviews values(1,101,10,1001) returning rid,userid,score,pid into newreview;
    select uname, pname into my.uname, my.pname
    from users u natural join products p
    where  u.userid = newreview.userid and p.pid = newreview.pid
    ;
    dbms_output.put_line('user: '||my.uname||' entered a new review for Product: '||my.pname||' with a review score of: '||newreview.score);
end;
/

output: user: nobody entered a new review for Product: prod 1001 with a review score of: 10

In order to inform another session about an event you should use dbms_alert (transactional) or dbms_pipe (non transactional) packages. An example of dbms_alert:

create or replace trigger new_review_trig after insert on reviews for each row
begin
    dbms_alert.signal('new_review_alert', 'signal on last rid='||:new.rid); 
end;
/

Run the following block in another session (new window, worksheet, sqlplus or whatever else). It will be blocked until the registered signal is arrived:

<<observer>>declare message varchar2(400); status integer; uname users.uname%type; pname products.pname%type; score reviews.score%type;
begin
    dbms_alert.register('new_review_alert');
    dbms_alert.waitone('new_review_alert', observer.message, observer.status); 
    if status != 0 then raise_application_error(-20001, 'observer: wait on new_review_alert error'); end if;
    select uname, pname, score into observer.uname, observer.pname, observer.score
    from reviews join users using(userid) join products using (pid)
    where rid = regexp_substr(observer.message, '\w+\s?rid=(\d+)', 1,1,null,1) 
    ;
    dbms_output.put_line('observer: new_review_alert for user='||observer.uname||',product='||observer.pname||': score='||observer.score);
end;
/

Now in your session:

insert into reviews values(2, 101,7,1001);
commit; --no alerting before commit 

The another (observer) session will be finished with the output:
observer: new_review_alert for user=nobody,product=prod 1001: score=7

Answer 2

The first problem I can see is that you're missing a colon when you refer to new.rid. The second is that you're accessing the review table inside a row-level trigger on that same table, which will give you a mutating table error at some point; but you don't need to as all the data from the inserted row is in the new pseudorow.

create or replace trigger userNameTrigger
after insert on review
for each row
declare
  l_uname users.uname%type;
  l_pname product.pname%type;
begin
  select u.uname into l_uname
  from users u
  where u.userid = :new.userid;

  select p.pname
  into l_pname
  from product
  where p.pid = :new.pid;

  dbms_output.put_line('user '|| l_uname
    || ' entered a new review for product ' || l_pname
    || ' with a review score of '|| :new.score);
end;

The bigger problem is that the only person who could see the message is the user inserting tow row, which seems a bit pointless; and they would have to have output enabled in their session to see it.

If you're trying to log that so someone else can see it then store it in a table or write it to a file. As the review table can be queried anyway it seems a bit redundant though.

Having all your table columns as strings is also not good - don't store numeric values (e.g. scores, and probably the ID fields) or dates as strings, use the correct data types. It will save you a lot of pain later. You also don't seem to have any referential integrity (primary/foreign key) constraints - so you can review a product that doesn't exist, for instance, which will cause a no-data-found exception in the trigger.

Answer 3

P.S. There was no RID in the Table REVIEW, so i'll just assume it was supposed to be PID.

create or replace trigger userNameTrigger
after insert on review
for each row
declare

    x varchar2(256);
    y varchar2(256);
    z varchar2(256);

BEGIN

    select  uname 
          , pname
          , score
    INTO    x
          , y
          , z
    from   review r 
        ,  product p 
        ,  users u
    where  r.pid = p.pid 
    and    r.userid = u.userid 
    and    r.PID = :new.pid;
    dbms_output.put_line('user: '|| X ||'entered a new review for Product: '|| Y || 'with a review score of: '|| Z);

end userNameTrigger;     

You just made a mistake on the INTO statement, you can just clump them together in one INTO.