sql - Insert a different value in underlying table when a view is updated -

consider following example have table , corresponding view:

table           | view t_emailaddress  | v_emailaddress ----------------+---------------- id      int     | id status  varchar | status valid   int     | column1 varchar | column1 columnn varchar | columnn 

i have program issues following queries:

update v_emailaddress set status = 'valid'   id = 1; update v_emailaddress set status = 'invalid' id = 2; 

is possible update underlying table status valid , invalid stored 1 , 0 inside valid column of underlying table.

note cannot change table or query. can add "normal" columns table.

you use instead of update trigger on view. when ever update statement on view completed, trigger fire. note instead of trigger means update blocked, , have write update statement underling table yourself.

something along these lines should trick:

create trigger v_emailaddress_io_update on v_emailaddress instead of update begin  update t set column1 = i.column1,     columnn = i.columnn,     valid = case when i.[status] = 'valid' 1 else 0 end  inserted  inner join t_emailaddress t on i.id = t.id  end 

note must include columns can updated in view in update statement, otherwise not updated.