Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - Trigger for updating same table after insert

Here's what I'm trying to do:

When there's a new INSERT into the table ACCOUNTS, I need to update the row in ACCOUNTS where pk = NEW.edit_on by setting status='E' to denote that the particular (old) account has been edited.

DELIMITER $$  DROP TRIGGER IF EXISTS `setEditStatus`$$ CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTS FOR EACH ROW BEGIN     update ACCOUNTS set status='E' where ACCOUNTS.pk = NEW.edit_on ; END$$  DELIMITER ; 

The requirement is NOT that I manipulate the newly inserted column, but an already existing column with pk = NEW.edit_on

However, I can't update the same table: Can't update table ACCOUNTS ... already used by the statement that invoked this trigger

Please suggest a workaround

PS: I have already gone through Updating table in trigger after update on the same table, Insert into same table trigger mysql, Update with after insert trigger on same table and mysql trigger with insert and update after insert on table but they dont seem to answer my question.

Edit

ACCOUNTS Table:

CREATE TABLE  `ACCOUNTS` (   `pk` bigint(10) unsigned NOT NULL AUTO_INCREMENT,   `user_id` bigint(9) unsigned NOT NULL,   `edit_on` bigint(10) unsigned DEFAULT NULL,   `status` varchar(1) NOT NULL DEFAULT 'A',   PRIMARY KEY (`pk`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2147483726 DEFAULT CHARSET=latin1 
like image 318
th3an0maly Avatar asked Oct 13 '12 22:10

th3an0maly


People also ask

Can we use trigger on same table?

Is there any way I can make this work? Thanks everyone! Trigger can NOT alter the table which altering fires this thigger. No exclusions.

Does after update trigger fire on insert?

If a trigger is defined as AFTER UPDATE it does NOT fire after an INSERT. Can you share the code from your triggers? If we *assume that your insert trigger updates the same table, then that action will cause the update trigger to execute.

Can we update a table using trigger?

AFTER UPDATE Trigger in SQL is a stored procedure on a database table that gets invoked or triggered automatically after an UPDATE operation gets successfully executed on the specified table. For uninitiated, the UPDATE statement is used to modify data in existing rows of a data table.

How do I create a trigger update in MySQL?

Introduction to MySQL AFTER UPDATE triggers In this syntax: First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause. Second, use AFTER UPDATE clause to specify the time to invoke the trigger. Third, specify the name of the table to which the trigger belongs after the ON keyword.


1 Answers

It seems that you can't do all this in a trigger. According to the documentation:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

According to this answer, it seems that you should:

create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

With a stored proc you'll manually commit the changes (insert and update). I haven't done this in MySQL, but this post looks like a good example.

like image 191
pjama Avatar answered Sep 29 '22 13:09

pjama