Execute Trigger on View?

postgresql triggers view hsqldb
mainstringargs picture mainstringargs · Jan 16, 2009 · Viewed 14.8k times · Source

I am not too familiar with database triggers and/or views. I am currently using PostgreSQL and HSQL; although the database is not too important. I am just wondering if any database offers something like this:

I have an (example) table like this:

CREATE TABLE DUMMY_TABLE (ID INTEGER, NUMBER INTEGER);

I created a view like this:

CREATE VIEW DUMMY_VIEW AS SELECT * FROM DUMMY_TABLE WHERE NUMBER > 5;

I insert a couple of entities:

INSERT INTO DUMMY_TABLE VALUES(1,2);
INSERT INTO DUMMY_TABLE VALUES(1,10);

so of course the DUMMY_VIEW only contains VALUES(1,10) when I call

SELECT * FROM DUMMY_VIEW

So now what I want to do is add a trigger to the DUMMY_VIEW that is called whenever an entity is inserted that has NUMBER > 5.

I have tried adding triggers directly to the DUMMY_VIEW in both HSQL and PostgreSQL; but they say that triggers cannot be added to views.

Is this (or a functionally similar solution) possible?

Answer

ocharles picture ocharles · Jul 18, 2011

It should be noted that PostgreSQL 9.1+ supports triggers on views. See WAITING FOR 9.1 – TRIGGERS ON VIEWS for a brief look at this.

Related questions

How do I temporarily disable triggers in PostgreSQL?

I'm bulk loading data and can re-calculate all trigger modifications much more cheaply after the fact than on a row-by-row basis. How can I temporarily disable all triggers in PostgreSQL?

Read More
Postgresql, update if row with some unique value exists, else insert

I have a URLs table. They contain (id int primary key, url character varying unique, content character varying, last analyzed date). I want to create trigger or something(rule may be), so each time i make insert from my java …

Read More
Get first date of month in postgres

I'm trying to get a 'date' type that corresponds to the first day of the current month. Basically one of my tables stores a date, but I want it to always be the first of the month, so I'm trying …

Read More