Query if inside a trigger

Question

Query if inside a trigger

Navigation

#1 by (2 votes)

2

If I update a person with a vacuum that does not insert anything in Audit, but if I update a person with a data, name, etc., that does a INSERT . I have a reservation table with schedules, when someone booking updated the person field associated with the schedule, and that triggers the trigger that leaves the record of who registered, when, etc., but in turn I have an event that every day at 00:00:00 update all fields empty people so that the next day can be solved again, that is the problem, the update reservation is ok, when the event is executed the trigger I take the 24 empty updates and insert 24 empty data in the audit table

This is the trigger code that I 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 record inserted ok in the audit: How is the audit when the event is executed:

mysql

asked by Juan 29.01.2016 в 20:18

source

1 answer

Is it possible to save what is drawn in the "Panel" control using the Paint event? Get the value of a dropdown with Bootstrap optgroup in ASP.NET

score 2 · Accepted 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> satisfies the validations that you create corresponding. As you mentioned in your question if the field is empty then it should not be updated. I imagine that with 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 review is coordinador .
The trigger must be before updating pcTotal .

With this in mind, your trigger would be as follows:

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 COALESCE to validate if the value of NEW.coordinador is NULL which then returns an empty string '' .