为此,语法如下 -
delimiter // create trigger yourTriggerName before insert on yourTableName FOR EACH ROW BEGIN yourStatement1 . . N END // delimiter ;
为了理解上述语法,让我们创建一个表 -
mysql> create table DemoTable1919 ( Value int ); Query OK, 0 rows affected (0.00 sec)
这是在插入之前创建 MySQL 触发器的查询 -
mysql> delimiter // mysql> create trigger sumis60 before insert on DemoTable1919 FOR EACH ROW BEGIN declare custom_message varchar(256); IF (SELECT sum(Value) from DemoTable1919) = 60 THEN set custom_message= "cannot insert because sum(Value) is greater than 60"; signal sqlstate '13500' set message_text= custom_message; END IF; END // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;
现在您可以使用插入命令在表中插入一些记录并检查触发器的工作 -
mysql> insert into DemoTable1919 values(10); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1919 values(20); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1919 values(30); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1919 values(40); ERROR 1644 (13500): cannot insert because sum(Value) is greater than 60
以上就是如何在插入之前创建MySQL触发器?的详细内容,更多请关注php中文网其它相关文章!
已抢66663个
抢已抢38047个
抢已抢21026个
抢已抢2644个
抢已抢418个
抢已抢19167个
抢