Q
Problem solve Get help with specific problems with your technologies, process and projects.

Creating trigger to update start_date

I'm trying to create an INSTEAD OF trigger defined on a view that I have created. Here's the view:

create or replace view v_member as
select member_id, start_date
from lovingm;

I'm trying to create the trigger so it will update the lovingm table with member_id of 22, and set the start_date to 20-JAN-04. Right now the start_date for member_id 22 is 05-DEC-03. How do I create this trigger and how does it update the start_date?
INSTEAD OF triggers are triggers written on views so that users can perform DML operations on the underlying table or other tables when DML statements are written against the view.

In your example you will create an instead of view on your view v_member as follows. Then you can run a regular update statement against your v_member view as though v_member is a table.

CREATE OR REPLACE TRIGGER
UPDATE_V_MEMBER
INSTEAD OF UPDATE ON V_MEMBER
FOR EACH ROW
BEGIN
  IF :old.start_date <> :new.start_date
    THEN
    UPDATE LOVINGM
         SET START_DATE = :new.start_date
        WHERE MEMBER_ID = :old.member_id;
  END IF;
END;
/

Now, if you run 

UPDATE   V_MEMBER 
         SET  START_DATE = '20-JAN-04' 
    WHERE member_id = 22;

It will accomplish the desired objective.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close