Creating a After Insert Trigger

Question

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.

Answer 1

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.