CODEWITHSUNDEEP
mysql
slier
slier

Reputation: 6750

Trigger After Delete Not working

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 got inserted to table deleted_agents
But when i change the event to BEFORE DELETE it work just fine.
Anyone know what is wrong?

EDITED
Yes there is foreign key constraint inside table user_profiles.
Basically when a row from table user got deleted, it will delete a coresponding row on table user_profiles.

So i make a wrong assumption that a trigger would be executed first before any foreign key constraint action

The best way to do this is to use AFTER DELETE trigger on table user_profiles itself.
Unfortunately mysql dosent activate the trigger on foreign key constraint

Upvotes: 4

Views: 2503

Answers (1)

actkatiemacias
actkatiemacias

Reputation: 1473

Could it be that since the row has been deleted a cascading delete trigger on the user_profiles table has executed and the WHERE clause cannot find the user_id?

Upvotes: 2

Related Questions