Execute a trigger on a view in PostgreSQL

Question

I am not too familiar with database triggers and/or views. I am currently using PostgreSQL and HSQL, even though 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?

Kimkimball

Answer 1

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.

Glanders

triggers on views are instead of triggers and are used only to make updatable views. They will not solve the problem from the question, as they are not listening on the changes to underlying tables – Modal

when the data on an underlying tables changes then the view changes thats how they work. – Flacon

Answer 2

Yes, triggers cannot be placed on views directly. What you should do is place a trigger on the base table and check to see if the new NUMBER row has a value greater than 5.

Note: a view is only a stored select statement, so it does not really hold data. That is why one cannot check to see whether data is being inserted, deleted or updated in a view structure.

Selfdenial

in oracle you can place triggers on views – Ramage

As of postgresql 9.1, you can use triggers on views. – Stung

@JoeVanDyk copy pasting marcin's comment: triggers on views are instead of triggers and are used only to make updatable views. They will not solve the problem from the question, as they are not listening on the changes to underlying tables – Persecute

Answer 3

I think you have to put the trigger on the table, not the view.

The trigger could use a query on the view so that you are DRY.

Is there any other reason the trigger needs to be on the view and not the table?

An example in response to the comment

-- Create function
CREATE FUNCTION doWhatIwant() RETURNS trigger AS '
BEGIN
IF NEW.number > 5 THEN
  do_stuff
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER yourTrigger AFTER INSERT ON dummy_table
  FOR EACH ROW EXECUTE PROCEDURE doWhatIwant();

Fluorocarbon

The reason I want the trigger on the view is so that it is ONLY called when the Number of a newly inserted entity is greater than 5. Is this possible if I add a trigger to the table itself? This is all very unfamiliar to me. – Kimkimball

With HSQLDB, when you define the trigger on a table, the trigger code can have a condition on the inserted entities. You should use AFTER INSERT, AFTER UPDATE etc triggers. – Bluster

Answer 4

I'm not sure what you want to achieve.

A trigger executes code on data change. A view is a (let's say) "callable sub-set of data". It is virtually non-existent, unless you select from it. It can't contain a trigger, because it contains nothing.

So basically you want a trigger on the base table.

Invalid

Answer 5

This is possible if you add the trigger to the table with the same condition as the view.

The trigger body should have something like:

if (inserted.NUMBER > 5) {
   do something;
}
//do nothing if inserted.NUMBER is not > 5

Rimskykorsakov

Answer 6

HSQLDB 2.x supports both updatable views and trigger-updatable views.

Your view example is updatable by itself. Therefore you can insert / delete / update rows using the view instead of the table. This will not allow rows containing NUMBER <= 5 in inserts and updates.

You can also define triggers on the view. These triggers are defined with INSTEAD OF INSERT, INSTEAD OF UPDATE or INSTEAD OF DELETE. In the body of the trigger, you can check for the values and either throw an exception for invalid input, or insert the row into the base table.

see http://hsqldb.org/doc/2.0/guide/triggers-chapt.html

Bluster

Answer 7

For example, you create person table, then insert 2 rows into it as shown below.

CREATE TABLE person (
  id INTEGER,
  name VARCHAR(20)
);

INSERT INTO person (id, name) 
VALUES (1, 'John'), (2, 'David');

Next, you create log table, then you insert the row whose num is 0 into log table as shown below:

CREATE TABLE log (
  num INTEGER
);

INSERT INTO log (num) VALUES (0);

Now, you can create my_func() trigger function with RETURNS trigger and LANGUAGE plpgsql which increments num by 1 as shown below:

CREATE FUNCTION my_func() RETURNS trigger
AS $$
BEGIN
  UPDATE log SET num = num + 1;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Next, you create my_v view as shown below.

CREATE VIEW my_v AS
  SELECT * FROM person;

Now, you can create my_t trigger which runs my_func() when UPDATE or DELETE operation is prevented on my_view for each row as shown below:

CREATE TRIGGER my_t INSTEAD OF UPDATE OR DELETE ON my_v
FOR EACH ROW EXECUTE FUNCTION my_func();

*Memos:

• INSTEAD OF can prevent operations.

• You can use INSTEAD OF only with a view and FOR EACH ROW otherwise there is error.

• You cannot specify one or more columns with UPDATE OF when using INSTEAD OF otherwise there is error.

• You cannot use TRUNCATE with INSTEAD OF otherwise there is error.

• The trigger with BEFORE or AFTER and INSERT, UPDATE or DELETE on a view doesn't work. *My question and the answers explains it in detail.

Then, you insert a row to person table with my_v, then num is still 0 as shown below:

postgres=# INSERT INTO my_v (id, name) VALUES (3, 'Robert');
INSERT 0 1
postgres=# SELECT * FROM person;
 id |  name
----+--------
  1 | John
  2 | David
  3 | Robert
(3 rows)

postgres=# SELECT num FROM log;
 num
-----
   0
(1 row)

Then, you try to update name to Tom on all 3 rows on person table with my_v, then the update is prevented, then num is 3 as shown below:

postgres=# UPDATE my_v SET name = 'Tom';
UPDATE 0
postgres=# SELECT * FROM person;
 id |  name
----+--------
  1 | John
  2 | David
  3 | Robert
(3 rows)

postgres=# SELECT num FROM log;
 num
-----
   3
(1 row)

Then, you try to delete 2 rows from person table where id is 2 and 3 with my_v, then the deletion is prevented, then num is 5 as shown below:

postgres=# DELETE FROM my_v WHERE id IN (2, 3);
DELETE 0
postgres=# SELECT * FROM person;
 id |  name
----+--------
  1 | John
  2 | David
  3 | Robert
(3 rows)

postgres=# SELECT num FROM log;
 num
-----
   5
(1 row)

Socalled