Error executing trigger mysql

Question

Error executing trigger mysql

Navigation

#1 by (0 votes)

1

Good afternoon warriors, I'm having a problem implementing a trigger in mysql. It happens that I created a table called auditing with the same structure as the main attendance table, this audit table records all events, actions that are done in the attendance table. It is working everything right, however a new need arose, when the service is finished the state = 1 field should be equal to state = 2, for this I modified the already existing trigger to be as follows:

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:

The save failed because of the following error:

Can not update table '#_list' in stored function / trigger because it is already used by statement which invoked this stored function / trigger. SQL = UPDATE #_set SET ordering = '2', state = '1', tem_cadastro = '1', createcadastro = '0', objective = '2', broker_name = '19', broker_email = '19' '3', emprendimento_torre = '1', data_hora_visita = '2016-01-01 00: 00: 01 ', remarks =' bla bla ', valorproposed =' 120.000,00 ', situation_proposed =' 2 ', counter_proposal =' 110.000,00 ', finalizar_atendimento =' 1 ', finalizar_motivo =' 1 ', another_empreendimento =' ', responsible =' 718 ' 2016-07-29 14:04:39 'WHERE id =' 2 '

I understand that I can not perform two actions at the same time because of the table, and I can not tweak the PHP CRUD for constraints, so my exit at that point was working with the triggers. / p>

mysql pdo active-record

asked by anonymous 29.07.2016 / 19:33

1 answer

"Uncaught SyntaxError: Unexpected token:" error when trying to fetch data in JSON format from another URL Giving ctrl + f in webbrowser by code

score 0 · Answer 1

It turns out that you have set a trigger for the tabela_atendimento table where the trigger fires in the UPDATE event, correct!?

But in your trigger you are trying to do an update on the same table, you are trying to call the trigger recursively, understand!?

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

Would not your idea make an UPDATE in the tabela_atendimento_auditoria table?

UPDATE

Based on this SOen topic and the documentation suggested in the same topic. I understand that if you do update the STATE field in a trigger triggered in the BEFORE UPDATE event, maybe that's right.

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

This is because of the fact that I mentioned earlier, you can not use a trigger to update the same table being called in the query event. But in that case you would be altering the value of the already selected line before doing UPDATE.