1

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?

3

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.