Postgres no trigger action on a condition

Hi,
   We are on Postgres 9.6.
  I have a table called tbl_client. This has many fields and one of the fields is called end_dt.
My requirement is to insert a row into another table called tbl_audit, for any changes in tbl_client.
So, i created a trigger to capture the same.

Now, the requirement is that i don't insert a row if the change is only on the end_Dt.

Can someone give me an idea how this can be added to the condition?
pvsbandiAsked:
Who is Participating?
 
earth man2Commented:
declare
  add_audit_record boolean;
begin
if OLD.end_dt <> NEW.end_dt then
   -- check if nothing else has changed then don't add audit record;
   add_audit_record := OLD.col1 <> NEW.col1 or OLD.col2 <> NEW.col2;  --etc
  if  add_audit_record then
       -- add audit record
  end if;
else
  -- add audit record
end if;

  return NEW;
end;
0
 
pvsbandiAuthor Commented:
Thank you
0
 
earth man2Commented:
if you need to test for columns changing to/from NULL you could consider using
add_audit_record := OLD.col1 is distinct from NEW.col1 or OLD.col2 is distinct from NEW.col2;  --etc
0
 
pvsbandiAuthor Commented:
Oh thank you! I wrapped both the sides with a coalesce to deal with the nulls.
0
 
pvsbandiAuthor Commented:
Hi,

   Using your logic, how can we compare fields using an update trigger and use the same for insert or delete?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.