postgres 트리거 만들기

CREATE FUNCTION update_json_expedia_region_union() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
    UPDATE expedia_region_union SET boundaries = ST_GeomFromGeoJSON(coordinates)::geometry, boundaries_nearby = ST_GeomFromGeoJSON(coordinates_nearby)::geometry WHERE region_id = NEW.region_id;
    UPDATE expedia_region_union SET add_continent = (
		select json_agg(json_build_object('id', region_id, 'name', region_name_kr, 'type', region_type, 'name_en', region_name)::jsonb) from expedia_region_continent where region_id in  (select unnest(add_continent_region_id) from expedia_region_union where region_id=NEW.region_id)
	)
	where region_id = NEW.region_id;
	UPDATE expedia_region_union SET jsonn = 	json_build_object('region_code', region_id, 'region_name', region_name, 'region_type', region_type, 'region_name_korean', region_name_kr, 'region_name_korean_full', region_name_full_kr)::jsonb
	|| json_build_object('region_level', region_level)::jsonb
	|| json_build_object('center_longitude', center_longitude, 'center_latitude', center_latitude)::jsonb
	|| json_build_object('continent', continent, 'country', country, 'parent_city_flag', parent_city_flag, 'parent_city', parent_city)::jsonb
	|| json_build_object('codes', codes)::jsonb
	|| json_build_object('boundaries', coordinates)::jsonb
	|| json_build_object('related_airport', airport, 'nearest_airport', nearest_airport, 'nearest_airport_country', nearest_airport_in_country)::jsonb
	|| json_build_object('ancestors', case when add_continent is not null then ancestors_info || add_continent else ancestors_info end)::jsonb
	|| json_build_object('city_home_flag', city_home_flag, 'use_yn_flag', use_yn_flag, 'source_from', source_from, 'source_time', source_time)::jsonb
	WHERE region_id = NEW.region_id;
    RETURN NEW;
END
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER update_json_expedia_region_union
AFTER UPDATE on expedia_region_union
FOR EACH ROW 
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE update_json_expedia_region_union();

 

 

CREATE FUNCTION update_json_expedia_region_union_copy_jpa_test() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
    UPDATE expedia_region_union_copy_jpa_test SET boundaries = ST_GeomFromGeoJSON(coordinates)::geometry, boundaries_nearby = ST_GeomFromGeoJSON(coordinates_nearby)::geometry WHERE region_id = NEW.region_id;
    UPDATE expedia_region_union_copy_jpa_test SET add_continent = (
		select json_agg(json_build_object('id', region_id, 'name', region_name_kr, 'type', region_type, 'name_en', region_name)::jsonb) from expedia_region_continent where region_id in  (select unnest(add_continent_region_id) from expedia_region_union_copy_jpa_test where region_id=NEW.region_id)
	)
	where region_id = NEW.region_id;
	UPDATE expedia_region_union_copy_jpa_test SET jsonn = 	json_build_object('region_code', region_id, 'region_name', region_name, 'region_type', region_type, 'region_name_korean', region_name_kr, 'region_name_korean_full', region_name_full_kr)::jsonb
	|| json_build_object('region_level', region_level)::jsonb
	|| json_build_object('center_longitude', center_longitude, 'center_latitude', center_latitude)::jsonb
	|| json_build_object('continent', continent, 'country', country, 'parent_city_flag', parent_city_flag, 'parent_city', parent_city)::jsonbnnn
	|| json_build_object('codes', codes)::jsonb
	|| json_build_object('boundaries', coordinates)::jsonb
	|| json_build_object('related_airport', airport, 'nearest_airport', nearest_airport, 'nearest_airport_country', nearest_airport_in_country)::jsonb
	|| json_build_object('ancestors', case when add_continent is not null then ancestors_info || add_continent else ancestors_info end)::jsonb
	|| json_build_object('city_home_flag', city_home_flag, 'use_yn_flag', use_yn_flag, 'source_from', source_from, 'source_time', source_time)::jsonb
	WHERE region_id = NEW.region_id;
    RETURN NEW;
END
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER expedia_region_union_copy_jpa_test_trigger
AFTER UPDATE on expedia_region_union_copy_jpa_test
FOR EACH ROW 
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE update_json_expedia_region_union_copy_jpa_test();

 

 

check / function and trigger

select pg_get_functiondef('update_json_expedia_region_union_copy_jpa_test'::regproc);

SELECT event_object_table
      ,trigger_name
      ,event_manipulation
      ,action_statement
      ,action_timing
FROM  information_schema.triggers
WHERE event_object_table = 'expedia_region_union_copy_jpa_test' -- Your table name comes here
ORDER BY event_object_table
     ,event_manipulation

 

트리거를 잘못 만들면 update이 안된다 ㅎㅎ 

 

drop trigger if exists expedia_region_union_copy_jpa_test_trigger on expedia_region_union_copy_jpa_test;