Hello,
I am still getting ERROR: invalid input syntax for integer:"42P01" when I am insert a data into the table.
CONTEXT: PL/pgSQL function cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins() line 29 at assignment.
Insert query:
insert into cidr_staging.stg_adjudicative_status (receipt_number, adjudicative_action_date, adjudicative_time_stamp, adjudicative_status, mig_filename, mig_insert_dt, mig_modified_dt, mig_seq) values (E'LIN1890030316', E'20180409',E'0752',E'Dennied',E'c3_20180626_adjudicativestatus_000.txt',NULL,NULL,172011);
the trigger is:
Create or replace function cidr_staging.trigger_fct_tr_stg_adjudicative_status_in() RETURNS trigger AS $BODY$
declare
v_seq bigint:=0;
v_ErrorCode bigint;
V_ErrorMsg varchar(512)'
v_Module varchar(32):= 'TR_STG_ADJUDICATIVE_STATUS_INS';
BEGIN
BEGIN
select nextval('sq_staging') into STRICT v_seq;
if NEW.mig_seg is null then
NEW.mig_seq:=v_seq;
enf if;
if NEW.mig_filename is null then
NEW.mig_filename :='Unknown';
end if;
exception
when others then
v_ErrorCode := SQLSTATE'
v_ErrorMsg := SQLERRM;
insert into cidrmgmt.errorlog(stamp, os_user, host, module, errorcode, erromsg) values (CURRENT_TIMESTAMP, sys_context('userenv','session_user'), sys_context('userenv','host'),v_Module, v_ErrorCode, v_ErrorMsg);
END;
RETURN NEW;
end
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER tr_stg_adjudicative_status_ins BEFORE INSERT ON cidr_staging.stg_adjudicative_status FOR EACH ROW
EXECUTE PROCEDURE cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins();
the table definition is:
create table cidr_staging.stg_adjudicative_status (
receipt_number character varying (13) not null,
adjudicative_action_date character varying(8),
adjudicative_time_stamp character varying(4),
adjudicative_status character varying(50,
mig_file_name character varying(80) not null,
mig_insert_dt timestamp without time zone,
mig_modified_dt timestamp without time zone,
mig_seg bigint not null);
Triggers:"tr_stg_adjudicative_status_ins" before INSERT ON cidr_staging.stg_adjudicative_status FOR EACH ROW EXECUTIVE PROCEDURE cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins()
even I take the "E" values out the error is still occurred but if I dropped the function and trigger, the insert is fine
thank you so much for looking into this error.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
