Trigger after removal Does not work

Question

Here is my code

CREATE TRIGGER `agent_maintenance` AFTER DELETE ON `users` FOR EACH ROW BEGIN INSERT INTO deleted_agents (agent_id, fullname, email, mobile_no, deleted) SELECT user_id, fullname, email, mobile_no, NOW() FROM user_profiles WHERE user_id = OLD.id; END // DELIMITER ; 

Apparently nothing was inserted into the deleted_agents table
But when I change the event to BEFORE DELETE , it works fine.
Does anyone know what is wrong?

EDITED
Yes, there is a foreign key constraint inside the user_profiles table.
Basically, when a row from the user table is deleted, it will delete the corresponding row in the user_profiles table.

So, I am mistaken in the assumption that the trigger will be executed first before any foreign key constraint action

The best way to do this is to use the AFTER DELETE trigger for user_profiles itself.
Unfortunately mysql dosent triggers a foreign key constraint trigger

Answer 1

Maybe, since the row was deleted, the cascading delete trigger in the user_profiles table executed and the WHERE clause cannot find user_id?