Changeset 69db8eb9c49a9d2c4427e7001aa0baa803248de8

Show
Ignore:
Timestamp:
05/09/08 21:25:50 (11 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1210368350 +0000
git-parent:

[9c824156bbb942b0da737b077c77025f0b56c67d]

git-author:
Denish Patel <denish@omniti.com> 1210368350 +0000
Message:

Created trigger on stratcon.loading_dock_status_s
to log the entry if STATE or AVAILABILTY status changes.
Long table :stratcon.loading_dock_status_s_change_log

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/schema.sql

    r9c82415 r69db8eb  
    1515 
    1616CREATE TABLE stratcon.loading_dock_status_s ( 
     17    sid integer NOT NULL, 
     18    whence timestamp NOT NULL, 
     19    state character(1) NOT NULL, 
     20    availability character(1) NOT NULL, 
     21    duration integer NOT NULL, 
     22    status text, 
     23    PRIMARY KEY(sid,whence) 
     24); 
     25 
     26CREATE TABLE stratcon.loading_dock_status_s_change_log ( 
    1727    sid integer NOT NULL, 
    1828    whence timestamp NOT NULL, 
     
    137147 
    138148 GRANT SELECT,INSERT ON stratcon.loading_dock_status_s TO stratcon; 
    139  GRANT SELECT,INSERT ON stratcon.loading_dock_check  TO stratcon; 
    140  GRANT SELECT,INSERT ON stratcon.loading_dock_status TO stratcon; 
    141  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric TO stratcon; 
    142  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text TO stratcon; 
     149 GRANT SELECT,INSERT ON stratcon.loading_dock_status_s_change_log TO stratcon; 
    143150 GRANT SELECT,INSERT ON stratcon.loading_dock_check_s TO stratcon; 
    144151 GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon; 
     
    184191$$ LANGUAGE plpgsql; 
    185192 
    186 -- Trigger Function to change Metrix Text Changes  
     193-- Trigger Function to log dock status Changes  
     194 
     195CREATE  TRIGGER loading_dock_status_s_change_log 
     196    AFTER INSERT ON stratcon.loading_dock_status_s 
     197    FOR EACH ROW 
     198    EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log(); 
     199 
     200 
     201CREATE OR REPLACE FUNCTION stratcon.loading_dock_status_s_change_log() RETURNS trigger 
     202    AS $$ 
     203DECLARE 
     204    v_state CHAR(1); 
     205    v_avail CHAR(1); 
     206BEGIN 
     207 
     208IF TG_OP = 'INSERT' THEN 
     209    SELECT state,availability FROM  stratcon.loading_dock_status_s WHERE sid = NEW.sid  
     210        AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log  
     211                        WHERE  SID=NEW.sid and  WHENCE <> NEW.whence ) 
     212    INTO v_state,v_avail; 
     213 
     214    IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN 
     215 
     216        INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status) 
     217            VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status);  
     218 
     219    END IF; 
     220 
     221ELSE 
     222        RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log'; 
     223END IF; 
     224 
     225    RETURN NULL; 
     226 
     227END 
     228$$ 
     229    LANGUAGE plpgsql; 
     230 
     231 
     232-- Trigger Function to log Metrix Text Changes  
    187233 
    188234CREATE TRIGGER loading_dock_metric_text_s_change_log 
    189     AFTER INSERT ON loading_dock_metric_text_s 
     235    AFTER INSERT ON stratcon.loading_dock_metric_text_s 
    190236    FOR EACH ROW 
    191     EXECUTE PROCEDURE loading_dock_metric_text_s_change_log(); 
    192  
    193 CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger 
     237    EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log(); 
     238 
     239 
     240CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger 
    194241    AS $$ 
    195242DECLARE 
     
    211258 
    212259ELSE 
    213         RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; 
     260        RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log '; 
    214261END IF; 
    215262