CREATE TRIGGER trigger_price before UPDATE ON channel for each row BEGIN declare msg varchar(200); declare msh varchar(200); IF (NEW.price < 0 OR NEW.price > 100) THEN SET msg=CONCAT('不行'，NEW.price,'wo'); else SET msh= CONCAT('ok'); SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg; SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msh; end if; END;错在哪

这个 TRIGGER 的问题在于在 IF 语句块外面定义了两个变量 msg 和 msh，但是在 IF 语句块内部没有使用它们。而在 IF 语句块内部定义的 msg 变量并没有被初始化，仅仅是定义了它的类型，这会导致在触发器中使用 SIGNAL 语句时出现错误。

下面是修改后的代码：

CREATE TRIGGER trigger_price BEFORE UPDATE ON channel 
FOR EACH ROW 
BEGIN     
    DECLARE msg VARCHAR(200); 		
    IF (NEW.price < 0 OR NEW.price > 100) THEN     
        SET msg = CONCAT('价格不能小于0或大于100，当前价格为', NEW.price); 		
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;    
    END IF; 	
END;

修改后的代码中，只定义了一个 msg 变量，并且在 IF 语句块内部对它进行了初始化和赋值。如果触发器中的条件不满足，则会抛出一个信号，其中包含了错误信息。同时，将 SQLSTATE 设置为 '45000'，表示触发器抛出的是一个用户定义的错误。

相关问题

触发器 在学生表中删除学生的同时删除选课表的信息 CREATE TRIGGER students_delete_before BEFORM DELETE ON students FOR EACH ROW BEGIN DELETE FROM sc WHERE Sno=old.Sno; END; 更新选课表信息 CREATE TRIGGER students_update_after AFTER UPDATE ON students FOR EACH ROW BEGIN UPDATE sc SET Sno=new.Sno WHERE Sno=old.Sno; END; 学分录入（成绩大于60） CREATE TRIGGER sc_install_after AFTER UPDATE ON sc FOR EACH ROW BEGIN DECLARE xf SAMLLINT; SELECT Ccredit INTO xf FORM course WHERE Cno=new.Cno; IF new.Grade>=60 THEN UPDATE students SET Scredit+xf WHERE Sno=new.Sno; END IF; END;这段代码找出错误并改正

在第三个触发器中，声明变量的语句中 "SAMLLINT" 应该为 "SMALLINT"。同时，在更新语句后面应该加上 SET 关键字，将修改后的学分值赋值给 Scredit 字段。修改后的代码如下：

CREATE TRIGGER sc_install_after AFTER UPDATE ON sc FOR EACH ROW 
BEGIN 
    DECLARE xf SMALLINT; 
    SELECT Ccredit INTO xf FROM course WHERE Cno=new.Cno; 
    IF new.Grade >= 60 THEN 
        UPDATE students SET Scredit = Scredit + xf WHERE Sno = new.Sno; 
    END IF; 
END;

create trigger _before_insert_orders before insert on orders for each row begin declare orders goods id int; declare goods_inventory_number decimal(10,2); declare orders_goods_name varchar(50); declare msg varchar (100); if not exists (select goods name from goods where goods id=new.goods id) then setmsg-’你订购的商品不存在！请重新下订单！！ signal sqlstate '45000' set message_text=msg; end if; select inventory_number,goods_name into goods_inventory_number,orders_goods_name from goods where goods_id=new.goods_id; if goods inventory number<new.number then setmsg=concat(orders_goods_name’库存不足，请修改订购商品的数量！”; signal sqlstate '45000' set message text=msg; else update goods set inventory number=inventory number-new.number where goods id=new.goods id; end if；end 检查这段代码哪里有错

这段代码有以下几个错误：

1. `declare orders goods id int;` 这行代码有语法错误，应该是 `declare orders_goods_id int;`。

2. `setmsg-’你订购的商品不存在！请重新下订单！！ signal sqlstate '45000' set message_text=msg;` 这行代码也有语法错误，`setmsg` 应该改为 `set msg`。

3. `select inventory_number,goods_name into goods_inventory_number,orders_goods_name from goods where goods_id=new.goods_id;` 这行代码应该是 `select inventory_number,goods_name into goods_inventory_number,orders_goods_name from goods where goods_id=new.goods_id;`。

4. `setmsg=concat(orders_goods_name’库存不足，请修改订购商品的数量！”; signal sqlstate '45000' set message text=msg;` 这行代码有语法错误，应该将 `orders_goods_name` 改为 `orders_goods_name + '`，并且将 `message text` 改为 `message_text`。

修改后的代码如下：

create trigger _before_insert_orders before insert on orders for each row
begin
    declare orders_goods_id int;
    declare goods_inventory_number decimal(10,2);
    declare orders_goods_name varchar(50);
    declare msg varchar (100);
    
    if not exists (select goods_name from goods where goods_id=new.goods_id) then
        set msg='你订购的商品不存在！请重新下订单！！';
        signal sqlstate '45000' set message_text=msg;
    end if;
    
    select inventory_number,goods_name into goods_inventory_number,orders_goods_name from goods where goods_id=new.goods_id;
    
    if goods_inventory_number<new.number then
        set msg=concat(orders_goods_name, '库存不足，请修改订购商品的数量！');
        signal sqlstate '45000' set message_text=msg;
    else
        update goods set inventory_number=inventory_number-new.number where goods_id=new.goods_id;
    end if;
end;