Mysql trigger from one base to another

Question

I am trying to create a Trigger in Mysql where I need in updating a table (I will 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 every time I try to create Mysql it shows the following error:

CREATE TRIGGER B1.TB1.trigger_schedule_vinculo_situacao AFTER UPDATE ON B1.TB1 for each Row Begin if (new.situation <> 'asset') THEN delete from B2.TB2 Where username = NEW.ready_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

Answer 1

I was able to figure out the problem. Since the version of my mysql is 5.5 it has some particularities in the construction. It doesn’t change much, it’s details that affect. You have to use the delimiter and the ; corresponding.

See the code below that it accepts.

Ps.: Now I found another problem that the table already has a Rigger de afeter Insert, then I will have to change the existing one to include the above code, because mysql gives error 1235 stating that it is not possible more than one Rigger 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 ;