source mysql 默认值,mysql设置列的默认值作为当前登录用户

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 |

+----+---------------+------------+------------------+----------------------+---------------------+

解决方案

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.