Update the table using triggers

Question

I am trying to update the product table whenever product_inv is updated but my code isn't running. Kindly help me with this

create table Product
(           
       P_ID varchar2(10) constraint cat_pk PRIMARY KEY,
       P_name varchar2(15) constraint Cat_unqnn UNIQUE NOT NULL,
       P_Brand varchar2(15) CHECK(P_Brand IN('Yes', 'No')),
       P_Cost varchar2(6),
       P_qty number      
);

insert into product values('10',  'Coke', 'Yes', '34', '8');
insert into product values('20', 'Pepsi', 'No', '25', '6');


create table Product_Inv
(    
       P_ID varchar2(5) REFERENCES Product(P_ID),           
       Rate varchar2(5) constraint prodcut_rate_nn NOT NULL,           
       Qty number constraint prodcut_qty_nn NOT NULL,
       Discount varchar2(15),
       Lot_ExpiryDate varchar2(10)         
);

create or replace trigger Product
AFTER insert or update or delete on Product_Inv
for each row

begin
  UPDATE Product
       SET p_qty = p_qty + new.Qty 
       WHERE p_id = new.p_id; 

end;

after this insert the item 10 would be previous 8 + this 9 , but there's some error.

 insert into Product_inv values('10', '25', '9', '25', '19-8-2014');

what you get in your product table after insert into Product_inv? — Ilesh Patel, Apr 18 '14 at 10:55
I am not sure will this create error or not but first change your trigger name because your table name and trigger name both are same. second when you pass numeric values than write them without quote. — Ilesh Patel, Apr 18 '14 at 11:02
check this http://stackoverflow.com/questions/20063953/ora-04098-is-invalid-and-failed-re-validation — Ilesh Patel, Apr 18 '14 at 11:13
unrelated, but well-intended advice: please rethink some of your datatypes — Rob van Wijk, Apr 18 '14 at 13:45
You can use `show errors` immediately after creating the trigger, or query `user_errors` later, to see what is actually wrong. But it won't have created the trigger if you already had a table with that name; perhaps you already have one from an earlier attempt with a different name? — Alex Poole, Apr 18 '14 at 15:50

score 0 · Answer 1 · answered Apr 18 '14 at 12:51

Your problem is in the trigger code: You have to use a ":" in front of new.

So instead of this part of code:

 UPDATE Product
       SET p_qty = p_qty + new.Qty 
       WHERE p_id = new.p_id;

you need to put this part:

 UPDATE Product
       SET p_qty = p_qty + :new.Qty 
       WHERE p_id = :new.p_id;

Update the table using triggers

1 Answers1