gs79
asked on
Alert notification/Trigger on NO Data or Data Stalls
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.
Thanks
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.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you please provide me with a sample script or links explaining writing such procedures to send emails using utl feature along with scheduling the job..
Thanks
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I didnt get a chance to implement this. I may play with the tutorials sometime later. We used someother application to monitor which was already in place..
Thanks for the suggestions..
Thanks for the suggestions..
Write a procedure and chedule a job as slightwv suggests.