Execute Trigger on View?

Question

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?

score 18 · Accepted Answer · answered Jul 17 '11 at 22:17

18

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.

answered Jul 17 '11 at 22:17

ocharles

6,172
2
35
46

6

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 – marcin Sep 22 '15 at 14:35
when the data on an underlying tables changes then the view changes thats how they work. – Chris Habgood Jun 01 '23 at 15:08

score 9 · Answer 2 · answered Jan 16 '09 at 16:59

9

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.

answered Jan 16 '09 at 16:59

bogertron

2,255
1
24
36

in oracle you can place triggers on views – Hoffmann Oct 28 '09 at 21:51
9

As of postgresql 9.1, you can use triggers on views. – Joe Van Dyk Feb 24 '12 at 03:50
1

@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 – ibizaman Jan 12 '18 at 20:10

DanSingerman · Answer 3 · 2009-01-16T17:00:57.100

2

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

edited Jan 16 '09 at 17:00

answered Jan 16 '09 at 16:53

DanSingerman

36,066
13
81
92

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. – mainstringargs Jan 16 '09 at 16:55
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. – fredt May 27 '11 at 19:10

score 1 · Answer 4 · answered Jan 16 '09 at 16:57

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.

score 1 · Answer 5 · answered Jan 16 '09 at 16:58

1

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

answered Jan 16 '09 at 16:58

Michael Sharek

5,043
2
30
33

score 1 · Answer 6 · answered Apr 17 '11 at 09:44

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

Execute Trigger on View?

6 Answers6

Linked