MySQL : Stored Procedure from Trigger giving duplicate results

MySQLstored-procedurestrigger

I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK.

When i'm calling this stored procedure from AFTER DELETE trigger, from multiple tables it is inserting duplicate values. Basically, same PK value is getting inserted for all table names in the audit table. So, if i delete a record from a table, number of inserts in audit table for single delete = number of triggers.

CREATE PROCEDURE sp_ins_log(
trigger_name VARCHAR(64),
column_name VARCHAR(64),
column_value VARCHAR(255),
requested_by VARCHAR(100))
INSERT INTO log (request_id,reference_schema, reference_table, reference_field, reference_id, action_performed, requested_by)
SELECT 
(SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id = CONNECTION_ID()),
EVENT_OBJECT_SCHEMA, 
EVENT_OBJECT_TABLE, 
column_name,
column_value, 
CONCAT(ACTION_TIMING,'_',EVENT_MANIPULATION),
IFNULL(requested_by,CURRENT_USER()) 
FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();

CREATE TRIGGER tr_table_ad
AFTER DELETE
ON table_name FOR EACH ROW
CALL sp_ins_log('table_name',old.id);

But, if i put the same insert query individually in each trigger, it works fine.

Reproduced the issue DB Fiddle

Compare this with direct execution of INSERT query inside SP.

Best Answer

The reason is simple - inaccuracy. Variable name and column name interference. You type WHERE TRIGGER_NAME = trigger_name. Both TRIGGER_NAME and trigger_name are treated as input variable name, and this condition is always true. You have 2 triggers matched the rest condition TRIGGER_SCHEMA = SCHEMA(), so query returns 2 rows.

Simply rename input variables. Or add table alias and allow the server to distinguish the column name and variable name: WHERE INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();.

Related Question