MySQL: default value of column from another table

Question

Consider you have table USER which has primary key on field USER_ID. And every table in your db should contain MODIFIED_BY field with id of actual user. My aim is to specify default value on that field which equals id of user with appropriate name. F.E. you have user with name system and you want every record of every table to have id of this user in MODIFIED_BY field. As well you want all the new records to satisfy this.

Answer 1

Assuming that you are referring to other sorts of users than database users (e.g. 'registered users'), maybe triggers are the solution to your problem. With two triggers, one fired when inserting and one when updating, you can specify the value of a modified_at-column using custom code, e.g. select user_id from ... where ...

Please check the following sample code, which should directly answer your question; hope it solves your problem. Otherwise, please give us more information / some more context.

DELIMITER |

drop table if exists sometable
|
CREATE TABLE `sometable` (
  `somecolumn` int(11) DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL
)
|
drop table if exists registered_user
|
CREATE TABLE registered_user (
  user_id integer primary key,
  name varchar(50)
)
|
drop trigger if exists sometable_inserted
|
drop trigger if exists sometable_modified
|
CREATE TRIGGER sometable_inserted BEFORE INSERT ON sometable
FOR EACH ROW
BEGIN
SET new.modified_by := (select user_id from registered_user where name=@name);
END;
|
CREATE TRIGGER sometable_modified BEFORE UPDATE ON sometable
FOR EACH ROW
BEGIN
SET new.modified_by := (select user_id from registered_user where name=@name);
END;
|

DELIMITER ;

insert into registered_user values (5, 'joe');
insert into registered_user values (6, 'sam');

set @name='sam';
insert into sometable(somecolumn) values (1);
insert into sometable(somecolumn) values (2);
set @name='joe';
update sometable set somecolumn = 3 where somecolumn = 2;