Trigger to update mysqli php record

Question

Trigger to update mysqli php record

Navigation

#1 by (0 votes)

1

Hi, I'm new to the trigger and I have a problem. I have a table with 2 fields | quantity | criteria | and I need that when updating the amount the trigger updates the criterion: example if I update the amount to > 0 then which criterion is updated to Won, if quantity = 0 then criterion that updates to Lost and if quantity is empty then criterion passes to pending.

<?php
$id_mercado = $_POST['id_mercado'];
$cantidad = $_POST['cantidad'];
$criterio = $_POST['criterio'];

$sql = $mysqli->query("UPDATE mercado SET cantidad='$cantidad',  criterio='$criterio' WHERE id_mercado=$id_mercado");
?>

the trigger I have is this

CREATE TRIGGER estado_update BEFORE UPDATE ON mercado FOR EACH ROW BEGIN if new.cantidad > 0 then update mercado SET criterio = 'Ganó'; end if; END;

I have not been able to do with the nested if and I do not know how to properly extructure the code, the trigger believes it in phpmyadmin, someone can guide me.

mysql php trigger

asked by 09.07.2017 в 21:58

source

1 answer

Capture and record current date in disable field Help in Mongoose and mongoDB with linked queries

score 0 · Accepted Answer

Hello for your case you should do the trigger in the following way.

DELIMITER //
CREATE TRIGGER estado_update BEFORE UPDATE ON mercado FOR EACH ROW
BEGIN
    IF (NEW.cantidad>0) THEN 
	SET NEW.criterio = 'Ganó'; 
    END IF;
    IF (NEW.cantidad=0) THEN
        SET NEW.criterio = 'Perdió';
    END IF;
    	IF (NEW.cantidad ='') THEN 
        SET NEW.criterio = 'Pendiente'; 
    END IF;
END;//
DELIMITER ;