Home / Questions / 3393
Accepted
Juan
Asked: 2020-01-30 11:18:58 +0800 CST 2020-01-30 11:18:58 +0800 CST

Query if inside a trigger

  • 772

If I update person with an empty one that does not insert anything in Audit, but if I update person with a data, name, etc., that if I do a INSERT. I have a table of reservations with schedules, when someone makes a reservation I update the person field associated with the schedule, and that triggers the triggerone that leaves the record of who registered, when, etc., but in turn I have an event that every day at 00 :00:00 updateall the people fields are empty so that the next day it can be solved again, that is the problem, the updatereserve one is ok, when the event is executed triggerit takes the 24 empty updates and inserts 24 empty data in the audit table

This is the code triggerI currently have

DELIMITER $$
CREATE TRIGGER Pc_Auditoria 
AFTER UPDATE ON pcTotal
FOR EACH ROW
INSERT INTO auditoria (pc, personaAnterior, horario, persona, fecha_mod)
VALUES (NEW.pc, OLD.persona, NEW.horario, NEW.persona, NOW());
DELIMITER ;

when i make a reservation The audit table Ok Audit table when the event is executed

When I make a reservation: The record inserted ok in the audit: How is the audit when the event is executed:

mysql

1 Answers

  1. Best Answer

    You can try the following:

    DELIMITER $$
CREATE TRIGGER Pc_Auditoria AFTER UPDATE ON pcTotal
FOR EACH ROW
BEGIN
    IF <tu condición aca> THEN BEGIN
        INSERT INTO auditoria (pc, personaAnterior, horario, persona, fecha_mod)
        VALUES (NEW.pc, OLD.persona, NEW.horario, NEW.persona, NOW());
    END; END IF;
END$$
DELIMITER ;

    Where <tu condición aca>it satisfies the validations that you think correspond. As you mention in your question if the field is empty then it should not be updated. I imagine that by empty you mean NULL, so the condition would be something like this:

    IF NEW.persona IS NOT NULL THEN

    According to your comments:

    • The field to check is coordinador.
    • The trigger must be before update pcTotal.

    With this in mind, your trigger would look like this:

    DELIMITER $$
CREATE TRIGGER Pc_Auditoria BEFORE UPDATE ON pcTotal
FOR EACH ROW
BEGIN
    IF COALESCE(NEW.coordinador, '') != '' THEN BEGIN
        INSERT INTO auditoria (pc, personaAnterior, horario, persona, fecha_mod)
        VALUES (NEW.pc, OLD.persona, NEW.horario, NEW.persona, NOW());
    END; END IF;
END$$
DELIMITER ;

    Note the use of the function COALESCEto validate if the value of NEW.coordinadoris NULLthen return an empty string ''.

    • 3