Creating a After Insert Trigger

oracle-11gplsqltrigger

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.

Best Answer

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.

Related Solutions

MySQL BEFORE INSERT trigger with IF conditions

As @dezso says, you can't compare NULL with arithmetic comparison operators as described in the MySQL's documentation.

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Edit this part:

IF NumberHP IS null then # <-- IS replaced =
    insert into smsra_sms
    set idsms = IDx, SendingDateTime = WaktuKirim,
    isi = IsiSMS, pengirim = NumberHP
    ON duplicate key update idsms = idsms + IDx;
ELSE
    insert into smsra_trash
    set Nohp = NumberHP
    ON duplicate key update Nohp = Nohp + NumberHP;
END IF;

Sql-server – Instead of insert trigger VS For insert Triggers in SQL server

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

The INSTEAD OF trigger replaces the original insert. So the code:

Insert into dbo.TestTable (Name,Age,DOB,Address) 
Select Name,Age,DOB,Address From inserted;

...is executed instead of the original insert:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

The actions in the instead of trigger are completed before constraints are checked, and before any AFTER triggers fire.

When the instead of trigger completes, a row has been added to the TestTable and CopyTable tables. Note though that the transaction is still active at this point, so the changes are not yet permanent.

Next, the after trigger fires. It sees the name 'Waleed' in the inserted table, prints a message, but does not rollback the changes made so far in the transaction (including the rows added by the instead of trigger). No row is added to the AuditTable.

If the condition that checked for the name 'Waleed' also performed a rollback, everything would be rolled back, and no rows would be added to any table.

Best Answer

Related Solutions

MySQL BEFORE INSERT trigger with IF conditions

Sql-server – Instead of insert trigger VS For insert Triggers in SQL server

Related Question