create or replace view v_member as
select member_id, start_date
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
Related Q&A from Azim Fahmi
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.