CODEWITHSUNDEEP
mysqlsqltriggers
Isaac
Isaac

Reputation: 218

How to set up mysql multi-triggers

How can i keep the below these triggers into a single trigger?

I have tried it, but i am not sure what mistake i am doing?

Below are my trigger's

  1. create trigger Disminuir_Existencia1
after insert
on detalle 
for each row 
update producto set existencia =
                         existencia-new.Cantidad
                       where id_p=new.id_p

  2. create trigger Aumentar_Existencia
after insert
on detalle
for each row    

update producto set existencia =if( new.activo = 0, producto.existencia +
 new.cantidad,producto.existencia)

where new.id_P = producto.id_P

  3. CREATE TRIGGER aumentar
AFTER insert
on detalle
for each row

update factura set total =
 ( 
     select producto.precio * new.cantidad
    from producto 
    where new.id_p=producto.id_p)

 where new.Folio= factura.folio;

Upvotes: 0

Views: 77

Answers (2)

Andriy M
Andriy M

Reputation: 77737

The correct solution in your situation may depend on what you have actually tried already and what were the results of your attempts.

For instance, it may turn out that you simply need to enclose your UPDATE statements in BEGIN ... END and that will be enough:

create trigger CombinedDetalleInsertTrigger
after insert
on detalle 
for each row 
begin
  update ... where id_p=new.id_p;

  update ... where new.id_P = producto.id_P;

  update ... where new.Folio= factura.folio;
end;

as this answer suggests.

In many cases, however, you would also need to use the DELIMITER instruction:

delimiter $$

create trigger CombinedDetalleInsertTrigger
after insert
on detalle 
for each row 
begin
  update ... where id_p=new.id_p;

  update ... where new.id_P = producto.id_P;

  update ... where new.Folio= factura.folio;
end$$

delimiter ;

as explained in this answer.

Still, it may not always be possible to use DELIMITER as that is a client instruction rather than a MySQL statement, and not all MySQL clients support it. In that case you can try following this answer's suggestions: use the allowMultiQueries=true connection property or remove the final ; (the one after the end).

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21677

Mysql doesn't support the one trigger for all the operations like for example sql-server does.

You are doing it the way you have to in mysql.

If you want to have the code centralized, you can create a procedure and call the same procedure from the 3 different triggers with the NEW/OLD information and an identifier for the event (I/U/D).

Upvotes: 0

Related Questions