From: | Pepe TD Vo <pepevo(at)yahoo(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Automatically updating a new information column in PostgreSQL |
Date: | 2019-10-04 13:26:52 |
Message-ID: | 744670167.2325583.1570195612720@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Voillequin, Jean-Marc | 2019-10-04 13:49:24 | RE: Automatically updating a new information column in PostgreSQL |
Previous Message | Tim Underwood | 2019-10-02 13:27:37 | Questions regarding pgAgent and ODBC drivers |
From | Date | Subject | |
---|---|---|---|
Next Message | Voillequin, Jean-Marc | 2019-10-04 13:49:24 | RE: Automatically updating a new information column in PostgreSQL |
Previous Message | Rob Sargent | 2019-09-27 16:41:05 | Re: Transactions in one table |