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.
考虑你有表USER,它在字段USER_ID上有主键。数据库中的每个表都应包含MODIFIED_BY字段,其id为实际用户。我的目标是在该字段上指定默认值,该默认值等于具有适当名称的用户的id。 F.E.您拥有名称系统的用户,并且您希望每个表的每个记录在MODIFIED_BY字段中具有此用户的ID。同样,您希望所有新记录都满足此要求。
1 个解决方案
#1
2
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 ...
假设您指的是其他类型的用户而不是数据库用户(例如“注册用户”),可能触发器是您问题的解决方案。使用两个触发器,一个在插入时触发,一个在更新时触发,您可以使用自定义代码指定modified_at-column的值,例如:从...中选择user_id ...
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;
#1
2
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 ...
假设您指的是其他类型的用户而不是数据库用户(例如“注册用户”),可能触发器是您问题的解决方案。使用两个触发器,一个在插入时触发,一个在更新时触发,您可以使用自定义代码指定modified_at-column的值,例如:从...中选择user_id ...
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;