两张表:
tab_1: userid int(20)
username varch(5)
pwd varchar(10)tab_2:userid int(20)
age int(1)我想在两个表之间建立一个级联插入,父表是tab_1, 子表是tab_2;
我的代码是这样写:delimiter &&
create trigger user_logout_trig1 after insert
on tab_1 for each row
begin
declare userID int(20);
select login.userid into userID;
insert into usersinfo(userID) values(:userID);
end;
&&
delimiter;有错呀~请哪位大侠高手帮帮忙呀~~给个正确的代码吧在线等啊
tab_1: userid int(20)
username varch(5)
pwd varchar(10)tab_2:userid int(20)
age int(1)我想在两个表之间建立一个级联插入,父表是tab_1, 子表是tab_2;
我的代码是这样写:delimiter &&
create trigger user_logout_trig1 after insert
on tab_1 for each row
begin
declare userID int(20);
select login.userid into userID;
insert into usersinfo(userID) values(:userID);
end;
&&
delimiter;有错呀~请哪位大侠高手帮帮忙呀~~给个正确的代码吧在线等啊
解决方案 »
- mysql 数据库被 drop database 数库库名,能恢复吗?
- enum类型占用几个字节?
- prompt中如何显示日期?
- 存储过程不存在?!(PROCEDURE does not exist)
- mysql里如何递归查询树装状记录,求助。
- mysql有什么方法可以自动记录死锁情况
- 触发器问题,上次提问已石沉大海
- mysql创建中文表时总是出错
- commit ,unlock table慢
- mysql主从复制:Slave_IO_Running、Slave_SQL_Running都为Yes,但是不能同步,Last_IO_Error:2003和Last
- mysql数据库创建表错误!!!急!!!
- 导出数据格式出错
or
insert into usersinfo(userID) values(userID);
提示什么
完整代码:delimiter &&
create trigger user_logout_trig1 after insert
on login for each row
begin
declare userID int(20);
select login.userid into userID;
insert into usersinfo(userID) values(:userID);
end;
&&
delimiter;我的问题中只是用tab_1代替login表,login是父表,usersinfo是子表。
完整代码:delimiter &&
create trigger user_logout_trig1 after insert
on login for each row
begin
declare userID int(20);
select login.userid into userID;
insert into usersinfo(userID) values(:userID);
end;
&&
delimiter;我的问题中只是用tab_1代替login表,login是父表,usersinfo是子表。
delimiter &&
create trigger user_logout_trig1 after insert
on `login` for each row
begin
select `login`.`userid` into @userID;
insert into usersinfo(`userID`) values(@userID);
end;
&&
delimiter;
注意字段名与变量名重名
delimiter &&
create trigger user_logout_trig1 after insert
on login for each row
begin
declare idnum int(20);
select userid.login into @idnum;
insert into usersinfo(userID) values(@idnum);
end;
&&
delimiter;上面的触发器是执行成功了,但是当执行下面的插入语句时
insert into login values
(103,'[email protected]','haozai');报错:Unknown table 'userid' in field list。
create trigger user_logout_trig1 after insert
on login for each row
begin
declare idnum int(20);
select `userid`.`login` into @idnum;
insert into `usersinfo`(`userID`) values(@idnum);
end;
&&
delimiter;
注意保留字
login表:create table Login
(
userID int(20) not null auto_increment,
e_mail VARCHAR(40),
pwd VARCHAR(20),
primary key (userID)
);
userinfo表
create table UsersInfo
(
userID int(20) not null auto_increment,
cute_name varchar(16),
sex char(1),
primary key (userID)
);
插数据:
insert into login values
(100,'[email protected]','huazai');insert into usersinfo (userid,cute_name,sex) values
(100,'蔡大骅','男');
外键:alter table usersinfo
add constraint userid_login_pk foreign key(userID)
references login(userID) ON DELETE CASCADE ON UPDATE CASCADE;
后边我就建触发器查数据了,然后就错了
DROP TRIGGER IF EXISTS user_logout_trig1$$
CREATE TRIGGER user_logout_trig1 AFTER INSERT
ON login FOR EACH ROW
BEGIN
DECLARE idnum INT(20);
SELECT `userid` INTO @idnum FROM `login`;
INSERT INTO `usersinfo`(`userID`) VALUES(@idnum);
END;$$
DELIMITER ;INSERT INTO `usersinfo`(`userID`) VALUES(new.`userID`);
出错 ~Result consisted of more than one row
INSERT INTO `usersinfo`(`userID`) VALUES(new.`userID`);
就行了
DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `user_logout_trig1`$$CREATE TRIGGER `user_logout_trig1` AFTER INSERT ON `login`
FOR EACH ROW BEGIN
DECLARE idnum INT(20);
INSERT INTO `usersinfo`(`userID`) VALUES(new.userid);
END;
$$DELIMITER ;INSERT INTO login VALUES
(101,'[email protected]','huazai');
测试通过
这样就对了但是为什么不用select 就可以了呢~ 用select就不成功呢?
select into 只能有唯一满足的条件的记录才行