Automatically updating a new information column in PostgreSQL
Pepe TD Vo <[email protected]> Fri, 4 Oct 2019 13:26:52 +0000 (UTC)
| Newsgroups | gmane.comp.db.postgresql.admin,gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
------=_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 <html><head></head><body><div class=3D"ydp3b190d25yahoo-style-wrap" style= =3D"font-family:verdana, helvetica, sans-serif;font-size:16px;"><div dir=3D= "ltr" data-setdir=3D"false"><div><blockquote class=3D"ydpea5899c8_28lDeogZh= LGXvE95QRPeDL" style=3D"margin: 4px 0px 4px 8px; padding: 0px 0px 0px 8px; = border-top: 0px; border-right: 0px; border-bottom: 0px; border-left-width: = ; border-image: initial; font-stretch: inherit; font-size: 14px; line-heigh= t: inherit; font-family: Noto Sans, Arial, sans-serif; vertical-align: base= line; color: rgb(26, 26, 27);"><div style=3D"margin: 0px; padding: 0px 0px = 0.25em; border: 0px; font-style: inherit; font-weight: inherit; font-stretc= h: inherit; font-size: inherit; line-height: inherit; font-family: inherit;= vertical-align: baseline;" dir=3D"ltr" data-setdir=3D"false"><span><span s= tyle=3D"color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif; f= ont-size: 16px;">good morning experts,</span></span></div><div style=3D"mar= gin: 0px; padding: 0px 0px 0.25em; border: 0px; font-style: inherit; font-w= eight: inherit; font-stretch: inherit; font-size: inherit; line-height: inh= erit; font-family: inherit; vertical-align: baseline;" dir=3D"ltr" data-set= dir=3D"false"><span><span style=3D"color: rgb(0, 0, 0); font-family: verdan= a, helvetica, sans-serif; font-size: 16px;">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.</span></span= ></div><div style=3D"margin: 0px; padding: 0px 0px 0.25em; border: 0px; fon= t-style: inherit; font-weight: inherit; font-stretch: inherit; font-size: i= nherit; line-height: inherit; font-family: inherit; vertical-align: baselin= e;" dir=3D"ltr" data-setdir=3D"false"><span><span style=3D"color: rgb(0, 0,= 0); font-family: verdana, helvetica, sans-serif; font-size: 16px;"><br></s= pan></span></div><div style=3D"margin: 0px; padding: 0px 0px 0.25em; border= : 0px; font-style: inherit; font-weight: inherit; font-stretch: inherit; fo= nt-size: inherit; line-height: inherit; font-family: inherit; vertical-alig= n: baseline;" dir=3D"ltr" data-setdir=3D"false"><span><span style=3D"color:= rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif; font-size: 16px= ;">here is my script even I take all the rest out and just simple function = with new.mig_insert_dt :=3D localtimestamp;</span></span></div><div style= =3D"margin: 0px; padding: 0px 0px 0.25em; border: 0px; font-style: inherit;= font-weight: inherit; font-stretch: inherit; font-size: inherit; line-heig= ht: inherit; font-family: inherit; vertical-align: baseline;" dir=3D"ltr" d= ata-setdir=3D"false"><br></div><div style=3D"margin: 0px; padding: 0px 0px = 0.25em; border: 0px; font-style: inherit; font-weight: inherit; font-stretc= h: inherit; font-size: inherit; line-height: inherit; font-family: inherit;= vertical-align: baseline;" dir=3D"ltr" data-setdir=3D"false"><br></div><di= v style=3D"margin: 0px; padding: 0px 0px 0.25em; border: 0px; font-style: i= nherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; li= ne-height: inherit; font-family: inherit; vertical-align: baseline;" dir=3D= "ltr" data-setdir=3D"false"><span><span style=3D"color: rgb(0, 0, 0); font-= family: verdana, helvetica, sans-serif; font-size: 16px;"><br></span></span= ></div><div style=3D"margin: 0px; padding: 0px 0px 0.25em; border: 0px; fon= t-style: inherit; font-weight: inherit; font-stretch: inherit; font-size: i= nherit; line-height: inherit; font-family: inherit; vertical-align: baselin= e;">CREATE OR REPLACE FUNCTION "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_C= DIM_INS"() <span style=3D"font-family: inherit; font-size: inher= it; font-style: inherit; font-weight: inherit;">RETURNS trigger </span= ><span style=3D"font-family: inherit; font-size: inherit; font-style: inher= it; font-weight: inherit;">AS $$</span></div><p class=3D"ydpea5899c8_1qeIAg= B0cPwnLhDF9XSiJM" style=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: = 0px; font-style: inherit; font-weight: inherit; font-stretch: inherit; font= -size: inherit; line-height: inherit; font-family: inherit; vertical-align:= baseline;">declare</p><p class=3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" styl= e=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: 0px; font-style: inher= it; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-h= eight: inherit; font-family: inherit; vertical-align: baseline;">v_ErrorCod= e int;<br><span style=3D"font-family: inherit; font-size: inherit; font-sty= le: inherit; font-weight: inherit;">v_ErrorMsg varchar(512);<br></span><spa= n style=3D"font-family: inherit; font-size: inherit; font-style: inherit; f= ont-weight: inherit;">v_Module varchar(32) :=3D 'TR_STG_APPLICATION_CDIM_IN= S';</span></p><p class=3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"marg= in: 0px; padding: 0.8em 0px 0.25em; border: 0px; font-style: inherit; font-= weight: inherit; font-stretch: inherit; font-size: inherit; line-height: in= herit; font-family: inherit; vertical-align: baseline;">begin</p><p class= =3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"margin: 0px; padding: 0.8e= m 0px 0.25em; border: 0px; font-style: inherit; font-weight: inherit; font-= stretch: inherit; font-size: inherit; line-height: inherit; font-family: in= herit; vertical-align: baseline;">----<br></p><div><span style=3D"font-fami= ly: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;= ">-- If this is an INSERT operation</span></div><div><span style=3D"font-fa= mily: inherit; font-size: inherit; font-style: inherit; font-weight: inheri= t;">----</span></div><p></p><p class=3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM"= style=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: 0px; font-style: = inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; l= ine-height: inherit; font-family: inherit; vertical-align: baseline;">if TG= _OP =3D 'INSERT' then</p><p class=3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" st= yle=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: 0px; font-style: inh= erit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line= -height: inherit; font-family: inherit; vertical-align: baseline;"> &= nbsp;----<br><span style=3D"font-family: inherit; font-size: inherit; font-= style: inherit; font-weight: inherit;"> -- This just ensures th= at the filename is not null<br></span><span style=3D"font-family: inherit; = font-size: inherit; font-style: inherit; font-weight: inherit;"> &nbs= p;----</span></p><p class=3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"m= argin: 0px; padding: 0.8em 0px 0.25em; border: 0px; font-style: inherit; fo= nt-weight: inherit; font-stretch: inherit; font-size: inherit; line-height:= inherit; font-family: inherit; vertical-align: baseline;"> if = new.mig_filename IS NULL then<br> new.mig_filename :=3D= 'Unknown';<br> end if;</p><p class=3D"ydpea5899c8_1qeIAgB0cPwn= LhDF9XSiJM" style=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: 0px; f= ont-style: inherit; font-weight: inherit; font-stretch: inherit; font-size:= inherit; line-height: inherit; font-family: inherit; vertical-align: basel= ine;"> new.mig_insert_dt =3D current_timestamp;</p><p class=3D"= ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"margin: 0px; padding: 0.8em 0p= x 0.25em; border: 0px; font-style: inherit; font-weight: inherit; font-stre= tch: inherit; font-size: inherit; line-height: inherit; font-family: inheri= t; vertical-align: baseline;">end if;</p><p class=3D"ydpea5899c8_1qeIAgB0cP= wnLhDF9XSiJM" style=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: 0px;= font-style: inherit; font-weight: inherit; font-stretch: inherit; font-siz= e: inherit; line-height: inherit; font-family: inherit; vertical-align: bas= eline;">----<br>-- Exception error handler<br>----</p><p class=3D"ydpea5899= c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"margin: 0px; padding: 0.8em 0px 0.25em;= border: 0px; font-style: inherit; font-weight: inherit; font-stretch: inhe= rit; font-size: inherit; line-height: inherit; font-family: inherit; vertic= al-align: baseline;"> exception<br> when ot= hers then</p><p class=3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"margi= n: 0px; padding: 0.8em 0px 0.25em; border: 0px; font-style: inherit; font-w= eight: inherit; font-stretch: inherit; font-size: inherit; line-height: inh= erit; font-family: inherit; vertical-align: baseline;"> = v_ErrorCode :=3D SQLSTATE;<br> v_ErrorMsg :=3D SQLERRM= ;</p><p class=3D"ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"margin: 0px; = padding: 0.8em 0px 0.25em; border: 0px; font-style: inherit; font-weight: i= nherit; font-stretch: inherit; font-size: inherit; line-height: inherit; fo= nt-family: inherit; vertical-align: baseline;"> insert = into "ECISDRDM"."ERRORLOG"( "TSTAMP", "OS_USER", "HOST", "MODULE", "ERRORCO= DE", "ERRORMSG")<br>&= nbsp; values (CURRENT_TIMESTAMP, CURRENT_USER, inet_server_ad= dr(), v_Module, v_ErrorCode, v_ErrorMsg);</p><p class=3D"ydpea5899c8_1qeIAg= B0cPwnLhDF9XSiJM" style=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: = 0px; font-style: inherit; font-weight: inherit; font-stretch: inherit; font= -size: inherit; line-height: inherit; font-family: inherit; vertical-align:= baseline;">RETURN NEW;<br>end;<br>$$<br>language 'plpgsql';</p><p class=3D= "ydpea5899c8_1qeIAgB0cPwnLhDF9XSiJM" style=3D"margin: 0px; padding: 0.8em 0= px 0px; border: 0px; font-style: inherit; font-weight: inherit; font-stretc= h: inherit; font-size: inherit; line-height: inherit; font-family: inherit;= vertical-align: baseline;">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"() ;</p>= </blockquote><div style=3D"margin: 0px; padding: 0.8em 0px 0.25em; border: = 0px; font-stretch: inherit; font-size: 14px; line-height: inherit; font-fam= ily: Noto Sans, Arial, sans-serif; vertical-align: baseline; color: rgb(26,= 26, 27);">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.</div><div style=3D"margin: 0px; padding: 0.8em 0px 0.25em; bord= er: 0px; font-stretch: inherit; font-size: 14px; line-height: inherit; font= -family: Noto Sans, Arial, sans-serif; vertical-align: baseline; color: rgb= (26, 26, 27);">"</div><blockquote class=3D"ydpea5899c8_28lDeogZhLGXvE95QRPe= DL" style=3D"margin: 4px 0px 4px 8px; padding: 0px 0px 0px 8px; border-top:= 0px; border-right: 0px; border-bottom: 0px; border-left-width: ; border-im= age: initial; font-stretch: inherit; font-size: 14px; line-height: inherit;= font-family: Noto Sans, Arial, sans-serif; vertical-align: baseline; color= : rgb(26, 26, 27);"><div style=3D"margin: 0px; padding: 0px 0px 0.25em; bor= der: 0px; font-style: inherit; font-weight: inherit; font-stretch: inherit;= font-size: inherit; line-height: inherit; font-family: inherit; vertical-a= lign: baseline;">if new.mig_filename IS NULL then </div><div style=3D"= margin: 0px; padding: 0px 0px 0.25em; border: 0px; font-style: inherit; fon= t-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: = inherit; font-family: inherit; vertical-align: baseline;"><span style=3D"fo= nt-family: inherit; font-size: inherit; font-style: inherit; font-weight: i= nherit;"> RAISE EXCEPTION 'UNKNOWN';</span></div><div style=3D= "margin: 0px; padding: 0px 0px 0.25em; border: 0px; font-style: inherit; fo= nt-weight: inherit; font-stretch: inherit; font-size: inherit; line-height:= inherit; font-family: inherit; vertical-align: baseline;"><span style=3D"f= ont-family: inherit; font-size: inherit; font-style: inherit; font-weight: = inherit;"> new.mig_filename :=3D 'Unknown';</span></div><div s= tyle=3D"margin: 0px; padding: 0px 0px 0.25em; border: 0px; font-style: inhe= rit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-= height: inherit; font-family: inherit; vertical-align: baseline;"><span sty= le=3D"font-family: inherit; font-size: inherit; font-style: inherit; font-w= eight: inherit;">end if;</span></div><div style=3D"margin: 0px; padding: 0p= x 0px 0.25em; border: 0px; font-style: inherit; font-weight: inherit; font-= stretch: inherit; font-size: inherit; line-height: inherit; font-family: in= herit; vertical-align: baseline;" dir=3D"ltr" data-setdir=3D"false"><span s= tyle=3D"font-family: inherit; font-size: inherit; font-style: inherit; font= -weight: inherit;"><span><span style=3D"color: rgb(26, 26, 27); font-family= : Noto Sans, Arial, sans-serif; font-size: 14px;">new.mig_insert_dt '=3D no= w(); </span></span></span></div></blockquote></div><br></div><d= iv dir=3D"ltr" data-setdir=3D"false">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<span>= <span style=3D"color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-s= erif; font-size: 16px;">ould you please advise what I did wrong here? = </span></span></div><div dir=3D"ltr" data-setdir=3D"false"><span><spa= n style=3D"color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif= ; font-size: 16px;"><br></span></span></div><div dir=3D"ltr" data-setdir=3D= "false">thank you,</div><div dir=3D"ltr" data-setdir=3D"false"><span><span = style=3D"color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif; = font-size: 16px;"><br></span></span></div><div class=3D"ydp3b190d25signatur= e"><div id=3D"ydp3b190d25yui_3_13_0_rc_1_1_1381789421038_7868" class=3D"ydp= 3b190d25yiv3811552299ms__id13889"><font color=3D"#0000ff"><b><span class=3D= "ydp3b190d25yiv3811552299sg"><font color=3D"#8000ff" style=3D"background-co= lor: inherit;"></font></span></b></font><div id=3D"ydp3b190d25yui_3_13_0_rc= _1_1_1381789421038_7874" class=3D"ydp3b190d25yiv3811552299ms__id13893"><fon= t id=3D"ydp3b190d25yui_3_13_0_rc_1_1_1381789421038_7873" color=3D"#0000ff">= <font color=3D"#8000ff"></font><span id=3D"ydp3b190d25yui_3_13_0_rc_1_1_138= 1789421038_7871" class=3D"ydp3b190d25yiv3811552299sg"><font id=3D"ydp3b190d= 25yui_3_13_0_rc_1_1_1381789421038_7870" color=3D"#8000ff" style=3D"backgrou= nd-color: inherit;"><b id=3D"ydp3b190d25yui_3_13_0_rc_1_16_1381789421038_18= ">Bach-Nga<br clear=3D"none"><br clear=3D"none"></b>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.</font></span></font></div><di= v id=3D"ydp3b190d25yui_3_13_0_rc_1_1_1381789421038_7874" class=3D"ydp3b190d= 25yiv3811552299ms__id13893"><font color=3D"#0000ff"><span class=3D"ydp3b190= d25yiv3811552299sg"><font color=3D"#8000ff" style=3D"background-color: inhe= rit;">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)</font></span></font></div><br clear=3D"none"><span style=3D"colo= r:rgb(128, 0, 255);" id=3D"ydp3b190d25yui_3_13_0_rc_1_1_1381789421038_7867"= >**Live simply **Love generously **Care deeply **Speak kindly.</span></div>= <div id=3D"ydp3b190d25yui_3_13_0_rc_1_1_1381789421038_7866" class=3D"ydp3b1= 90d25yiv3811552299ms__id13894"><span id=3D"ydp3b190d25yui_3_13_0_rc_1_1_138= 1789421038_7865" class=3D"ydp3b190d25yiv3811552299sg"><font id=3D"ydp3b190d= 25yui_3_13_0_rc_1_1_1381789421038_7864" color=3D"#8000ff" style=3D"backgrou= nd-color: inherit;">*** Genuinely rich *** Faithful talent *** Sharing succ= ess<br></font></span></div></div></div></body></html> ------=_Part_2325582_1590264360.1570195612717--