How do I set trigger to copy user_id to another table

问题: I need a trigger in phpmyadmin for 2 tables. I have two tables called users and group_has_users. Table group_has_users has 2 values called group_id and user_id. I'm try...

问题:

I need a trigger in phpmyadmin for 2 tables.

I have two tables called users and group_has_users. Table group_has_users has 2 values called group_id and user_id.

I'm trying to make a trigger that automatically inserts new data to group_has_users table with user_id when new user is created in users table( with group_id set to 1)

How to do this with phpmyadmin trigger?

insert into group_has_users (user_id) values (new.user_id);

I have no clue to make it group_id to set 1.


回答1:

I think this is what you want:

delimiter $$

create trigger after_insert_users
after insert on users 
for each row begin

if new.group_id = 1 then

insert into group_new_users (user_id) values (new.user_id);

end if;

end $$
delimiter ;

Note that you will have to run this in your cli or in mysql workbench


回答2:

Everytime a new record is created in table users, you are looking to automatically create a new record in group_has_users, with a default group_id of 1.

Consider the following trigger:

DELIMITER //
CREATE TRIGGER users_after_insert
AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO group_has_users(group_id, user_id) VALUES(1, NEW.user_id);
END;
//

Demo on DB Fiddle:

DELIMITER //

CREATE TABLE users (user_id int)//   
CREATE TABLE group_has_users (group_id int, user_id int)//

CREATE TRIGGER users_after_insert
AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO group_has_users(group_id, user_id) VALUES(1, NEW.user_id);
END;
//

INSERT INTO users(user_id) values(1);

SELECT * FROM group_has_users;

| group_id | user_id |
| -------- | ------- |
| 1        | 1       |
  • 发表于 2019-03-20 14:55
  • 阅读 ( 179 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除