mysql触发器实例

进行数据库移植，SQL Server=>MySQL。SQL Server上有如下的Trigger

Sql代码  

1. SET QUOTED_IDENTIFIER ON   
2. GO  
3. SET ANSI_NULLS ON   
4. GO  
5. ALTER  TRIGGER [trg_risks] ON dbo.projectrisk  
6. FOR INSERT, UPDATE  
7. AS  
8. BEGIN  
9. UPDATE projectrisk  
10.    SET classification =  
11.    case      
12.     when  calc>= 9 then 3  
13.     when calc <9 and calc>=4 then 2  
14.     when calc <4 then 1  
15.    end   
16.    from (select inserted.id, inserted.possibility*inserted.severity  as calc from inserted) as T1  
17.    where projectrisk.id = T1.id  
18. END  
19. GO  
20. SET QUOTED_IDENTIFIER OFF   
21. GO  
22. SET ANSI_NULLS ON   
23. GO  

简单了解了下MySQL中，Trigger的语法。

Sql代码  

1. # 创建  
2. CREATE TRIGGER <触发器名称>  
3. { BEFORE | AFTER }  
4. { INSERT | UPDATE | DELETE }  
5. ON <表名称>  
6. FOR EACH ROW  
7. <触发器SQL语句>  
8.   
9. # 删除  
10. DROP TRIGGER <触发器名称>  

注：创建触发器需要CREATE TRIGGER权限。（HeidiSQL中执行Trigger语句会有bug）

由于MySQL中的每个触发器只能针对一个动作，所以本次移植就需要创建两个触发器。对于发生变更的行，在触发器中可以用 NEW 来代替。
下边的触发器有什么问题吗？

Sql代码  

1. delimiter &&  
2. CREATE TRIGGER trg_risks_insert  
3. AFTER INSERT ON `projectrisk`  
4. FOR EACH ROW  
5. UPDATE projectrisk SET classification = CASE  
6. WHEN possibility*severity>=9 THEN 3  
7. WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2  
8. WHEN possibility*severity <4 THEN 1  
9. END  
10. WHERE id = new.id;  
11. &&  
12. CREATE TRIGGER trg_risks_update  
13. AFTER UPDATE ON `projectrisk`  
14. FOR EACH ROW  
15. UPDATE projectrisk SET classification = CASE  
16. WHEN possibility*severity>=9 THEN 3  
17. WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2  
18. WHEN possibility*severity <4 THEN 1  
19. END  
20. WHERE id = new.id;  
21. &&  
22. delimiter ;  

问题就是，没有考虑到触发器中的修改也会触发触发器，进入了死循环。做了如下修改后，终于OK了。

Sql代码  

1. delimiter &&  
2. CREATE TRIGGER trg_risks_insert  
3. BEFORE INSERT ON `projectrisk`  
4. FOR EACH ROW  
5. BEGIN  
6.   SET new.classification = CASE  
7.   WHEN new.possibility*new.severity>=9 THEN 3  
8.   WHEN new.possibility*new.severity <9 AND new.possibility*new.severity>=4 THEN 2  
9.   WHEN new.possibility*new.severity <4 THEN 1  
10.   END;  
11. END  
12. &&  
13. CREATE TRIGGER trg_risks_update  
14. BEFORE UPDATE ON `projectrisk`  
15. FOR EACH ROW  
16. BEGIN  
17.   SET new.classification = CASE  
18.   WHEN new.possibility*new.severity>=9 THEN 3  
19.   WHEN new.possibility*new.severity <9 AND new.possibility*new.severity>=4 THEN 2  
20.   WHEN new.possibility*new.severity <4 THEN 1  
21.   END;  
22. END  
23. &&  
24. delimiter ;  

分享到：