CODEWITHSUNDEEP
databasepostgresqlviewtriggershsqldb
mainstringargs
mainstringargs

Reputation: 14411

Execute a trigger on a view in PostgreSQL

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?

Upvotes: 11

Views: 24913

Answers (7)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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)

Upvotes: 0

ocharles
ocharles

Reputation: 6080

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.

Upvotes: 18

fredt
fredt

Reputation: 24372

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

Upvotes: 1

DanSingerman
DanSingerman

Reputation: 36532

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

Upvotes: 2

bogertron
bogertron

Reputation: 2355

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.

Upvotes: 9

Michael Sharek
Michael Sharek

Reputation: 5069

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

Upvotes: 1

Tomalak
Tomalak

Reputation: 338326

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.

Upvotes: 1

Related Questions