postgresql 表触发器

1、先建一个函数，用来执行触发器启动后要执行的脚本

CREATE OR REPLACE FUNCTION "public"."trigger_day_aqi"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN

   --日均值表，没有o3,小时值表，没有o3_8h
     
     NEW.so2iaqi=DAY_SO2_AQI(NEW.so2);
         NEW.no2iaqi=DAY_NO2_AQI(NEW.no2);
         NEW.coiaqi=DAY_CO_AQI(NEW.co);
         NEW.o3_8hiaqi=O3_8_AQI(NEW.o3_8h);
         NEW.pm10iaqi=PM10_AQI(NEW.pm10);
         NEW.pm25iaqi=PM25_AQI(NEW.pm25);

         NEW.aqi=NEW.coiaqi;
         NEW.primarypol='CO';
         
        IF NEW.aqi<NEW.no2iaqi   then   NEW.aqi=NEW.no2iaqi;    NEW.primarypol='NO2';    end if;
        IF NEW.aqi<NEW.so2iaqi   then   NEW.aqi=NEW.so2iaqi;    NEW.primarypol='SO2';    end if;
        IF NEW.aqi<NEW.o3_8hiaqi  then   NEW.aqi=NEW.o3_8hiaqi;   NEW.primarypol='O3_8H';  end if;
        IF NEW.aqi<NEW.pm10iaqi  then   NEW.aqi=NEW.pm10iaqi;   NEW.primarypol='PM10';   end if;
        IF NEW.aqi<NEW.pm25iaqi  then   NEW.aqi=NEW.pm25iaqi;   NEW.primarypol='PM2.5';  end if; 

        IF NEW.aqi<=50           then   NEW.primarypol='-';      end if;

         NEW.aqilevel=getRank(NEW.aqi);



    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

2、建表的触发器，

CREATE TRIGGER gk_site_day_insert BEFORE INSERT ON gk_site_day
    FOR EACH ROW EXECUTE PROCEDURE trigger_day_aqi();

 

转载于:https://www.cnblogs.com/tiandi/p/10781001.html