Ankur Ghoshal
Ankur Ghoshal

Reputation: 21

Problem to update postgres table column values using TD["new"] from update trigger function written in plpython3u

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

Answers (0)

Related Questions