Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Mar 30 '13 at 18:14

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
    
Thank you David. I had misunderstood triggers. Now wondering if I can handle it with just triggers instead of scheduling. CREATE OR REPLACE TRIGGER triggerName BEFORE UPDATE ON tableA FOR EACH ROW BEGIN :new.date1 = :old.date1 :new.date2 = :old.date2 :new.date3 = :old.date3 WHERE primarykey IN (1,2,3,4,5,6,7,8,9) AND status!='cancelled' END Will this work for my case? –  chakraps Mar 28 '13 at 14:28
1  
@chakraps if you want to ask that question, please go ahead with a new question rather than changing this one which has been well answered. We prefer not to have moving targets! :) –  Jack Douglas Mar 31 '13 at 13:48
    
@JackDouglas - this question was migrated from SO, where I answered it. Perhaps the culture is different here, but in SO we often answer the question the OP should have asked - it's part of the teaching process. Anyway, because you have zapped my answer I don't have the possibility of improving it to address the main point. –  APC Apr 1 '13 at 20:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.