Search code examples
mysqlsqltriggerssql-deletemysql-error-1442

Delete from a table after update in another table causes an error in mysql

I have two table in a MySQL database: 'users' and 'subscription' in the table 'users', each users have a subscriptionID which is the ID of a line in 'subscriptions' or NULL. I need to create an update trigger on 'users' that delete the rows in 'subscriptions' where the new value of subscriptionID is set to null.

Here is my trigger :

CREATE TRIGGER `trg_DeleteSubscriptions` AFTER UPDATE ON `users`
FOR EACH ROW IF (NEW.subscriptionID <=> NULL) THEN 
DELETE FROM subscriptions s WHERE s.subscriptionID = OLD.subscriptionID; 
END IF

There is no problem at the creation of my trigger, however I have a recursive event that execute this query : 

UPDATE users SET AccountState = 2, subscriptionID = null WHERE UserID IN
(SELECT * from (SELECT u.UserID FROM users u INNER JOIN subscriptions a ON
u.subscriptionID = a.subscriptionID WHERE a.EndDate < CURRENT_TIMESTAMP) as c)

and cause an error : #1442 - Can't update table 'subscriptions' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

(I know that the query is strange with the (select * from (select ..) as c), but that was to fix another problem where i couldn't update the 'users' table because it was used in the subquery, by creating a alias / temp table)

I don't understand what is the problem since the trigger is called from the 'users' table and the delete is made in 'subscriptions' ??

UPDATE : I'm pretty sure that the problem is caused by the event's query and not the trigger itself, since i tested with that simple query 'UPDATE users SET SubscriptionID = null WHERE UserID = 24 and the trigger executed correctly without error...

Thanks a lot !

Solution

  • This is a documented restriction in MySQL.

    The SQL statement referencing table subscriptions is causing a trigger to be fired.

    So the trigger is not allowed to modify the contents of the subscriptions table.

    If the trigger attempts to apply a change to subscriptions table, MySQL raises an error.

    This restriction is documented in the MySQL Reference Manual.