Oracle: Trigger to store username after INSERT into column of same table

oracle-11g-r2plsqltrigger

Let's say I have a column in a table within my database named 'CREATE_USER'.
How would I go about creating a trigger that on INSERT on the table: inserts the name of the row creating user into the column 'CREATE_USER' within that same row.

Basically a column that stores the row creating users username within the same row. I've searched around and could not find anything that worked for me. I included what I've tried below.

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT ON table_name FOR EACH ROW

DECLARE v_username varchar2(20);

BEGIN

    SELECT USER INTO v_username
    FROM dual;

    INSERT INTO table_name (first_col_name, second_col_name)
    VALUES (:new.first_col_name, v_username);

END;

The first problem I immediately run into is 'ORA-00947: not enough values'…

Also, if I wanted to do this for every table in my database, would I need separate triggers for each table?

Best Answer

First of all, in the row-level trigger body you cannot perform DML against the table which fires the trigger. You will get "mutating table" error .
Secondly, you can't modify the row inside AFTER trigger body, only inside BEFORE . Assuming CREATE_USER field has proper type and size, the trigger can look like 

CREATE OR REPLACE TRIGGER BI_TABLE_NAME
BEFORE INSERT ON table_name FOR EACH ROW

BEGIN
    :new.CREATE_USER:= USER;
END;

Another option would be not to use trigger at all, but have a column with default value USER : 

create table test_table (test_table_id int not null primary key,
 ..., 
created_by varchar2(30) default user);
Related Question