Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

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.

1 Answer
1

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 :