我使用MySQL6.0Workbench来创建触发器。
我有一张储存车辆维修费用的桌子。我想创建一个触发器,在更新和插入列时,它将对所有值进行汇总,并使用计算的和填充已经存在的总计列。我希望这些值存储在表中,而不是运行查询。
表= Inventory_maintenance (oil_change,制动器,皮带,其他,总计)所有的值都是双倍的。我的表目前是空的,等待数据。
这些是我迄今尝试过的触发因素:
USE `car_db`;
DELIMITER $$
CREATE TRIGGER `inventory_maintenance_AINS`
AFTER INSERT ON `inventory_maintenance`
FOR EACH ROW
BEGIN
SET inventory_maintenance.TOTAL =
inventory_maintenance.OIL_CHANGE
+ inventory_maintenance.BELTS
+ inventory_maintenance.BRAKES
+ inventory_maintenance.OTHER ;
END#错误:
ERROR 1193: Unknown system variable 'TOTAL'
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'inventory_maintenance' already exists**还有..。
USE `car_db`;
DELIMITER $$
CREATE TRIGGER `inventory_maintenance_AINS`
AFTER INSERT ON `inventory_maintenance`
FOR EACH ROW
BEGIN
SET NEW.TOTAL = NEW.OIL_CHANGE + NEW.BELTS + NEW.BRAKES + NEW.OTHER;
END#错误:
ERROR 1362: Updating of NEW row is not allowed in after trigger
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'inventory_maintenance' already exists**我哪里出问题了?原谅我,但我对触发还不熟悉。
发布于 2014-01-04 19:37:15
只有在完成DML操作后,AFTER触发器才会触发。NEW的范围将一直到DML操作完成为止。
请参阅:http://forums.mysql.com/read.php?99,84452,84495#msg-84495
如果确实希望在新行中设置列的值,请使用BEFORE触发器。
下面的工作应该是有效的。
DELIMITER $$
CREATE TRIGGER `inventory_maintenance_BINS`
BEFORE INSERT ON `inventory_maintenance`
FOR EACH ROW
BEGIN
SET NEW.TOTAL = NEW.OIL_CHANGE + NEW.BELTS + NEW.BRAKES + NEW.OTHER;
END#
$$
DELIMITER ;发布于 2015-06-07 20:32:51
DELIMITER $$
CREATE TRIGGER `inventory_maintenance_BINS`
BEFORE INSERT ON `inventory_maintenance`
FOR EACH ROW
BEGIN
SET NEW.TOTAL = NEW.OIL_CHANGE + NEW.BELTS + NEW.BRAKES + NEW.OTHER;
END#
$$
DELIMITER ;或者你可以试试这个:
DELIMITER $$
CREATE TRIGGER `inventory_maintenance_BINS`
AFTER INSERT ON `inventory_maintenance`
FOR EACH ROW
BEGIN
SET inventory_maintenance.TOTAL = inventory_maintenance.OIL_CHANGE + inventory_maintenance.BELTS + inventory_maintenance.BRAKES + inventory_maintenance.OTHER;
END#
$$
DELIMITER ;插入触发器后不能使用标记"New“。
https://stackoverflow.com/questions/20925444
复制相似问题