问题:
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 |