I have notification table (very large table). I need your help with the below scenario:
- select all notifications for
- insert these notifications in
- delete all these notifications from
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?