postgreSQL trigger 触发器

应用场景

 

1）记录中的缺失信息不全，

2）更新 汽车移动的位置信息。

 

 

-- DROP TRIGGER local_routing_dev_user_trigger ON public.local_routing_dev_user;

create trigger local_routing_dev_user_trigger after
insert
    on
    public.local_routing_dev_user for each row execute procedure fucupdatelocal_routing_dev_user();

 

 

 

 

 

CREATE OR REPLACE FUNCTION public.fucupdatelocal_routing_dev_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
   BEGIN
     

UPDAte local_routing_dev_user as routing
set user_id = rduser.user_id , user_name = rduser.user_name
from routing_device_user_relation rduser
where rduser.user_code =routing.user_code
and
routing.createdon > (now() - INTERVAL '300 min') and routing.user_name is null;

UPDATE local_routing_dev_user  as rou
set  device_name=dev.device_name , device_id=dev.device_id, latitude=dev.latitude::float8, longitude= dev.longitude::float8,
 dev_distance=0
from d_device  dev     
where  rou.product_key = dev.produce_key and 
rou.device_key = dev.device_key 
and
rou.createdon > (now() - INTERVAL '300 min') and rou.device_name is null;

     --  INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$function$
;