How to update/insert in specific column of table through trigger

Let me first explain the cause of this error:

When you have created a trigger on insert and then again you are inserting within the trigger the insert statement inside the trigger will again call the trigger and this will go on till recursive limit of 50 is reached . It is when system throws the error ORA-00036.

There are certain limitations on handling the mutating trigger i.e. you can use Autonomous Transaction which you have used but you can only read from the table not write i.e. Insert/update not allowed or other option is to use Statement level trigger and not row level trigger.

Below is the reference from O'Reilly books

Here are some guidelines to keep in mind regarding mutating table errors: • In general, a row-level trigger may not read or write the table from which it has been fired. The restriction applies only to row-level triggers, however. Statementlevel triggers are free to both read and modify the triggering table; this fact gives us a way to avoid the mutating table error. • If you make your trigger an autonomous transaction (by adding the PRAGMA AUTONOMOUS TRANSACTION statement and committing inside the body of the trigger), then you will be able to query the contents of the firing table. However, you will still not be allowed to modify the contents of the table.

Coming to the solution side:

I don't understand why you want to insert the exact same record in the same table(Just adding UPBY column). You can keep the logs by either adding a separate log table and inserting the data in that table or inserting the record at the time of original insertion rather than using trigger to perform another DML operation which is also an overhead in terms of performance.

Wrong approach. Table was mutating (as the trigger updates the same table which is being modified by the main transaction), so you tried to save yourself by declaring it an autonomous transaction, but then insert caused trigger to fire and update the table which fired the trigger again and so on, until you run out of resources.

Beside that, another HUGE mistake you did: don't ever do anything in SYS or SYSTEM schema.

ORA-04088: error during execution of trigger 'SYSTEM.MODIFIY_BYY'

Leave them alone, they own the database and - if you do something you shouldn't have, you'll destroy the database. Do your training in another schemas (such as SCOTT or HR if available, or create one of your own).

Back to your problem: you don't have to directly update the table, just use pseudorecord (:new or :old, whichever is appropriate to what you do).

For a sample table:

SQL> create table family
  2    (f_num      number,
  3     f_name     varchar2(20),
  4     f_age      number,
  5     upby       varchar2(20)
  6    );

Table created.

trigger would look like this:

SQL> create or replace trigger modify_byy
  2    before insert or update or delete on family
  3    for each row
  4  begin
  5    if inserting then
  6       :new.upby := 'insert';            --> note this
  7    elsif updating then
  8       :new.upby := 'update';            --> and this
  9    elsif deleting then
 10       dbms_output.put_line('deleted');
 11    end if;
 12  end;
 13  /

Trigger created.

Testing:

SQL> set serveroutput on;
SQL> insert into family(f_num, f_name, f_age) values (1, 'Little', 10);

1 row created.

SQL> select * from family;

     F_NUM F_NAME                    F_AGE UPBY
---------- -------------------- ---------- --------------------
         1 Little                       10 insert

SQL> update family set f_age = 20 where f_num = 1;

1 row updated.

SQL> select * from family;

     F_NUM F_NAME                    F_AGE UPBY
---------- -------------------- ---------- --------------------
         1 Little                       20 update

SQL> delete from family where f_num = 1;
deleted

1 row deleted.

SQL>

However, trigger name (as well as upby column name) suggest that trigger should actually record who did the last change. If so, it would be simplified to

SQL> create or replace trigger modify_byy
  2    before insert or update on family
  3    for each row
  4  begin
  5    :new.upby := user;
  6  end;
  7  /

Trigger created.

SQL> insert into family(f_num, f_name, f_age) values (2, 'Foot', 30);

1 row created.

SQL> select * from family;

     F_NUM F_NAME                    F_AGE UPBY
---------- -------------------- ---------- --------------------
         2 Foot                         30 SCOTT

SQL>

Handling deletes in this context doesn't make much sense; you'd have to have a separate "log" table to log deletes. And, when you do that, switch inserts and updates to it (the log table, I mean) as well. And add a timestamp column.