Trigger Mysql from one base to another

Question

Trigger Mysql from one base to another

Navigation

#1 by (0 votes)

0

I'm trying to create a trigger in Mysql where I need to update a table (I'll call tb1) depending on the situation field, delete a record in another database from a table.

Base 1 = B1 Table 1 = Tb1 Base 2 = B2 Table 2 = Tb2

- trigger I'm trying to create

CREATE TRIGGER trigger_agendamento_vinculo_situacao AFTER UPDATE ON B1.TB1 for each row begin
     if (new.situacao <> 'ativo') THEN 
        delete from B2.TB2 where username = NEW.prontuario_original;
  end;

But whenever I try to create MySQL it has the following error:

CREATE TRIGGER B1.TB1.trigger_agendamento_vinculo_situacao AFTER UPDATE ON B1.TB1 for each row begin if (new.situation &>; 'active') THEN delete from B2.TB2 where username = NEW.Prontuario_original Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 0.000 sec

mysql sql-update delete

asked by anonymous 04.01.2019 / 13:53

1 answer

Search City by State with Ajax and Laravel Select using Enum

score 0 · Answer 1

I discovered the problem. As the version of my mysql is 5.5 the same has some particularities in the construction. It does not change much, they are details but they affect. It has to use the delimiter and the; correspondent.

See the code below that he accepts.

Ps: Now I found another problem that the table already has an affect trigger, so I will have to change the existing one to include the above code, because mysql gives error 1235 stating that it is not possible to trigger more than one trigger for the same action.

Let's go to the code:

DELIMITER $$
DROP TRIGGER IF EXISTS 'B1'.'trigger_update_radius_vinculo'$$
CREATE TRIGGER trigger_update_radius_vinculo AFTER UPDATE ON B1.TB1 for each row 
Begin
   if new.situacao <> 'ativo' THEN 
    delete from B2.TB2 where username = NEW.prontuario_original;
 End if;
End;
$$
DELIMITER ;