谁能帮我把这个oracle触发器改成postgreSQL的谢谢

2010-04-21 09:42:24 118 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Oracle中sequence的创建和sequence的触发器的创建首先查看自己用户是否有增加sequence的权限如果没有的话添加权限 conn sys/password as sysdba grant create sequence to 用户;Orcale添加sequence:CREATE sequence ZhouMM –创建的sequence的名字 minvalue 1 –最小值从几开始 maxvalue 999999999 –最阅读详情

原oracle触发器脚本



create or replace trigger TIB_T_RACKTEMPHUM_UPDATED

  after insert on t_racktemphum  

  for each row

declare

       v_count int;

  -- local variables here

begin

  select count(*) into v_count from T_RACKTEMPHUM_UPDATED where sensorid=:new.sensorid;

  if v_count>0 then

  update T_RACKTEMPHUM_UPDATED set temp=:new.temp,humid=:new.humid,collecttime=:new.collecttime

  where sensorid=:new.sensorid;

  else

  insert into T_RACKTEMPHUM_UPDATED values (:new.sensorid,:new.temp,:new.humid,:new.collecttime);

  end if;

end TIB_T_RACKTEMPHUM_UPDATED;

建表语句





CREATE TABLE "public"."t_racktemphum" (

"id" numeric DEFAULT NULL NOT NULL,

"sensorid" numeric(10) DEFAULT NULL::numeric,

"temp" numeric(8,2) DEFAULT NULL::numeric,

"humid" numeric(8,2) DEFAULT NULL::numeric,

"collecttime" timestamp(6) DEFAULT NULL::timestamp without time zone,

CONSTRAINT "t_racktemphum_pkey" PRIMARY KEY ("id"),

CONSTRAINT "t_racktemphum_id_check" CHECK (id IS NOT NULL),

CONSTRAINT "t_racktemphum_id_check1" CHECK (id IS NOT NULL)

)

WITH (OIDS=FALSE)

;



ALTER TABLE "public"."t_racktemphum" OWNER TO "postgres";



COMMENT ON TABLE "public"."t_racktemphum" IS '机柜温湿度采集表';



COMMENT ON COLUMN "public"."t_racktemphum"."id" IS '流水号';



COMMENT ON COLUMN "public"."t_racktemphum"."sensorid" IS '传感器编号';



COMMENT ON COLUMN "public"."t_racktemphum"."temp" IS '温度';



COMMENT ON COLUMN "public"."t_racktemphum"."humid" IS '湿度';



COMMENT ON COLUMN "public"."t_racktemphum"."collecttime" IS '采集时间';;