NAS_ALARM_NOTE History

[Legacy User]

I've been trying to figure out a way to get the note IDs for alarms that have been closed. I know that NAS_ALARM_NOTE contains all note IDs of active alarms. As soon as an alarm is acknowledged the nimid is removed from that table. Has anyone found a way to make the data persistent?

[Anon Anon]

The reason for the id (or id's) not being stored with the alarm history is the chance of the note being deleted after the alarm has been acknowledged.  You can, however, add some trigger work to the database that will store the information you are after in a separate table.  So the next step is to update the database.db file with the following (or run the enclosed script in the NAS).  When this is done, all new attach requests will be stored both in NAS_ALARM_NOTE and PRIV_NOTE_HISTORY.

 

1. Create a table to hold the historic (NAS_ALARM_NOTE) information, lets call it PRIV_NOTE_HISTORY

    CREATE TABLE PRIV_NOTE_HISTORY ('nimid' text NOT NULL , 'note_id' int NOT NULL , 'time' datetime NOT NULL )

 

2. Create a trigger to act on 'attach note', lets call it PRIV_

    CREATE TRIGGER PRIV_TRG_NOTE_ATTACH AFTER INSERT ON NAS_ALARM_NOTE
    BEGIN
       INSERT INTO PRIV_NOTE_HISTORY  ( nimid, note_id, time) VALUES (new.nimid, new.note_id,new.time);
    END

 

 

--
-- Create table and trigger to store alarm notes historically
--
database.open ("database.db")

database.query("CREATE TABLE   PRIV_NOTE_HISTORY ('nimid' text NOT NULL , 'note_id' int NOT NULL , 'time' datetime NOT NULL )")
database.query("CREATE TRIGGER PRIV_TRG_NOTE_ATTACH AFTER INSERT ON NAS_ALARM_NOTE BEGIN INSERT INTO PRIV_NOTE_HISTORY  ( nimid, note_id, time) VALUES (new.nimid, new.note_id,new.time);END")

database.close()

 

 

Hope this helps,

Carstein

 

[Pedrob]

Error when execute

 

CREATE TRIGGER PRIV_TRG_NOTE_ATTACH AFTER INSERT ON NAS_ALARM_NOTE
    BEGIN
       INSERT INTO PRIV_NOTE_HISTORY  ( nimid, note_id, time) VALUES (new.nimid, new.note_id,new.time);
    END

 

 

output:

Msg 102, Level 15, State 1, Procedure PRIV_TRG_NOTE_ATTACH, Line 3
Incorrect syntax near 'AFTER'.

[Pedrob]

I try this and run correctly

 

CREATE TABLE PRIV_NOTE_HISTORY ('nimid' text NOT NULL , 'note_id' int NOT NULL , 'time' datetime NOT NULL )

 

CREATE TRIGGER PRIV_TRG_NOTE_ATTACH ON NAS_ALARM_NOTE AFTER INSERT  AS
BEGIN
       INSERT INTO PRIV_NOTE_HISTORY  ( nimid, note_id, time)
       select nimid,note_id, time from inserted      
END