sql - create a trigger on a view -
i have time-dependent view uses now()
function , hence changes values passage of time.
simplistic code below:
drop view if exists av; drop table if exists a; create table (when_epoch_seconds integer, x varchar); insert a(when_epoch_seconds, x) select cast (extract (epoch now()) int), 'x'; create view av cte (select max(when_epoch_seconds) latestentry a) select cast (extract (epoch now()) int) - cte.latestentry > 5 too_old cte; select too_old av;
my question how can create trigger continuously monitors value of column too_old
in view av
and, e.g., inserts row in "notifications" table whenever value flips true false or vice-versa? or there maybe other mechanism that's more suited accomplishing same effect?
a view "virtual" table, meaning reflects values stored 1 or more tables. trigger set of instructions executed when specific event (insert, delete, etc) happens on table.
how can create trigger continuously monitors value of column too_old in view av
you can't create trigger related view. view doesn't "change" values, table does. view result set of records stored referencing table(s). edit: can create view (as @mikesherrill'catrecall' precisely said), in postgresql 9.1. feature introduced in latest versions of engine. if can create trigger on view, think correct way create on base table. don't see point in creating on view.
it doesn't exist trigger executed, listening changes of referencing table. if existing, trigger runs continuously affect terribly performance of sql engine, making sql engine sluggish , incredibly slow running queries. that, think, main reason why such thing doesn't exist.
the postgresql engine has nice feature you, listening changes, named indeed listen.
Comments
Post a Comment