Categories
Database Development

MySQL : Stored Procedure from Trigger giving duplicate results

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_value VARCHAR(255))
BEGIN

INSERT ....

END$$
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.

Leave a Reply

Your email address will not be published. Required fields are marked *