Execute a trigger on a view in PostgreSQL

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.

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();

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.

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

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

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)