postgreSQL触发器

DROP TABLE IF EXISTS "public"."sales_opportunities_service_history";


DROP SEQUENCE IF EXISTS "public"."sales_opportunities_serv_history_id_seq";
CREATE SEQUENCE "public"."sales_opportunities_serv_history_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 12
 CACHE 1;


CREATE TABLE "public"."sales_opportunities_service_history" (
"id" int4 DEFAULT nextval('sales_opportunities_serv_history_id_seq'::regclass) NOT NULL,
"sales_opp_serv_id" int4,
"opportunity_id" int4,
"quotation_id" int4,
"service_id" int4,
"description_id" int4,
"insurance_id" int4,
"insurance_template_id" int4,
"typeId" int4,
"name" varchar(200) COLLATE "default",
"begintime" timestamp(6),
"endtime" timestamp(6),
"remarks" varchar(1000) COLLATE "default",
"ruletype" int4,
"description" varchar(1024) COLLATE "default",
"contract_type" int4,
"service_type" int4,
"service_type_content" varchar(512) COLLATE "default",
"insurance_description" varchar(10000) COLLATE "default",
"description_remarks" varchar(2555) COLLATE "default",
"insurance_remarks" varchar(2555) COLLATE "default",
"price_description" varchar(2555) COLLATE "default",
"createby" varchar(255) COLLATE "default",
"updateby" varchar(255) COLLATE "default",
"createtime" timestamp(6),
"updatetime" timestamp(6),
"status" int4,
"tenantcompany" varchar(255) COLLATE "default",
"serv_index" int4,
"show_insurance" bool,
"price_attachment" varchar(10000) COLLATE "default",
"insurance_attachment" varchar(10000) COLLATE "default",
"historycreatetime" timestamp(6),
"historystatus" int4,
"historyupdatetime" timestamp(6),
CONSTRAINT "sales_opportunities_service_history_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;


ALTER TABLE "public"."sales_opportunities_service_history" OWNER TO "dbuser";




DROP TRIGGER IF EXISTS "tg_sales_opportunities_service" ON "sales_opportunities_service" ;


CREATE TRIGGER "tg_sales_opportunities_service" AFTER INSERT OR UPDATE OF "id", "opportunity_id", "quotation_id", "service_id", "description_id", "insurance_id", "insurance_template_id", "typeId", "name", "begintime", "endtime", "remarks", "ruletype", "description", "contract_type", "service_type", "service_type_content", "insurance_description", "description_remarks", "insurance_remarks", "price_description", "createby", "updateby", "createtime", "updatetime", "status", "tenantcompany", "serv_index", "show_insurance", "price_attachment", "insurance_attachment" OR DELETE ON "public"."sales_opportunities_service"
FOR EACH ROW
EXECUTE PROCEDURE "public"."tg_fun_sales_opportunities_service"();






CREATE OR REPLACE FUNCTION "public"."tg_fun_sales_opportunities_service"()
  RETURNS "pg_catalog"."trigger" AS $BODY$BEGIN
IF(TG_OP = 'UPDATE') THEN
INSERT INTO "public"."sales_opportunities_service_history" ("sales_opp_serv_id", "opportunity_id", "quotation_id", "service_id", "description_id", "insurance_id", "insurance_template_id", "typeId", "name", "begintime", "endtime", "remarks", "ruletype", "description", "contract_type", "service_type", "service_type_content", "insurance_description", "description_remarks", "insurance_remarks", "price_description", "createby", "updateby", "createtime", "updatetime", "status", "tenantcompany", "serv_index", "show_insurance", "price_attachment", "insurance_attachment", "historycreatetime", "historystatus")
VALUES(NEW."id", NEW."opportunity_id", NEW."quotation_id",NEW."service_id", NEW."description_id", NEW."insurance_id", NEW."insurance_template_id", NEW."typeId", NEW."name", NEW."begintime", NEW."endtime", NEW."remarks", NEW."ruletype", NEW."description", NEW."contract_type", NEW."service_type", NEW."service_type_content", NEW."insurance_description", NEW."description_remarks", NEW."insurance_remarks", NEW."price_description", NEW."createby", NEW."updateby", NEW."createtime", NEW."updatetime", NEW."status", NEW."tenantcompany", NEW."serv_index", NEW."show_insurance", NEW."price_attachment", NEW."insurance_attachment", CURRENT_TIMESTAMP, '1');
RETURN NEW;
END IF;
IF(TG_OP = 'INSERT') THEN
INSERT INTO "public"."sales_opportunities_service_history" ("sales_opp_serv_id", "opportunity_id", "quotation_id", "service_id", "description_id", "insurance_id", "insurance_template_id", "typeId", "name", "begintime", "endtime", "remarks", "ruletype", "description", "contract_type", "service_type", "service_type_content", "insurance_description", "description_remarks", "insurance_remarks", "price_description", "createby", "updateby", "createtime", "updatetime", "status", "tenantcompany", "serv_index", "show_insurance", "price_attachment", "insurance_attachment", "historycreatetime", "historystatus")
VALUES(NEW."id", NEW."opportunity_id", NEW."quotation_id",NEW."service_id", NEW."description_id", NEW."insurance_id", NEW."insurance_template_id", NEW."typeId", NEW."name", NEW."begintime", NEW."endtime", NEW."remarks", NEW."ruletype", NEW."description", NEW."contract_type", NEW."service_type", NEW."service_type_content", NEW."insurance_description", NEW."description_remarks", NEW."insurance_remarks", NEW."price_description", NEW."createby", NEW."updateby", NEW."createtime", NEW."updatetime", NEW."status", NEW."tenantcompany", NEW."serv_index", NEW."show_insurance", NEW."price_attachment", NEW."insurance_attachment", CURRENT_TIMESTAMP, '1');
RETURN NEW;
END IF;
IF(TG_OP = 'DELETE') THEN
UPDATE "public"."sales_opportunities_service_history" SET "historyupdatetime" = CURRENT_TIMESTAMP, "historystatus" = 0
WHERE "sales_opp_serv_id" = OLD."id";
RETURN NEW;
END IF;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;