Trying to flash an alert if the journal of it has not been updated in the last 24hours.
I am able to get Serial of the alerts which have not had a journal update in last 24s, however I am stuck at a point of setting Flash=1 for all the Serials which I get from below query
select Serial, max(RowSerial)
FROM alerts.journal where Chrono <= (getdate - 86400)
GROUP by Serial;
Any help would be appreciated
Pinned topic Flashing - Journal table not updated
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-01-30T21:47:17Z at 2013-01-30T21:47:17Z by webman123
SystemAdmin 110000D4XK1283 Posts
Re: Flashing - Journal table not updated2013-01-30T17:35:03ZThis is the accepted answer. This is the accepted answer.Hi
I guess this is for a temporal trigger? Try something like the following
create or replace trigger group flash_triggers; create or replace trigger flasholdjournal group flash_triggers priority 10 comment 'Set an alarm to flash if a journal update has not been created within in a given time limit' every 60 seconds evaluate select Max(Chrono) as MaxChrono, Serial from alerts.journal group by Serial bind as tt begin for each row x in tt begin if (x.MaxChrono <= (getdate - 86400)) then update alerts.status set Flash = 1 where Serial = x.Serial; end if; end; end;
A couple of notes:
Normally the evaluate statement can be replaced with a straight for each row, which is faster. But in this case evaluate is needed because we want to use the group by syntax.
The trigger won't catch alarms that have no journals applied to them.
You'll want to keep an eye on how the trigger performs in production, its had no testing.
I hope this helps.