CODEWITHSUNDEEP
sqldatabaseoracle-databasedatabase-trigger
Claudio Scabbia Sepúlveda
Claudio Scabbia Sepúlveda

Reputation: 97

saving a value from another table in a oracle trigger

I'm writting a trigger and I'm crushing with a wall, 'cause I need to save a value from another table in a variable, and then substract that value to a column of another table, I'm working with a trigger but it doesn't work :(

This is my code:

create or replace trigger tg_update_total_new_product
after insert on detalle_comanda 
for each row
declare
    var_precio numeric;
begin
    var_precio := (select precio from producto where id=:new.producto_id);
     update comanda set precuenta=precuenta+var_precio where id=:new.comanda_id;
END;

The error code is the next:

Trigger TG_UPDATE_TOTAL_NEW_PRODUCT compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
4/20      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat
4/73      PLS-00103: Encountered the symbol ")" when expecting one of the following:     . ( * @ % & - + ; / at for mod remainder rem    <an exponent (**)> and or group having intersect minus order    start union where connect || indicator multiset 
6/4       PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     end not pragma final instantiable persistable order    overriding static member constructor map 
Errors: check compiler log

That table is a middle-one, then I have the total of the sale in another table, and I want that, when someone add a product, the total is added... I've been thinking in a function, or a view, but I don't know why this isn't working... Please help! Thanks!

Upvotes: 1

Views: 315

Answers (3)

Popeye
Popeye

Reputation: 35920

I would suggest not using any variable as you will need to handle no data found, more than one rows fetched exception. You can directly use the SELECT statement inside the UPDATE statement as follows:

create or replace trigger tg_update_total_new_product
after insert on detalle_comanda 
for each row
begin
     update command 
        set precuenta=precuenta 
                      + COALESCE((select precio from producto where id=:new.producto_id),0) 
      where id=:new.comanda_id;
END;
/

Upvotes: 1

switch
switch

Reputation: 116

This is where you have made a mistake - var_precio := (select precio from producto where id=:new.producto_id);

You have to use select [column] into [variable]

Therefore, your correct syntax should be - 

select precio into var_precio from producto where id=:new.producto_id;

Upvotes: 0

pifor
pifor

Reputation: 7882

You should use SELECT ... INTO ...:

select precio into var_precio from producto where id=:new.producto_id;

Upvotes: 1

Related Questions