Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iGNbu-0008Dp-Ag for pgsql-admin@arkaria.postgresql.org; Fri, 04 Oct 2019 13:27:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iGNbr-0001GQ-M1 for pgsql-admin@arkaria.postgresql.org; Fri, 04 Oct 2019 13:27:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iGNbr-0001GD-68 for pgsql-admin@lists.postgresql.org; Fri, 04 Oct 2019 13:27:03 +0000 Received: from sonic302-2.consmr.mail.bf2.yahoo.com ([74.6.135.41]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iGNbo-0007KW-5u for pgsql-admin@postgresql.org; Fri, 04 Oct 2019 13:27:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1570195617; bh=fWwbFg57/FBOatchWD8qXPjIS3+UR+UfoB2m/cqiqR8=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=kOzw5Tel1+OxIt25U+X8PsyXBf1PogtxGLBd0cmX4JaWfvw0FvJGhxUdLWsNdJjBy+6Bkhv9ioy1o41J3d3B5g7p35r9gZZwnrxXPel6R5AnYQh4fGvzouRAhkNJ5HkmyjdRbQq7m/P+yoL6WX1hjkOI0Dx/LuBEO0O+UiPR0F8XMtbEaMvDuPEHBOa9fUSu1EpNOgJcbTBp2AFxzg5GmKrgoKO3on1qGylZZ45Ol058rPNAjeg0dEkiSPjU62Et6jGObo/Lf9iOYo2ekRQE2R07dWf0bp7zzRr2OEiY3V1Afz7eQO7q6fxFn7yDtS4h0xjxK7tW3I9CGfYMeqCyyA== X-YMail-OSG: L9ospEIVM1lEiXaKo55faTrmeVQ7_PAWhQjePbA6u9xryXzwwyGVijynp6fWACy CB3d3FiqNecR5Z.1fy6CQBdTaMZgQam.gvGwHqSMedFsnGDy04SY.gIf7DRcoWkAwY0sKDGbjp7i MzXyMJpcAZOfvcM5luHu8BOsAwP_kUwtjeLA7faaD2Dk8yHUtwTvtW0CihUcj0QQoJXLwanj6AYt Vfp0mdZk16DT3ljjfmoZuEEWyocyG7hbeLwvnoRxYQL5t4l7MlM7137Lm8_Q_BnHhSIIfpi8wSC2 y6C_frjtfZU0jygFnDK6nUlj5GmXdBMwpIXCUEWvnT3VEi4yhmdEr4rgz_qF_vqLo9v4AP_.pYtH 7Yid0PAXWsh3.GpUTsX_Ak3JzNLpsveZSJtaAOMQf9kMG7IDbVYnJ4WRGJD3Jk7CgQZrPZEodbAf YalRXC2hdujFEC6oC1VFRPHsHZWkRPayG6DGXAK3p2BGDY9Va2zsIv8_4HXF0sXzDiixjs0B6AmU DbDJnKbE8v5Ilxz3K_cCUT73V6qOjg9pN73Lhl.Aj2GQgUxo4vT7cALN8P5K_K7d18QI0YSAJuH_ 26PwlHkbnMa3Gl244E29EBAiGz_vTXde4kbnz5GzMDbWg2DMWUuIjsry9IzTHdgFYMc77Hq1IPRo Ck_uzqeJxywbHmMaaukRHnrKJDfX7g9tgZJFGmpFpiSZ97FNVIrg392qGe2hRp.9qIpdnEIxnsD. B4WhbTPP7YeyZ9lU1fsqQ2ga72_AUjHGq7W8MyvIl9mb0FKCzSKT7wLB15hPUX2rJpu9P_GBug88 sRvG.vYi19a56EVAVGJ41ion85HcdHvW0pmPCqHdmaY3NArcsNtKFCYM4uhmXriyXVukJNGnCNmX ukvFE11wFUpDr2mbOmuduQ2bSbe2EDYlsgT0Q3_phAj7Ld1egQRZIWV9HZZsSRPoeGeWQNj2OEhf O1oqcG.BV3x5Vv1kI_oync8g.mEHrY4.1yv0grVR0liuTQKIWW5VK1Kkz9EHHv4PF.yYC_dh_cYL 4pBsbBNN_3zaiNLW71mQfrZlgTZuF2IdirHFnYjdxk3RhMqRCagF1egDuFzINc39TbV03RKQipaI 5yJ5_3VhhaUdLlgb7AntAap5j6lIlCwZKH8JXxVKuHEFSAAVWNHgFoNvetMK1Kza1.njWbLGysSA uIq8Trx38m1fBgqTOZJw9tLHeBIdvNN6I1JaZx8nSZOGKwg11olFG6cM.TQM5zsIR9MTKClm3A.4 DII8icl6qO.UqWX4fKGYSihhT8MMnVMU2ke9aKjwHPrc._Fcl6s38n2jZbhkkqTcQRZ46ctaxYqu KHkUvgkXjQrSCX7UbDou3FsQ- Received: from sonic.gate.mail.ne1.yahoo.com by sonic302.consmr.mail.bf2.yahoo.com with HTTP; Fri, 4 Oct 2019 13:26:57 +0000 Date: Fri, 4 Oct 2019 13:26:52 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin , Message-ID: <744670167.2325583.1570195612720@mail.yahoo.com> Subject: Automatically updating a new information column in PostgreSQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2325582_1590264360.1570195612717" References: <744670167.2325583.1570195612720.ref@mail.yahoo.com> X-Mailer: WebService/1.1.14448 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.90 Safari/537.36 Content-Length: 19435 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2325582_1590264360.1570195612717 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable good morning experts,I have a trigger before insert (even with or update) a= nd seem it doesnt' work.=C2=A0 The function simply sets both columns named = mig_filename to "unknown if its null, and mig_insert_dt to current timestam= ple for each row passed to the trigger. here is my script even I take all the rest out and just simple function wit= h new.mig_insert_dt :=3D localtimestamp; CREATE OR REPLACE FUNCTION "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_= INS"()=C2=A0=C2=A0RETURNS trigger=C2=A0AS $$ declare v_ErrorCode int; v_ErrorMsg varchar(512); v_Module varchar(32) :=3D 'TR_STG_APPLICATION_CDIM_INS'; begin ---- -- If this is an INSERT operation---- if TG_OP =3D 'INSERT' then =C2=A0 =C2=A0---- =C2=A0 =C2=A0-- This just ensures that the filename is not null =C2=A0 =C2=A0---- =C2=A0 =C2=A0if new.mig_filename IS NULL then =C2=A0 =C2=A0 =C2=A0 new.mig_filename :=3D 'Unknown'; =C2=A0 =C2=A0end if; =C2=A0 =C2=A0new.mig_insert_dt =3D current_timestamp; end if; ---- -- Exception error handler ---- =C2=A0 =C2=A0exception =C2=A0 =C2=A0 =C2=A0 when others then =C2=A0 =C2=A0 =C2=A0 v_ErrorCode :=3D SQLSTATE; =C2=A0 =C2=A0 =C2=A0 v_ErrorMsg :=3D SQLERRM; =C2=A0 =C2=A0 =C2=A0 insert into "ECISDRDM"."ERRORLOG"( "TSTAMP", "OS_USER"= , "HOST", "MODULE", "ERRORCODE",=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0"ERRORMSG") =C2=A0 =C2=A0 =C2=A0 values (CURRENT_TIMESTAMP, CURRENT_USER, inet_server_a= ddr(), v_Module, v_ErrorCode, v_ErrorMsg); RETURN NEW; end; $$ language 'plpgsql'; CREATE TRIGGER "TR_STG_APPLICATION_CDIM_INS" BEFORE INSERT OR UPDATE ON "EC= ISDRDM"."STG_APPLICATION_CDIM" FOR EACH ROW EXECUTE PROCEDURE "ECISDRDM"."T= RIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"() ; I even " RAISE EXCEPTION 'UNKNOWN'; " and for the mig_insert_dt, I put eith= er '=3D' or ':=3D' Now(), now(), localtimestamp, timestamp, and none of the= m would fill the time. Both mig.filename and mig_insert_dt are still blank.= " if new.mig_filename IS NULL then=C2=A0=C2=A0 =C2=A0 RAISE EXCEPTION 'UNKNOW= N';=C2=A0 =C2=A0 new.mig_filename :=3D 'Unknown';end if;new.mig_insert_dt '= =3D now();=C2=A0 =C2=A0 According to the postgres example 39-3 "shows an example of trigger procedu= re in PL/pgSQL", I don't see any different with the example and don't know = what I have missed here.=C2=A0 Would you please advise what I did wrong her= e?=C2=A0=C2=A0 thank you, Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir 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 t= o 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 ------=_Part_2325582_1590264360.1570195612717 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
good morning experts,
I have a trigger before insert = (even with or update) and seem it doesnt' work.  The function simply s= ets both columns named mig_filename to "unknown if its null, and mig_insert= _dt to current timestample for each row passed to the trigger.

here is my script even I take all the rest out and just simple function = with new.mig_insert_dt :=3D localtimestamp;



CREATE OR REPLACE FUNCTION "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_C= DIM_INS"()  RETURNS trigger AS $$

declare

v_ErrorCod= e int;
v_ErrorMsg varchar(512);
v_Module varchar(32) :=3D 'TR_STG_APPLICATION_CDIM_IN= S';

begin

----

----

if TG= _OP =3D 'INSERT' then

  &= nbsp;----
   -- This just ensures th= at the filename is not null
  &nbs= p;----

   if = new.mig_filename IS NULL then
      new.mig_filename :=3D= 'Unknown';
   end if;

   new.mig_insert_dt =3D current_timestamp;

end if;

----
-- Exception error handler
----

   exception
      when ot= hers then

     = v_ErrorCode :=3D SQLSTATE;
      v_ErrorMsg :=3D SQLERRM= ;

      insert = into "ECISDRDM"."ERRORLOG"( "TSTAMP", "OS_USER", "HOST", "MODULE", "ERRORCO= DE",               "ERRORMSG")
&= nbsp;     values (CURRENT_TIMESTAMP, CURRENT_USER, inet_server_ad= dr(), v_Module, v_ErrorCode, v_ErrorMsg);

RETURN NEW;
end;
$$
language 'plpgsql';

CREATE TRIGGER "TR_STG_APPLICATION_CDIM_INS" BE= FORE INSERT OR UPDATE ON "ECISDRDM"."STG_APPLICATION_CDIM" FOR EACH ROW EXE= CUTE PROCEDURE "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"() ;

=
I even " RAISE EXCEPTION 'UNKNOWN'; " and for the mig_insert_dt,= I put either '=3D' or ':=3D' Now(), now(), localtimestamp, timestamp, and = none of them would fill the time. Both mig.filename and mig_insert_dt are s= till blank.
"
if new.mig_filename IS NULL then 
    RAISE EXCEPTION 'UNKNOWN';
    new.mig_filename :=3D 'Unknown';
end if;
new.mig_insert_dt '=3D no= w();   

According to the postgres example 39-3= "shows an example of trigger procedure in PL/pgSQL", I don't see any diffe= rent with the example and don't know what I have missed here.  W= ould you please advise what I did wrong here? =  

thank you,

= 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.
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 appea= rances. But to those who knew him well, he was a perfect gentleman (Hermion= e Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
=
*** Genuinely rich *** Faithful talent *** Sharing succ= ess
------=_Part_2325582_1590264360.1570195612717--