触发器怎么做呢?觉得不行delimiter $$ create procedure auto_add(in name varchar(100),in password varchar(100)) begin declare num int; insert into users values(null,name); select id into num from users order by id desc limit 1; insert into password values(num,password); end$$ delimiter
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);delimiter |CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|delimiter ;INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
$mysqli->query($updateSQL);
if($mysqli->affected_rows > 0) {
// 读取最后插入的记录号
$updateSQL = "select LAST_INSERT_ID()";
if($result = $mysqli->query($updateSQL)){
if($row = $result->fetch_array(MYSQLI_BOTH)) {
$parentid = $row[0];
// 这里在执行插入password表的sql语句
$updateSQL= "insert into password(parentid,...) values($parentid,...)";
$mysqli->query($updateSQL);
}
}
}
create procedure auto_add(in name varchar(100),in password varchar(100))
begin
declare num int;
insert into users values(null,name);
select id into num from users order by id desc limit 1;
insert into password values(num,password);
end$$
delimiter
在 after insert 中即可
user表id主键自增长 password表id主键不自增长 。
用户注册 同时把 自增长的user表id写到password表的id里怎么写呢。在 user 表的 after insert 的触发器中 (after 中 auto-increment 的值已知), insert into password表 value ....不知道 daiyan_2008(Darwin) 的问题是什么?
(new.id,md5("aaaaa"))
(new.id.md5("bbbbb"))
(new.id,md5("ccccc"))
使用触发器就不是很方便了呀?怎么把这个值传给触发器呢?
这时候
只能先把password表的id插入,其余的字段设为null,然后用update来更新password表中的内容,所以感觉触发器不是很方便,请问ACMAIN_CHM,触发器的思路是这样的吗?我是一菜鸟新人……多指教