Re: Automatically updating a new information column in PostgreSQL

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"() ;

if new.mig_filename IS NULL then 

    RAISE EXCEPTION 'UNKNOWN';

    new.mig_filename := 'Unknown';

end if;

new.mig_insert_dt '= now();   

**Live simply **Love generously **Care deeply **Speak kindly.

*** Genuinely rich *** Faithful talent *** Sharing success