如何在插入之前创建MySQL触发器？

为此，语法如下 –

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触发器？的详细内容，更多请关注恰卡编程网(mip.qiaqa.com)其它相关文章！