-2

Table ArticulosTable detalle_ventasI want to bring values ​​from my articles table and save them in another, I am occupying this trigger but it does not work for me, hopefully you can help me. THANK YOU

    DELIMITER $$
    CREATE TRIGGER `cancelar_articulo` AFTER UPDATE ON `detalle_ventas` FOR EACH ROW BEGIN
    IF NEW.inventariable = 1 AND NEW.estado = 'Venta Cancelada' then
    INSERT INTO registro(nombre,stock,tipo,existencia,created_at)
    VALUES(NEW.articulo,NEW.cantidad,NEW.inventariable, articulos SET stock = stock + 
    NEW.cantidad,NOW());
    WHERE articulos.id = NEW.idarticulo
    END IF;  
    END
     $$
    DELIMITER ;
Christian Aldana
  • 11
  • 4
  • 2
    The where statement is standing all by itself what's it for? And you seem to be mashing up insert an update statements. – P.Salmon Sep 13 '20 at 07:14

1 Answers1

0

Not a mySQL dev (am a SQL Server dev) but the "WHERE" clause looks to me to be in the wrong place. I believe you have an insert statement and you're trying to filter which records you put into the other table?

In that case I'd expect the "WHERE" expression should be part of the "IF" statement. However, another issue I can see (perhaps this is a mySQL idiosyncracy) is you are referencing "articulos.id" - but there doesn't appear to be a table "articulos" in your query anywhere. This makes me think you are trying to insert any records that have "NEW.idarticulo" that is contained in a table called "articulos".

I can see two possible solutions (use one or the other) - though not sure how they will go with mySQL.

  1. Use an EXISTS() clause in your IF statement.

    IF NEW.inventariable=1 AND NEW.estado = 'Venta Cancelada' AND EXISTS(SELECT 1 FROM articulos WHERE id = NEW.idarticulo) THEN

  2. Use a SELECT query to insert your records and an INNER JOIN to ensure you only get records that match.

    INSERT INTO registro(nombre, stock, tipo, existencia, created_at) SELECT NEW.articulo,NEW.cantidad,NEW.inventariable, articulos SET stock = stock + NEW.cantidad,NOW() FROM NEW n INNER JOIN articulos a ON (n.idarticulo=a.id)

The only problem I can see with the above is the articulos SET stock = stock + NEW.cantidad part of the query - I'm not sure what you are trying to do here. I believe you are trying to sum up the values as you go - in that case (again perhaps mySQL lets you do this) you would need to verify if a record already exists in registro. If it doesn't exist, you do an insert, otherwise you do an update.

I believe the following is what you would need to use in that case:

mySQL INSERT or UPDATE

All that being said is there a significant gain in keeping the running total over just querying your tables? Triggers (in SQL Server) can have a detrimental effect on application performance in high volume systems. It can be more efficient to just execute a SELECT on your source table (with appropriate indexes) and add the values when you need them.

pookemon
  • 81
  • 7
  • what I require is to obtain the value of the stock column in my article table and add the quantity column of my sales_detail table, referring to the id article – Christian Aldana Sep 13 '20 at 01:40