加入触发器以后,INSERT就开始报错

方兆国

1. DELIMITER //
   
2. CREATE TRIGGER log_Student_Basic_Insert AFTER INSERT ON student_basic FOR EACH ROW
   
3.         BEGIN
   
4.                 UPDATE student_basic SET student_basic.Insert_Date=CURRENT_TIMESTAMP(),student_basic.Insert_HOST=SYSTEM_USER();               
   
5.                 UPDATE class SET class.sum=class.sum+1 WHERE class.id=student_basic.class_id;
   
6.                 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);
   
7.         END;//
   
8. DELIMITER ;

复制代码

请教一下,这段代码错在什么地方了,运行后，那个表里无法插入数据

下面这段儿时报错

1. PS C:\Windows\system32> mysql -u fangzhaoguo -p
   
2. Enter password: **********
   
3. Welcome to the MySQL monitor.  Commands end with ; or \g.
   
4. Your MySQL connection id is 2
   
5. Server version: 5.5.19 MySQL Community Server (GPL)
   
6. 
   
7. Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
   
8. 
   
9. Oracle is a registered trademark of Oracle Corporation and/or its
   
10. affiliates. Other names may be trademarks of their respective
    
11. owners.
    
12. 
    
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
14. 
    
15. mysql> use school;
    
16. Database changed
    
17. mysql> INSERT INTO teacher (id,name,sex,phone,collage_id) VALUES(00000000,'待定',0,0,0);
    
18. 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.
    
19. mysql>
    
20. mysql> INSERT INTO collage (id,name,main_teacher_id,sum_student,sum_teacher) VALUES(00,'未分类',00000000,0,0);
    
21. 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.
    
22. 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.

1. DELIMITER //
   
2. CREATE TRIGGER log_Student_Basic_Update AFTER UPDATE ON student_basic FOR EACH ROW
   
3.         BEGIN
   
4.                 UPDATE student_basic SET student_basic.Update_Date=CURRENT_TIMESTAMP(),student_basic.Update_Date=SYSTEM_USER();
   
5.         END;//
   
6. DELIMITER ;

复制代码

我总是感觉这段儿代码会出现死循环,因为触发条件是UPDATE,触发后的操作也是UPDATE，怎么修改呢？

devilkin0312

UPDATE student_basic SET student_basic.Insert_Date=CURRENT_TIMESTAMP(),student_basic.Insert_HOST=SYSTEM_USER();
改全表？

方兆国

回复 2# devilkin0312


    忘了WHERE语句,加上WHERE语句还是错的.目前唯一的解决方案就是把修改时间放在单独的表中

devilkin0312

为何不在insert的时候插入时间？回复 3# 方兆国


   

方兆国

回复 4# devilkin0312


    为了防止插入时,客户端系统时间错误(假设客户机和服务器时间存在不一致),导致插入的时间错误

devilkin0312

插入取server时间不就ok了
回复 5# 方兆国


   

方兆国

回复 6# devilkin0312


    插入时取服务器时间?我在想插入时程序已经把编译好的SQL语句传上去了,除了用触发器,我想不到其他办法啦,我刚开始学数据库的,没什么经验