pgsql触发器

pj_custom_audit_images表里的数据因为程序问题会被全部删除，有了这个触发器会在pj_custom_audit_images插入数据时同时插入pj_custom_audit_images_backup备份表里，即使主表数据被删除，备份表里还有一模一样的数据，可以重新导入到主表应用，或者也可以在这个备份表里查询。

-- 注释：新建B表（备份表，和A表（pj_custom_audit_images）结构表相同）
create table pj_custom_audit_images_backup(
 "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "eval_id" varchar(64) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "loss_no" varchar(60) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "act_flag" varchar(2) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "del_flag" varchar(2) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "upload_images_name" varchar(200) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "real_images_name" varchar(200) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "created_date" timestamp(6) DEFAULT NULL::timestamp without time zone
)
;
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."id" IS '主键 ';
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."eval_id" IS '定损单主键 ';
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."loss_no" IS '定损单号';
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."act_flag" IS '0换件   1整单';
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."del_flag" IS '删除标记';
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."upload_images_name" IS '上传图片名称';
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."real_images_name" IS '真实图片名称';
COMMENT ON COLUMN "sip_cloud_business"."pj_custom_audit_images"."created_date" IS '创建时间';

-- ----------------------------
-- Indexes structure for table pj_custom_audit_images
-- ----------------------------
CREATE UNIQUE INDEX "pj_custom_audit_images_pkey" ON "sip_cloud_business"."pj_custom_audit_images" USING btree (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- 注释：创建触发器
create or replace function auditlogfunc() returns trigger as $example_table$
begin
insert into pj_custom_audit_images_backup(id,eval_id,loss_no,act_flag,del_flag,upload_images_name,real_images_name,created_date) values (new.id,new.eval_id,new.loss_no,new.act_flag,new.del_flag,new.upload_images_name,new.real_images_name,current_timestamp);
return new;
end;
$example_table$ language plpgsql;


-- 注释：绑定触发器
create trigger example_trigger after insert on pj_custom_audit_images for each row execute procedure auditlogfunc();

-- 注释：插入语句
INSERT INTO pj_custom_audit_images (id,eval_id,loss_no,act_flag,del_flag,upload_images_name,real_images_name,created_date) VALUES ('140955666','140955777','D130020209211600655709419','0','1','1.jpg','171b6bf987ed480c87286a3db60b9f02.jpg',current_timestamp);


-- 删除触发器
-- drop trigger example_trigger on pj_custom_audit_images;