programs_info/无锡市生命线项目/刘琳琳/创建插入前触发器语句.txt

-- CREATE TABLE my_table (
--     id INT,
--     data TEXT
-- );
--  
CREATE OR REPLACE FUNCTION set_timely_status_before_insert() RETURNS TRIGGER AS $$
BEGIN
    if(NEW.scene='0') then
		   if(NEW.report_time is not null AND NEW.disposal_time is not null) then
			   if(EXTRACT(EPOCH FROM (CAST(NEW.disposal_time AS timestamp) - CAST(NEW.report_time AS timestamp))) / 3600<=2) then
					NEW.timely_status='1';
				  return NEW;
					else
					NEW.timely_status='0';
					return NEW;
					END if;
				END if;
			END if;
			
if(NEW.scene='1' or NEW.scene='5') then
		   if(NEW.report_time is not null AND NEW.disposal_time is not null) then
			   if(EXTRACT(EPOCH FROM (CAST(NEW.disposal_time AS timestamp) - CAST(NEW.report_time AS timestamp))) / 3600<=12) then
					NEW.timely_status='1';
				  return NEW;
					else
					NEW.timely_status='0';
					return NEW;
					END if;
				END if;
			END if;
			
if(NEW.scene='2' ) then
		   if(NEW.report_time is not null AND NEW.disposal_time is not null) then
			   if(EXTRACT(EPOCH FROM (CAST(NEW.disposal_time AS timestamp) - CAST(NEW.report_time AS timestamp))) / 3600<=4) then
					NEW.timely_status='1';
				  return NEW;
					else
					NEW.timely_status='0';
					return NEW;
					END if;
				END if;
			END if;			
			
			if(NEW.scene='3' ) then
		   if(NEW.report_time is not null AND NEW.disposal_time is not null) then
			   if(EXTRACT(EPOCH FROM (CAST(NEW.disposal_time AS timestamp) - CAST(NEW.report_time AS timestamp))) / 3600<=1) then
					NEW.timely_status='1';
				  return NEW;
					else
					NEW.timely_status='0';
					return NEW;
					END if;
				END if;
			END if;			

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER before_insert_set_timely_status
BEFORE INSERT ON warning_info
FOR EACH ROW EXECUTE FUNCTION set_timely_status_before_insert();


-- INSERT INTO "public"."warning_info" ("id", "scene", "warning_name", "warning_source", "equipment_name", "equipment_number", "warning_type", "warning_site", "area", "warning_rank", "generated_time", "report_time", "warning_describe", "report", "analyses", "disposal_state", "disposal_user_name", "disposal_user_id", "disposal_time", "disposal_suggest", "disposal_complete_time", "disposal_report", "remark", "geom_data", "defined", "deleted", "created_at", "created_by", "updated_at", "updated_by", "analyses_report", "warning_state", "streaming_url", "table_name", "threshold_value", "warning_number") VALUES ('c', '1', '可燃气体探测器报警', '1', '可燃气体探测器', 'R16lkjdi', NULL, '120.480965,31.595133', '320205', '3', '2024-03-21 19:40:30', '2023-09-11 07:30:40', NULL, NULL, '已完成', '3', NULL, NULL, '2024-03-21 19:41:05', '{"action":"接单","details":{"企业接单，接单人员":"华润值班"},"operator":"无锡华润燃气有限公司","stepType":"13","timestamp":"2024-03-21 19:41:05"}', '2024-05-22 17:52:19', NULL, NULL, NULL, NULL, 1, '2024-10-31 10:30:37.902', '-1', NULL, NULL, NULL, 2, NULL, 'rq_jc_jcbj', NULL, 'BJ2403210003');