Question: I have a table say 'scd_reading' where the data is inserted every 15 seconds. the data is in the following format:
Time mwr value
18-APR-10 06:15:00 24 23
18-APR-10 06:15:15 26 22
18-APR-10 06:15:30 23 21
18-APR-10 06:15:45 23 14
There are two undesired things that could happen sometimes
1. There is no data being updated to this table
2. The readings may start stalling at a point. ie the columns 'mwr' and 'value' may be showing the same readings for every subsequent 15 seconds.
I am looking to write a trigger to monitor this every half hour and fires
1. when there is no data update and writes a message say 'no data update' to the table called 'alert_tab'. ie when the trigger runs every half hour and if there is no data update in the past 5 min at the time it runs.
2. To write a message 'the reading stalled' when the reading stalls, that is past 10 readings for 'mwr' column and 'value' column are same at the time the trigger runs.
I am gonna schedule a report to run against 'alert_tab' table every half hour just after a minute the trigger is shceduled to run and catch any messages that may have populated in the table and send an alert notification to the user. I can reset this table when I run the report.
I need help writing a trigger to do as described above. If there are any better methods available to send an alert to the user..please let me know. I am using Oracle 10g database.