root/sql/sprocs/noit.check_status_archive_log_changes.sql

Revision f2f2353e4dd7ecf5cd5016d921a77166c57a235b, 1.1 kB (checked in by Denish Patel <denish@omniti.com>, 5 years ago)

Trigger function noit.check_status_archive_log_changes

  • Property mode set to 100644
Line 
1 -- formerly  trigger function stratcon.loading_dock_status_s_change_log
2
3 CREATE OR REPLACE FUNCTION noit.check_status_archive_log_changes()
4   RETURNS trigger AS
5 $BODY$
6 DECLARE
7     v_state CHAR(1);
8     v_avail CHAR(1);
9     v_whence timestamp with time zone;
10 BEGIN
11
12 IF TG_OP = 'INSERT' THEN
13     SELECT state,availability,whence FROM  noit.check_status_changelog WHERE sid = NEW.sid
14         AND WHENCE = (SELECT max(whence) FROM noit.check_status_changelog
15                         WHERE  SID=NEW.sid and  WHENCE <> NEW.whence )
16     INTO v_state,v_avail,v_whence;
17
18     IF NEW.whence > v_whence AND
19        (v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability) THEN
20         INSERT INTO noit.check_status_changelog (sid,whence,state,availability,duration,status)
21             VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status);
22     END IF;
23
24 ELSE
25         RAISE EXCEPTION 'Error in trigger function noit.check_status_archive_log_changes()';
26
27 END IF;
28
29     RETURN NULL;
30 END
31 $BODY$
32   LANGUAGE 'plpgsql'  SECURITY DEFINER;
33  
34 GRANT EXECUTE ON FUNCTION noit.check_status_archive_log_changes() TO stratcon;
Note: See TracBrowser for help on using the browser.