Trigger is not working
hello,
I am creating a table and a TRIGGER so when I create/update a register, it calculates by itself.
If I left "blank" the column that is to be calculated, I have an error "1064"
------------------------------------------------------------
delimiter $$
CREATE TABLE res_pro_servicios (
id_servicio int(10) unsigned NOT NULL AUTO_INCREMENT,
id_proveedor_res int(10) unsigned DEFAULT NULL,
no_reservacion int(11) DEFAULT NULL,
servicio varchar(255) DEFAULT NULL,
descripcion varchar(255) DEFAULT NULL,
fecha date DEFAULT NULL,
subtotal decimal(10,2) DEFAULT '0.00',
iva decimal(10,2) DEFAULT '0.00',
total decimal(10,2) DEFAULT '0.00',
confirmar_cv01 set('si','no') NOT NULL DEFAULT 'no',
cancelado_cv01 set('si','no') NOT NULL DEFAULT 'no',
eliminado_hi01 set('si','no') NOT NULL DEFAULT 'no',
PRIMARY KEY (id_servicio)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci$$
CREATE TRIGGER calcula_iva_res_servicios
BEFORE UPDATE ON res_pro_servicios
FOR EACH ROW
BEGIN
UPDATE res_pro_servicios
SET total = (NEW.subtotal*(NEW.iva/100))
WHERE id_servicio = NEW.id_servicio;
END$$
DELIMITER ;
------------------------------------------------------------
Best Regards.
Marco Antonio Rios P.
Mexico City