Postgresql添加删除触发器示例

概述-- 定义触发器CREATE TRIGGER "vss_after_insert_flow_201702" AFTER INSERT ON "public"."vss_passenger_flow_201702"FOR EACH ROWEXECUTE PROCEDURE "vss_after_insert_flow"(); --删除触发器DROP TRIGGER vss_after

-- 定义触发器CREATE TRIGGER "vss_after_insert_flow_201702" AFTER INSERT ON "public"."vss_passenger_flow_201702"FOR EACH ROWEXECUTE PROCEDURE "vss_after_insert_flow"();

--删除触发器DROP TRIGGER vss_after_insert_flow_201702 ON vss_passenger_flow_201702 ;

--定义存储过程-- FUNCTION: public.vss_after_insert_flow()-- DROP FUNCTION public.vss_after_insert_flow();CREATE FUNCTION public.vss_after_insert_flow()    RETURNS trigger    LANGUAGE ‘plpgsql‘    COST 100.0    VolATILE NOT LEAKPROOF AS $BODY$BEGIN    --Routine body goes here...    IF EXISTS(SELECT camera_ID FROM vss_passenger_hour         WHERE camera_ID = NEW.i_camera_ID and datetime = NEW.start_dt::DATE and EXTRACT(hour FROM time) = EXTRACT(hour FROM NEW.start_dt::TIME)) THEN    UPDATE vss_passenger_hour         SET passenger_in_count = passenger_in_count + NEW.i_enter_num,passenger_out_count = passenger_out_count + NEW.i_leave_num             WHERE camera_ID = NEW.i_camera_ID and datetime = NEW.start_dt::DATE and EXTRACT(hour FROM time) = EXTRACT(hour FROM NEW.start_dt::TIME);    ELSE    INSERT INTO vss_passenger_hour(region_ID,camera_ID,datetime,time,passenger_in_count,passenger_out_count)        VALUES (NEW.i_area_ID,NEW.i_camera_ID,NEW.start_dt,to_timestamp(to_char(NEW.start_dt,‘yyyy-mm-dd HH24‘)||‘:00:00‘,‘yyyy-mm-dd HH24:MI:SS‘),NEW.i_enter_num,NEW.i_leave_num);    END IF;    RETURN NulL;END$BODY$;ALTER FUNCTION public.vss_after_insert_flow()    OWNER TO postgres;

总结

以上是内存溢出为你收集整理的Postgresql添加/删除触发器示例全部内容，希望文章能够帮你解决Postgresql添加/删除触发器示例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。