Reputation: 21
I am facing problems in updating column values from the postgresql trigger function written in plpython3u using TD['new'].
All the other statements before and after the TD['new'] assignments are executed properly, no exception or error is generated by the code.
Insertion/Updation of other table records(from the same trigger function) is happening properly. But the table, on which the trigger is implemented, is not updating its own column values.
I am new to database, and not able to detect the issue.
Please help me in this regards.
Thanks.
##---TRIGGER---
CREATE TRIGGER frs_master_update_trigger
BEFORE UPDATE
ON public.frs_master
FOR EACH ROW
WHEN (COALESCE(NEW.exec_dt, '1900-01-01 00:00:00+05:53:28') >= '1900-01-01 00:00:00+05:53:28')
EXECUTE PROCEDURE frs_master_upsert_proc();
##---TRIGGER FUNCTION---
CREATE OR REPLACE FUNCTION frs_master_upsert_proc()
RETURNS trigger AS
$$
...
--- <Other codes>
...
'''
Parameter List
fir_no : Holds the FIR no for which the updation will be performed
fir_exists : Holds True/False which indicates updation or insertion
status : Holds True or False value to indicate enrollment status
debug_msg : Holds the debug message for this method
'''
def updateFRSMasterTable(fir_no,fir_exists,status):
debug_msg = ''
try:
if not fir_exists:
##---Update insert_on column in frs_master table---###
TD["new"]["insert_on"] = datetime.datetime.now() ##---> NOT UPDATED
###---Update enrolled column in frs_master table---###
TD["new"]["enrolled"] = status ##---> NOT UPDATED
##---Update updated_on column in frs_master table---###
TD["new"]["updated_on"] = datetime.datetime.now() ##---> NOT UPDATED
# Commit the changes
master_conn.commit()
if DEBUG:
debug_msg = f'FRS Master is commited as enrolled = {status}'
return (True,debug_msg)
except Exception as e:
if DEBUG:
debug_msg = 'Fail to commit FRS Master database'
msg = 'Fail to commit FRS Master database'
handle_exception(msg)
return (False,debug_msg)
...
--- <Other codes>
...
$$
LANGUAGE 'plpython3u';
This is the debug log:
2022_02_21_11_44_22:['SUCCESS', <OTHER_DEBUG_MESSAGES>,'FRS Master is commited as enrolled = True',<OTHER_DEBUG_MESSAGES>]
Upvotes: 0
Views: 257