The BEFORE INSERT trigger works only on a BEFORE UPDATE

Question

The BEFORE INSERT trigger works only on a BEFORE UPDATE

Navigation

#1 by (0 votes)

2

I have this trigger ( trigger ) named BEFORE UPDATE in MySQL :

CREATE DEFINER='root'@'%' TRIGGER 
'CalcularCamposCalculadosActuacionesU' BEFORE INSERT ON 'actuacion' 
FOR EACH ROW BEGIN
    SET NEW.TieneFiltrosValidos = 
        (select case when a.IdActuacion in 
        (select ac.IdActuacion from actuacion ac
         left join MaestraEstado es on es.IdMaestraEstado = ac.IdMaestraEstado
         where (((es.Valor <> "FIN") and (es.Valor <> "REVISA"))
                  or (ac.Incurrido2016 > 0)) and (es.Valor <> "ELIMINADO") 
                ) then "Sí"
                else "No"
         end Valor
     from actuacion a
     where a.IdActuacion = NEW.IdActuacion);'

According to some conditions, taken from a master table, I want to fill the field of the entity actuacion with a "Sí" , if it meets the condition, and otherwise a "No" .

On the trigger BEFORE UPDATE , yes, I update this field, while on the insert, no.

I do not jump any errors or anything, just leave that field to null.

mysql trigger

asked by user32284 09.03.2017 в 11:39

source

1 answer

Send data from a form to an Action using struts2, AJAX, and jquery dialog How can you decrease the amount of a field in a column in Mysql?

score 0 · Accepted Answer

The problem is that the query inside the trigger does not return any record in the case of a INSERT , so the field TieneFiltrosValidos remains at null . If we simplify the query, what you have is basically:

select ...
  from actuacion a
 where a.IdActuacion = NEW.IdActuacion

In other words, you are looking for the record you are about to insert. But how are you within BEFORE INSERT (emphasis in BEFORE ), that means that the record does not yet exist in the actuacion table, so the query it will not return any results.

Of course, in the case of a UPDATE , the thing is different, because the record already exists before UPDATE , and that's why it works.

It will be your turn to review the design of your query. Actually, the one you're trying to consult the same table that you're inserting seems a bit strange to me.

Here I leave a modified version of your query, which I think should be equivalent to what you are doing, but prevents the trigger from trying to consult the record you are about to insert. With more understanding of what you do, surely it can be improved.

SET NEW.TieneFiltrosValidos = (
  select case when exists (
    select null
      from (select null) x
      left join MaestraEstado es
        on es.IdMaestraEstado = NEW.IdMaestraEstado
     where ((es.Valor <> 'FIN' and es.Valor <> 'REVISA') or NEW.Incurrido2016 > 0)
       and es.Valor <> 'ELIMINADO')
  then 'Sí' else 'No' end);

Also, I mention that you possibly have a defect with your conditions es.Valor <> .. . Because in each of those cases, it seems that it is possible that es.Valor is null . If that becomes the case, take into account that verifying a value null with the operator <> will not evaluate true , as you might expect. As you probably know, comparisons with the value null must be made with IS NULL or IS NOT NULL to work correctly.