Is there a BEFORE SELECT trigger in pl/sql?

Question

Hi I am using sqldeveloper conected to an oracle database.

My senario is I have a customer table in my database with a date field called start_date which 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_member is false if SYSDATE > start_date + 1year.

I would like to do a check to see if a user is still a member (and change is_member if not) before the user calls a select statement. Is this possible?

If not has anyone got any ideas on how else to keep the is_member field up to date? Can you do a trigger that is called once a day to see if the membership has expiered?

Thanks in advace! =]

Answer 1

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_member view column will always contain up-to-date data.

Answer 2

There is no such thing as a BEFORE SELECT trigger in PL/SQL.

It sounds like is_member should not be a column in a table at all but should be something that is computed in a view. Something like

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.