无法同时使用SIGNAL和INSERT [英] Unable to use SIGNAL and INSERT at the same time

问题描述

此触发器有问题.当我在IF条件中使用INSERT和SIGNAL语句时，不执行插入.但是，如果没有SIGNAL，则插入完成.有人对此有解释吗?我主要担心的是，我需要插入和SIGNAL语句来取消主插入(引发触发器的插入)

I have a problem with this trigger. When I use the INSERT and the SIGNAL statements inside the IF condition, the insertion is not performed. However, without SIGNAL, the insertion is done. Anyone have an explanation for that? My main concern is that I need both the insertion and the SIGNAL statement to cancel the main insertion (the insertion that throws the trigger)

    DELIMITER //
CREATE TRIGGER log_venta BEFORE INSERT ON `venta_producto`
FOR EACH ROW BEGIN
    DECLARE value int;
    DECLARE valor_venta int;
    DECLARE saldo_cliente int;
    DECLARE cliente_id int;

    SELECT cliente INTO cliente_id FROM venta WHERE id=NEW.ventaID;
    SELECT credito INTO saldo_cliente FROM cliente WHERE no_cliente=cliente_id;
    SELECT precio INTO valor_producto FROM producto WHERE id=NEW.producto;

    SET valor_venta = NEW.cantidad*valor_producto;

    IF valor_venta > saldo_cliente THEN
      INSERT INTO log(cliente) VALUES (cliente_id);
      SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR';
    END IF; 
END
//
DELIMITER ;

谢谢

推荐答案

14.6.7.5信号语法

..

SIGNAL 是一种返回"错误的方法.

SIGNAL is the way to "return" an error.

..

21.3.1触发语法和示例

..

• 对于事务表，语句失败应引起该语句执行的所有更改的回滚.一个失败触发器导致语句失败，因此触发器失败也导致回滚.对于非事务处理表，无法进行此类回滚，因此，尽管该语句失败，但是在执行之前所做的任何更改错误点仍然有效.

..

上面提到的内容可以在以下示例中进行演示:

Mentioned above can be demonstrated in the following example:

mysql> DROP TABLE IF EXISTS `venta_producto`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `log`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `venta_producto` (
    ->   `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `cliente_id` INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `log` (
    ->   `cliente_id` INT UNSIGNED
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE TRIGGER `log_venta` BEFORE INSERT ON `venta_producto`
    -> FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO `log` (`cliente_id`) VALUES (NEW.`cliente_id`);
    ->   SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR';
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> INSERT INTO `venta_producto`
    ->   (`cliente_id`)
    -> VALUES
    ->   (1);
ERROR 1643 (02000): ERROR

mysql> SELECT
    ->   `id`,
    ->   `cliente_id`
    -> FROM
    ->   `venta_producto`;
Empty set (0.00 sec)

mysql> SELECT
    ->   `cliente_id`
    -> FROM
    ->   `log`;
Empty set (0.00 sec)

mysql> ALTER TABLE `log` ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `venta_producto`
    ->   (`cliente_id`)
    -> VALUES
    ->   (2);
ERROR 1643 (02000): ERROR

mysql> SELECT
    ->   `id`,
    ->   `cliente_id`
    -> FROM
    ->   `venta_producto`;
Empty set (0.00 sec)

mysql> SELECT
    ->   `cliente_id`
    -> FROM
    ->   `log`;
+------------+
| cliente_id |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

这篇关于无法同时使用SIGNAL和INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！