mysql触发器when_MySQL触发器

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER TRIGGER [trg_risks] ON dbo.projectrisk

FOR INSERT, UPDATE

AS

BEGIN

UPDATE projectrisk

SET classification =

case

when calc>= 9 then 3

when calc <9 and calc>=4 then 2

when calc <4 then 1

end

from (select inserted.id, inserted.possibility*inserted.severity as calc from inserted) as T1

where projectrisk.id = T1.id

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

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

# 创建

CREATE TRIGGER

{ BEFORE | AFTER }

{ INSERT | UPDATE | DELETE }

ON

FOR EACH ROW

# 删除

DROP TRIGGER

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

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

下边的触发器有什么问题吗？

delimiter &&

CREATE TRIGGER trg_risks_insert

AFTER INSERT ON `projectrisk`

FOR EACH ROW

UPDATE projectrisk SET classification = CASE

WHEN possibility*severity>=9 THEN 3

WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2

WHEN possibility*severity <4 THEN 1

END

WHERE id = new.id;

&&

CREATE TRIGGER trg_risks_update

AFTER UPDATE ON `projectrisk`

FOR EACH ROW

UPDATE projectrisk SET classification = CASE

WHEN possibility*severity>=9 THEN 3

WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2

WHEN possibility*severity <4 THEN 1

END

WHERE id = new.id;

&&

delimiter ;

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

delimiter &&

CREATE TRIGGER trg_risks_insert

BEFORE INSERT ON `projectrisk`

FOR EACH ROW

BEGIN

SET new.classification = CASE

WHEN new.possibility*new.severity>=9 THEN 3

WHEN new.possibility*new.severity <9 AND new.possibility*new.severity>=4 THEN 2

WHEN new.possibility*new.severity <4 THEN 1

END;

END

&&

CREATE TRIGGER trg_risks_update

BEFORE UPDATE ON `projectrisk`

FOR EACH ROW

BEGIN

SET new.classification = CASE

WHEN new.possibility*new.severity>=9 THEN 3

WHEN new.possibility*new.severity <9 AND new.possibility*new.severity>=4 THEN 2

WHEN new.possibility*new.severity <4 THEN 1

END;

END

&&

delimiter ;

分享到：

2011-09-09 16:54

浏览 5848

分类:数据库

评论