Error running Trigger mysql

Question

Good afternoon warriors, I’m having a problem implementing a Trigger in mysql. It turns out that I created a table called audit with the same structure of the main table of appointments, this table audit records all events, actions that are made in the table attendance. This working all right, however a new need arose, when the service is finalized the field state=1 should be equal state=2, for this I modified the existing Trigger to be the following way:

CREATE DEFINER=`root`@`localhost` TRIGGER `nomebanco`.`tabela_atendimento_triggernome` AFTER UPDATE ON `tabela_atendimento` FOR EACH ROW

BEGIN
    IF NEW.finalizar_atendimento = '1' THEN
        UPDATE tabela_atendimento SET state = '2' WHERE id = NEW.id;

    ELSE

        INSERT INTO tabela_atendimento_auditoria
        (
        id,
        asset_id,
        ordering,
        state,
        tem_cadastro,
        criarcadastro,
        objetivo,
        corretor_nome,
        corretor_email,
        empreendimento,
        empreendimento_unidade,
        empreendimento_torre,
        data_hora_visita,
        observacoes,
        valorproposta,
        situacao_proposta,
        contra_proposta,
        finalizar_atendimento,
        finalizar_motivo,
        outro_empreendimento,
        responsavel
        )
        VALUES
        (
        NEW.id,
        NEW.asset_id,
        NEW.ordering,
        NEW.state,
        NEW.tem_cadastro,
        NEW.criarcadastro,
        NEW.objetivo,
        NEW.corretor_nome,
        NEW.corretor_email,
        NEW.empreendimento,
        NEW.empreendimento_unidade,
        NEW.empreendimento_torre,
        NEW.data_hora_visita,
        NEW.observacoes,
        NEW.valorproposta,
        NEW.situacao_proposta,
        NEW.contra_proposta,
        NEW.finalizar_atendimento,
        NEW.finalizar_motivo,
        NEW.outro_empreendimento,
        NEW.responsavel
        );

    END IF;
END

before putting the IF worked perfectly, however now I have the return of an error in PHP with the following content:

Save failed because of the following error:

Can’t update table '#_call' in stored Function/Trigger because it is already used by statement which Invoked this stored Function/Trigger. SQL=UPDATE #_Support SET ordering='2',state='1',tem_cadastro='1',criarcadastro='0',objetivo='2',corretor_nome='19',corretor_email='19',empreendimento='2',empreendimento_unidade='3',empreendimento_torre='1',data_hora_visita='2016-01-01 00:00:01', remarks='bla bla',valuated='120.000,00',situacao_proposta='2',contra_proposta='110.000,00',finalizar_atendimento='1',finalizar_motivo='1',outro_empreendimento=''',responsavel='718',data_hora_registro='2016-07-29 14:04:39' WHERE id='2'

i understand that I can not perform two actions at the same time on account of the table, and I can not touch the CRUD of PHP by constraints, so my output at that time was to work with the triggers.

Answer 1

Turns out you set a Trigger for the table tabela_atendimento where Trigger is fired in the UPDATE event, correct!?

But in your Rigger you’re trying to update the same table, you’re trying to call Rigger recursively, you understand!?

...
IF NEW.finalizar_atendimento = '1' THEN
   UPDATE tabela_atendimento SET state = '2' WHERE id = NEW.id;
ELSE
...

Wouldn’t it be your idea to update the table tabela_atendimento_auditoria?

UPDATE

Based in this topic of Soen and in the documentation suggested in the same topic. I understand that if you update the STATE field in a Rigger fired in the BEFORE UPDATE event, maybe.

IF NEW.finalizar_attendance = '1' THEN SET NEW.STATE = 2;

As I said before, you cannot use a Rigger to update the same table that is being called in the query event. But in this case you would be changing the value of the line already selected before doing the UPDATE.