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