Topic
  • 2 replies
  • Latest Post - ‏2013-01-30T21:47:17Z by webman123
webman123
webman123
7 Posts

Pinned topic Flashing - Journal table not updated

‏2013-01-30T15:06:00Z |
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;
go
Any help would be appreciated
Updated on 2013-01-30T21:47:17Z at 2013-01-30T21:47:17Z by webman123
  • SystemAdmin
    SystemAdmin
    1283 Posts

    Re: Flashing - Journal table not updated

    ‏2013-01-30T17:35:03Z  
    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.

    Regards

    Steve
  • webman123
    webman123
    7 Posts

    Re: Flashing - Journal table not updated

    ‏2013-01-30T21:47:17Z  
    Steve,

    This is great!

    thank you very much for the help.