highgo数据库加触发器实例-记

需求：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;