oracle pl/sql 中是否有 BEFORE SELECT 触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9331784/
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
Is there a BEFORE SELECT trigger in pl/sql?
提问by geekman92
Hi I am using sqldeveloper conected to an oracle database.
嗨，我正在使用连接到 oracle 数据库的 sqldeveloper。
My senario is I have a customer table in my database with a date field called start_datewhich holds the date of the day the user signed up and a "boolean" field called is_member. Each membership only lasts one year so is_memberis false if SYSDATE > start_date + 1year.
我的场景是我的数据库中有一个客户表，其中一个名为的日期字段start_date保存用户注册的日期和一个名为is_member. 每个会员资格只持续一年，所以is_member如果 是假的SYSDATE > start_date + 1year。
I would like to do a check to see if a user is still a member (and change is_memberif not) before the user calls a select statement. Is this possible?
我想is_member在用户调用 select 语句之前检查一下用户是否仍然是成员（如果不是则更改）。这可能吗？
If not has anyone got any ideas on how else to keep the is_memberfield up to date? Can you do a trigger that is called once a day to see if the membership has expiered?
如果没有，有人对如何使该is_member领域保持最新有任何想法吗？能不能做一个每天调用一次的触发器，看看会员是否过期了？
Thanks in advace! =]
预先感谢！=]
回答by Vincent Malgrat
Don't use a table column to store dependent data, use a view:
不要使用表列来存储依赖数据，使用视图：
CREATE OR REPLACE VIEW customer_v AS
SELECT c.*,
CASE WHEN SYSDATE > add_months(c.start_date, 12)
THEN 'FALSE'
ELSE 'TRUE'
END is_member
FROM customer c
The is_memberview column will always contain up-to-date data.
该is_member视图的列将始终包含了最新的数据。
回答by Justin Cave
There is no such thing as a BEFORE SELECTtrigger in PL/SQL.
BEFORE SELECTPL/SQL 中没有触发器这样的东西。
It sounds like is_membershould not be a column in a table at all but should be something that is computed in a view. Something like
听起来is_member根本不应该是表中的列，而应该是在视图中计算的内容。就像是
CREATE OR REPLACE VIEW view_name
AS
SELECT <<list_of_columns>>
(CASE WHEN sysdate > add_months(start_date, 12)
THEN 'Y'
ELSE 'N'
END) is_member
FROM your_table
Your code would then simply query the view rather than querying the base table.
然后您的代码将简单地查询视图而不是查询基表。
回答by HLGEM
This is a job for the application to do the checking not a trigger. Triiger do not fire on SELECT.
这是应用程序进行检查而不是触发器的工作。Triiger 不会在 SELECT 上触发。
As far as keeping the field up-to-date, there are two possibilities. First set up a nightly job to inactivate any members whose meberships have expired. Or change your struture to store membershipenddate rather than isactive and use that in the application check to see if the member is active.
就使该领域保持最新而言，有两种可能性。首先设置一个夜间作业以停用任何会员资格已过期的成员。或者更改您的结构以存储membershipenddate 而不是isactive 并在应用程序中使用它来检查成员是否处于活动状态。
回答by Juan Rada
You can add a Policy to your table and schema and Add the code that you want.
您可以向表和架构添加策略并添加所需的代码。
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#CIHCAACD
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#CIHCAACD
CREATE OR REPLACE FUNCTION auth_orders(
schema_var IN VARCHAR2,
table_var IN VARCHAR2)
RETURN VARCHAR2
IS
return_val VARCHAR2 (400);
BEGIN
return_val := 'SALES_REP_ID = 159';
RETURN return_val;
END auth_orders;
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'oe',
object_name => 'orders',
policy_name => 'orders_policy',
function_schema => 'sys',
policy_function => 'auth_orders',
statement_types => 'select, insert, update, delete'
);
END;
回答by Dax
You can define a job which is run everyday and update the is_member field.
您可以定义每天运行的作业并更新 is_member 字段。
