I made a trigger in mysql to save a query to a table that is executed whenever there is an update or it in the provider table.
On my computer, localhost with xampp, it works normal, that is, every time I update or delete a record in the table it saves in the table the query log executed.
I created the trigger on my mysql server online. It happens that when I update (or delete) a record it executes the trigger by saving the query in the log table the first time, the second time it does not execute the trigger, the third time it executes, the fourth does not execute, and so on, or whether it keeps switching between executing and not executing the trigger.
I had to comment on this line online because I was giving it a bug in worckbench:
RESIGNAL SQLSTATE '21S01' SET MESSAGE_TEXT = 'TRIGGER tg_tab_fornecedor_after_d';/*RETORNA O NOME DA TRIGGER EM CASO DE ERRO*/
In my local environment if I remove this line, the same error occurs, the first upadte activates the trigger, the second does not, the third activates the trigger, the fourth does not, and so on.
Follow the full trigger:
DROP TRIGGER IF EXISTS tg_tab_fornecedor_after_d;
DELIMITER $
CREATE DEFINER='root'@'127.0.0.1' TRIGGER 'tg_tab_fornecedor_after_d'
AFTER DELETE ON 'fornecedor'
FOR EACH ROW
BEGIN
DECLARE original_query TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION /*DECLARAR EXECEÇÃO*/
RESIGNAL SQLSTATE '21S01' SET MESSAGE_TEXT = 'TRIGGER tg_tab_fornecedor_after_d';/*RETORNA O NOME DA TRIGGER EM CASO DE ERRO*/
/*LOG - INSERIR NOVO REGISTRO DE LOG */
SET @id = (select ifnull(max(log.log_id)+1,1) from log);
SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
insert into log
values(
@id,
substring_index(session_user(),'@',1),
now(),
substring_index(session_user(),'@',-1),
'D',
original_query
);
END$
DELIMITER ;
DROP TRIGGER IF EXISTS tg_tab_fornecedor_after_u;
DELIMITER $
CREATE DEFINER='root'@'127.0.0.1' TRIGGER 'tg_tab_fornecedor_after_u'
AFTER UPDATE ON 'fornecedor'
FOR EACH ROW
BEGIN
DECLARE original_query TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION /*DECLARAR EXECEÇÃO*/
RESIGNAL SQLSTATE '21S01' SET MESSAGE_TEXT = 'TRIGGER tg_tab_fornecedor_after_u';/*RETORNA O NOME DA TRIGGER EM CASO DE ERRO*/
/*LOG - INSERIR NOVO REGISTRO DE LOG */
SET @id = (select ifnull(max(log.log_id)+1,1) from log);
SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
insert into log
values(
@id,
substring_index(session_user(),'@',1),
now(),
substring_index(session_user(),'@',-1),
'U',
original_query
);
END$
I checked that the version of my mysql in xampp is version 5.6.21 and my mysql is version 5.1.73 online. Anyone know what might be happening?