postgresql - How to get update statement in trigger function or when condition -

create table accounts (  id serial primary key,  email text,  verified boolean default false,  ... );  create or replace function fn_reset_verified() returns trigger $$ begin  if (     old.email != new.email -- nothing if no change )     raise notice 'update';     update users set verified=false id=new.id; end if; return new; end; $$ language 'plpgsql';  create trigger reset_verified after update of email on accounts each row when (old.email != new.email) -- when email changed execute procedure fn_reset_verified(); 

based on above schema, wish create trigger automatic update verified false if user change email. work below statement:

update accounts set email='newemail@email.com' id=1; 

my problem come in when intend update email , set verified true @ same time below statement:

update accounts set email='verified@email.com', verified=true id=1; 

if verified been set in update statement, should skip function. maybe condition below:

when (old.email != new.email , statement.verified null) 

so there way can update statement or involved column?

first, should never call update on table within update trigger on table. chances endless loop (not in particular case, anyway) , there typically better ways achieve same thing.

in case, make before update trigger fires when email address changes , check/modify verified column within trigger:

create or replace function fn_reset_verified() returns trigger $$ begin      -- below condition not have checked because of when clause in trigger if (     old.email != new.email -- nothing if no change ) then     raise notice 'update';     new.verified := ...; end if;     return new; end; $$ language 'plpgsql';  create trigger reset_verified before update of email on accounts each row when (old.email != new.email) -- when email changed execute procedure fn_reset_verified();

however, there no logic determine set new.verified inside trigger. thing is, new variable holds values of columns of new row after setting columns new values, not values being modified command. if new.verified = true there no way know if command invoked trigger was:

update accounts set email = 'newemail@email.com', verified = true id = 1; 

or

update accounts set email = 'newemail@email.com' id = 1; 

so new.verified refer old email address or new email address labeled verified in update command.

your option change logic: if email address changes, needs verified after, new.verified = false.