Postgresql添加/刪除觸發器樣本

標籤：儲存   weight   datetime   roc   預存程序   return   time   else   new   

-- 定義觸發器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添加/刪除觸發器樣本