CREATE TRIGGER log_Student_Basic_Insert AFTER INSERT ON student_basic FOR EACH ROW
BEGIN
UPDATE student_basic SET student_basic.Insert_Date=CURRENT_TIMESTAMP(),student_basic.Insert_HOST=SYSTEM_USER();
UPDATE class SET class.sum=class.sum+1 WHERE class.id=student_basic.class_id;
UPDATE collage SET collage.sum_student=collage.sum_student+1 WHERE collage.id=(SELECT class.collage_id FROM class WHERE class.id=student_basic.class_id);
END;//
DELIMITER ;
复制代码
请教一下,这段代码错在什么地方了,运行后，那个表里无法插入数据
下面这段儿时报错
PS C:\Windows\system32> mysql -u fangzhaoguo -p
Enter password: **********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use school;
Database changed
mysql> INSERT INTO teacher (id,name,sex,phone,collage_id) VALUES(00000000,'待定',0,0,0);
ERROR 1442 (HY000): Can't update table 'teacher' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
mysql> INSERT INTO collage (id,name,main_teacher_id,sum_student,sum_teacher) VALUES(00,'未分类',00000000,0,0);
ERROR 1442 (HY000): Can't update table 'collage' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
复制代码
总是告诉我这个
ERROR 1442 (HY000): Can't update table 'collage' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
DELIMITER //
CREATE TRIGGER log_Student_Basic_Update AFTER UPDATE ON student_basic FOR EACH ROW
BEGIN
UPDATE student_basic SET student_basic.Update_Date=CURRENT_TIMESTAMP(),student_basic.Update_Date=SYSTEM_USER();