postgresql 在视图上执行触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/451096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute Trigger on View?
提问by mainstringargs
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 anydatabase offers something like this:
我对数据库触发器和/或视图不太熟悉。我目前正在使用 PostgreSQL 和 HSQL；虽然数据库不是太重要。我只是想知道是否有任何数据库提供这样的东西：
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
所以当然 DUMMY_VIEW 在我打电话时只包含 VALUES(1,10)
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.
所以现在我想要做的是向 DUMMY_VIEW 添加一个触发器，每当插入 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.
我曾尝试将触发器直接添加到 HSQL 和 PostgreSQL 中的 DUMMY_VIEW；但他们说触发器不能添加到视图中。
Is this (or a functionally similar solution) possible?
这（或功能类似的解决方案）是否可行？
回答by ocharles
It should be noted that PostgreSQL 9.1+ supports triggers on views. See WAITING FOR 9.1 – TRIGGERS ON VIEWSfor a brief look at this.
应该注意的是 PostgreSQL 9.1+ 支持视图触发器。参见WAITING FOR 9.1 – TRIGGERS ON VIEWS简要介绍一下。
回答by bogertron
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.
是的，触发器不能直接放置在视图上。您应该做的是在基表上放置一个触发器并检查新的 NUMBER 行的值是否大于 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.
注意：视图只是一个存储的选择语句，所以它并不真正保存数据。这就是为什么不能检查数据是否在视图结构中被插入、删除或更新的原因。
回答by Milen A. Radev
You may use rules instead of triggers (an example) but remember that rules are PostgreSQL-specific feature.
您可以使用规则而不是触发器（示例），但请记住，规则是 PostgreSQL 特定的功能。
回答by DanSingerman
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.
触发器可以在视图上使用查询，这样您就可以使用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();
回答by fredt
HSQLDB 2.x supports both updatable views and trigger-updatable views.
HSQLDB 2.x 支持可更新视图和触发器可更新视图。
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.
您的视图示例可自行更新。因此，您可以使用视图而不是表来插入/删除/更新行。这将不允许在插入和更新中包含 NUMBER <= 5 的行。
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.
您还可以在视图上定义触发器。这些触发器是用 INSTEAD OF INSERT、INSTEAD OF UPDATE 或 INSTEAD OF DELETE 定义的。在触发器的主体中，您可以检查值并为无效输入抛出异常，或将行插入基表。
回答by Michael Sharek
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
回答by Tomalak
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, unlessyou select from it. It can't contain a trigger, because it containsnothing.
触发器在数据更改时执行代码。视图是（比方说）“可调用的数据子集”。它几乎不存在，除非您从中选择。它不能包含触发器，因为它什么都不包含。
So basically you want a trigger on the base table.
所以基本上你想要一个基表上的触发器。
