Optimal SQL solution I have notification table (very large table). I need your help with the below scenario:


select all notifications for user id;
insert these notifications in notification_log table;
delete all these notifications from the notification table.


My thoughts :


create flag column in notification table and create on update trigger on it to do steps 2 and 3 in the above scenario. Draw Backs: trigger complicity o(n) and I am always select bulk of notification so it will be not nice for performance;
create simple SQL procedure to do the above scenario. Draw Backs: what if step of the scenario failed to commit the whole procedure will roll-back.


Can you help me to optimize this?
How about only SQL statements! no triggers, and no SPs; within a transaction for integrity:

START TRANSACTION;
INSERT INTO notification_log SELECT field1, field2, ... FROM notification_table WHERE user_id=123;
DELETE FROM notification_table WHERE user_id=123;
COMMIT;


Make sure you have an index on user_id.

HTH