1

I have a table with many columns and one special column that indicates if is synchronized or not. When an update occurs in the other columns I processed and update a 'log' table, this updates comes with the flag not synchronized, but when this was processed, I update the same row with a flag synchronized , in this case I do not to write in the log table. example:

Table: user( name, last_name, age, ..., sync_status )
IF (TG_OP = 'UPDATE' ) then 

     if new.sync_status != syncstatus_pending then 
        if old.sync_status = syncstatus_pending and new.sync_status != old.sync_status then 
           raise notice 'Sync status updated, It means we are changing from sync pending to sync yes';
           return new ; 
        else  
           raise Exception 'The synchronization status is not configured correctly.' ;
        end if ;
    end if;
    -- Check if we are trying to update a not synchronized row 
    if old.sync_status != syncstatus_yes then 
       raise Exception 'It was not possible to update rows are not synchronized.';
    end if ;
    if new.name = old.name and new.last_name = old.last_name and new.age = old.age 
    and old.sync_status= syncstatus_yes and new.sync_status= syncstatus_pending then 
   -- all columns are the same except the sync one, it means that we require sync this row but not to add to log_table 
    return new;
   else
   -- process to insert in the other 'log' table
   -- ...  
    insert into log_table ( )     
   end if ; 
end if ;

is there any other way to check that only one column is being updated? ( indicating that is synchronized or not )

0

Instead of using the value "syncstatus_yes", you could use your Syncstatus column to store a binary code that represents the columns you are updating.... and then move the syncstatus as it is to the log. This way you will know exactly which columns you are syncronizing...

whether you use your app code or either the trigger OLD and NEW values, you can know exactly which columns are being updated. Then you can use those columns to build your Syncstatus column

For example: (suppose you have 6 columns + syncstatus column)

Syncstatus = 19 = 010011 = power(2,(6-2))+power(2,(6-5))+power(2,(6-6)) 

this means that columns 2, 5 and 6 are being updated.

  • very clever , I will try it , thanks for your answer – Geist Jul 19 '18 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.