0

I created a trigger in table tbl_user_order.

and I want to insert data in retailer_deliver_customer table. CODE:-

create or replace trigger insert_record
after insert on tbl_user_order
for each row
BEGIN
insert into retailer_deliver_customer
(
prod_name,product_quantity,message,delivery_date,delivery_address,pin_code 
)
select
  :new.prod_name,
  :new.prod_quantity,
  :new.meessage,
  :new.delivery_date,
  :new.delivery_address,
  :new.pin_id
 from tbl_user_order where pin_id=:new.pin_id;
commit;
end insert_record;
/

I got mutating error once.

If I pass any value in :new.pin_id then it inserted and if we declare variable for it then get error.

  • can we insert data in retailer_deliver_customer(retailer_name,retailer_mno) from retailer_info table( retailer_name, retailer_MNO ).pin_id common field in both table.using trigger. upper suggestion is correct and this one addtional as new requirement – surendra Sep 14 '17 at 7:25
2

You don't need the select:

create or replace trigger insert_record
  after insert on tbl_user_order
  for each row
BEGIN
  insert into retailer_deliver_customer
    (prod_name,product_quantity,message,delivery_date,delivery_address,pin_code)
  values(
    :new.prod_name,
    :new.prod_quantity,
    :new.meessage,
    :new.delivery_date,
    :new.delivery_address,
    :new.pin_id);

end insert_record;
/

You can't commit in a trigger. The actions inside a trigger are always part of the transaction that fired the trigger.

  • it works for me. – surendra Sep 13 '17 at 10:59
  • @surendra: if that solved your problem, accept the answer, so that your question is marked as resolved – a_horse_with_no_name Sep 13 '17 at 12:55
  • Side note. You can issue commit inside trigger body if it's an autonomous trigger (created with PRAGMA AUTONOMOUS_TRANSACTION; ). – a1ex07 Sep 13 '17 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.