Re: Automatic update (periodic)

chani wrote:
> Hi,
>
> This may be another horrible idea.
>
> ** Is There any way to auto update columns of database table.
> ==============================================================
>
> Suppose I'm managing a database for school children.
> There is a table to hold all student's continuous assesments marks.
>
> (Student_id, date, assesment_no, marks)
>
> At the end of each month total for that month and full total(up to
> date)
> should be calculated.
> I have another table for this.
>
> (Student_id, total_january, total_february, ....... , Total)
>
>
> At the end of month respective total column to that month and last
> column (total)
> should be updated.
>
> I can do this by manually executing a procedure at the end of each
> month.
>
> But is there any automatic way to do this.????
> I can store procudure. It should Execute at the proper
> time by looking at system clock.
>
> I hope you understood my problem.
> Please let me know if it is not clear (Wrong grammer etc..)
>
> Please Give me some Idea to solve this problem.

As others have said, dbms_jobs will schedule a proc to do this, but my question is "why?"

That whole 'totals' table flys in the face of good db design. Database 101, First rule of thumb: Never store data that can be derived from other data. Now, that rule of thumb isn't hard and fast, and others will be able to provide many examples of when it should be broken. However, yours is a textbook example of how not to do it. In fact, it is such a textbook example, that it has 'homework' written all over it.  Besides, your column 'date' has a col. name that is a reserved word.

Instead of the 'totals' table, just

SELECT Student_id, sum(marks)
FROM assesment_table
GROUP BY to_char(assess_date,"yyyy-mm"); Received on Wed Apr 12 2006 - 20:38:41 CDT