mysql Set default value of a column as the current logged in user

Question

How do you set the default value of a column to be the logged in user?

I am creating a "logging" table, and one of the columns should be the logged in user

(the output of "SELECT user();"). 

Is this possible?

EDIT--What I tried:

create trigger logtrigger_test before insert on logging_test for each row set new.changed_by=current_user();

EDIT 2: describe table

+---------------+-------------+------+-----+-------------------+-----------------------------+
| Field         | Type        | Null | Key | Default           | Extra                       |
+---------------+-------------+------+-----+-------------------+-----------------------------+
| id            | int(11)     | NO   | PRI | NULL              | auto_increment              |
| source_table  | varchar(50) | NO   |     | NULL              |                             |
| foreign_id    | int(11)     | NO   |     | NULL              |                             | 
| field_changed | varchar(30) | NO   |     | NULL              |                             | 
| changed_by    | varchar(30) | YES  |     | NULL              |                             | 
| changed_on    | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+-------------+------+-----+-------------------+-----------------------------+

EDIT 3: SAMPLE entries in table

+----+---------------+------------+------------------+----------------------+---------------------+
| id | source_table  | foreign_id | field_changed    | changed_by           | changed_on          |
+----+---------------+------------+------------------+----------------------+---------------------+
| 0  | phone_numbers | 34         | home_phone       | jeff@localhost       | 2013-04-10 14:15:13 |
| 1  | contact_info  | 24         | first_name       | bob@localhost        | 2013-04-11 10:18:43 | 
| 2  | addresses     | 32         | home_address     | autoscript@localhost | 2013-04-12 11:10:37 |
| 3  | addresses     | 36         | business_address | bob@localhost        | 2013-04-12 14:56:17 |
| 4  | addresses     | 36         | business_address | jeff@localhost       | 2013-04-12 15:25:52 |
+----+---------------+------------+------------------+----------------------+---------------------+

Answer 1

OK, this is generally not the way one creates audit tables. Typically, when you want to log inserts, deletes, and updates, you would do something like this:

Create a table like foo:

create table foo (
    foo_id int not null auto_increment primary key,
    foo_data varchar(100) not null
    );

Then you usually make an audit table like so:

create table foo_audit (
    foo_audit_id not null auto_increment primary key,
    foo_id int,
    foo_data varchar(100),
    change_type char(1),
    change_timestamp timestamp default current_timestamp,
    change_login varchar(100)
    );

Then you make a trigger or triggers on the table like so:

create trigger trg_foo_insert
    after insert on foo
    for each row 
    insert into foo_audit (
        foo_id,
        foo_data,
        change_type,
        change_login
        )
    values (
        new.foo_id,
        new.foo_data,
        'I',
        current_user
        );

You would make a "U" trigger for updates, and a "D" trigger for deletes.

I think the main problem you are having is you are trying to do a "one size fits all" audit table; I think this pattern will cause you a lot of issues, you don't necessarily have the data you're looking for, and you will still need to write at least one trigger for each table you are auditing.

The actual answer to your question, however, is that you were either setting a trigger on a table that was not being inserted to, or trying to update a column on a table where the column did not exist.