需求:t_key_areas_info发生变化时,更新t_building_info_manage表相关字段。
1、新建function
create or replace function t_key_areas_info_trigger_func() returns trigger as $$ declare BEGIN IF( TG_OP = 'INSERT' ) THEN RAISE NOTICE 'this is a raise demo, INSERT ------------------------- '; RAISE NOTICE 'this is a raise demo, int_id is % ,new belong_building is % ',new.int_id,new.belong_building; UPDATE city_life_line.t_building_info_manage T SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 ), T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ), T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ), T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ), T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND status = '1' ) WHERE T.int_id = NEW.belong_building; elsif ( TG_OP = 'UPDATE' ) THEN RAISE NOTICE 'this is a raise demo, UPDATE------------------- '; RAISE NOTICE 'this is a raise demo, int_id is % ,new belong_building is % ,old belong_building is %',new.int_id,new.belong_building,old.belong_building; UPDATE city_life_line.t_building_info_manage T SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 ), T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ), T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ), T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ), T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND status = '1' ) WHERE T.int_id = NEW.belong_building; UPDATE city_life_line.t_building_info_manage T SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 ), T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ), T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ), T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ), T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND status = '1' ) WHERE T.int_id = OLD.belong_building; elsif ( TG_OP = 'DELETE' ) THEN RAISE NOTICE 'this is a raise demo, DELETE------------------- '; RAISE NOTICE 'this is a raise demo, int_id is % ,belong_building is %',OLD.int_id,OLD.belong_building; UPDATE city_life_line.t_building_info_manage T SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 ), T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ), T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ), T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ), T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND status = '1' ) WHERE T.int_id = OLD.belong_building; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;
2、新建触发器
CREATE TRIGGER trigger_t_key_areas_info after INSERT OR DELETE OR UPDATE ON city_life_line.t_key_areas_info FOR EACH ROW EXECUTE PROCEDURE city_life_line.t_key_areas_info_trigger_func();
3、查询触发器
SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname = 'trigger_t_key_areas_info';
4、删除触发器
DROP TRIGGER trigger_t_key_areas_info ON city_life_line.t_key_areas_info CASCADE;
标签:building,info,触发器,belong,life,实例,key,highgo,areas From: https://www.cnblogs.com/nuomm/p/18386084