CREATE TABLE equipment_ids
(
  id integer NOT NULL DEFAULT nextval('equipment_id_seq'::regclass),
  address character varying(50),
  CONSTRAINT equipment_pkey PRIMARY KEY (id)
)

CREATE TABLE snmp_ports
(
  id serial NOT NULL,
  equipment integer NOT NULL,
  oper integer NOT NULL DEFAULT 2,
  admin integer NOT NULL DEFAULT 2,
  last_up timestamp without time zone DEFAULT now(),
  last_down timestamp without time zone DEFAULT now(),
  flap_times integer DEFAULT 0,
  CONSTRAINT snmp_ports_pkey PRIMARY KEY (id),
  CONSTRAINT snmp_ports_equipment_fkey FOREIGN KEY (equipment)
      REFERENCES equipment_ids (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

CREATE TRIGGER update_snmp_oper
  AFTER UPDATE OF oper
  ON snmp_ports
  FOR EACH ROW
  EXECUTE PROCEDURE snmp_oper_update();


CREATE OR REPLACE FUNCTION snmp_oper_update()
  RETURNS trigger AS
$BODY$
BEGIN
		IF OLD.oper=2 AND NEW.oper=1 THEN
			UPDATE snmp_ports SET flap_times=flap_times+1, last_up=now() WHERE id = OLD.id;
		END IF;
		IF OLD.oper=1 AND NEW.oper=2 THEN
			UPDATE snmp_ports SET flap_times=flap_times+1, last_down=now() WHERE id = OLD.id;
		END IF;
	RETURN NEW;
END

2015-10-16 08:56:31 YEKT LOG:  statement: begin
2015-10-16 08:56:31 YEKT LOG:  statement: LOCK TABLE equipment_ids IN ACCESS EXCLUSIVE MODE;
2015-10-16 08:56:31 YEKT LOG:  statement: 
	            UPDATE snmp_ports SET oper = 2, admin = 1
	            WHERE id = 152531
	        
2015-10-16 08:56:31 YEKT LOG:  statement: commit

UPDATE  snmp_ports SET
              oper = %(oper)s,
              admin = %(admin)s,
              flap_times = CASE WHEN oper != %(oper)s THEN flap_times + 1 ELSE flap_times END,
              last_down = CASE WHEN oper = 1 and 2=%(oper)s THEN now() ELSE last_down END,
              last_up = CASE WHEN oper = 2 and 1=%(oper)s THEN now() ELSE last_down END
              WHERE id=%(port_id)s