Automatically updating a new information column in PostgreSQL

[Pepe TD Vo <pepevo@xxxxxxxxx>]

good morning experts,

I have a trigger before insert (even with or update) and seem it doesnt' work.  The function simply sets 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 := localtimestamp;

CREATE OR REPLACE FUNCTION "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"()  RETURNS trigger AS $$

declare

v_ErrorCode int;
v_ErrorMsg varchar(512);
v_Module varchar(32) := 'TR_STG_APPLICATION_CDIM_INS';

begin

----

-- If this is an INSERT operation

----

if TG_OP = 'INSERT' then

   ----
   -- This just ensures that the filename is not null
   ----

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

   new.mig_insert_dt = current_timestamp;

end if;

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

   exception
      when others then

      v_ErrorCode := SQLSTATE;
      v_ErrorMsg := SQLERRM;

      insert into "ECISDRDM"."ERRORLOG"( "TSTAMP", "OS_USER", "HOST", "MODULE", "ERRORCODE",               "ERRORMSG")
      values (CURRENT_TIMESTAMP, CURRENT_USER, inet_server_addr(), v_Module, v_ErrorCode, v_ErrorMsg);

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

CREATE TRIGGER "TR_STG_APPLICATION_CDIM_INS" BEFORE INSERT OR UPDATE ON "ECISDRDM"."STG_APPLICATION_CDIM" FOR EACH ROW EXECUTE PROCEDURE "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"() ;

I even " RAISE EXCEPTION 'UNKNOWN'; " and for the mig_insert_dt, I put either '=' or ':=' Now(), now(), localtimestamp, timestamp, and none of them would fill the time. Both mig.filename and mig_insert_dt are still blank.

"

if new.mig_filename IS NULL then 

    RAISE EXCEPTION 'UNKNOWN';

    new.mig_filename := 'Unknown';

end if;

new.mig_insert_dt '= now();   

According to the postgres example 39-3 "shows an example of trigger procedure in PL/pgSQL", I don't see any different with the example and don't know what I have missed here.  Would 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 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