Oracle trigger to update columns daily

Question

I'm trying to create a trigger that would run twice daily and do updates based on a specific condition.

To be run on Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

We have Table A and Table B with columns

1. Primary Key
2. Date 1
3. Date 2
4. Date 3
5. Status

For a specified set of Primary Keys in Table A, until Table A.Status==Completed, I want the Date fields to be copied over from Table B to Table A twice daily. The same primary keys will be already setup in Table B. The number of records to update will decrease over time as more records in the primary keys list reach completed status.

The purpose is to maintain certain static values on the Date fields till status==completed. We are unable to control an external system that keeps over-writing them. As a stopgap we want to rewrite them at frequent intervals to have the notion of maintaining static values. Once all PKs in our list reach completed status, we want to turn off the trigger.

Answer 1

This is not what triggers do -- they act on an event, such as a change to data.

Use DBMS_Scheduler to set up a repeating event.

Consider applying a function-based index to include only those rows that need updating, and make sure the query is constructed to be able to use it.