Creating a insert trigger on oracle views

Question

I'm working on my project for simple forum. For storing the datas i've database schema as:

To view the questions posted by the users easier i've created a view as

create view users_questions as select qid,username,questions from userinfo natural
join posts natural join questions

I am trying to create a trigger on that view to insert the data actually inserted into the table questions and post. I have basic knowledge on creating the trigger for tables[Had knowledge of trigger to backup the table rows when deleted]. But confused in case of the views, weather the instead trigger can be used in case of views to insert the data's into other tables.

Edited With My Try For Creating The Trigger

CREATE OR REPLACE TRIGGER TRIGGER1 
INSTEAD OF INSERT ON USERS_QUESTIONS 
BEGIN
    insert into questions values(new.qid,new.questions);  
END;

Not working shows error like:

Error(4,5): PL/SQL: SQL Statement ignored Error(4,46): PL/SQL:
ORA-00984: column not allowed here

What is your question here? If such triggers are possible? Yes, the documentation suggests that. — sticky bit
– sticky bit, Commented Aug 3, 2018 at 20:00
Be warned, by using triggers, you are putting a rope around your neck. Oracle recommends NOT to implement business logic with triggers. — Ychdziu
– Ychdziu, Commented Aug 3, 2018 at 20:18
Sorry not concerned about the oracle issues, for understanding the trigger uses. But thanks for the kind info @Ychdziu. Will never use in real life projects. — user7032676
– user7032676, Commented Aug 3, 2018 at 20:21
Coding suggestions - don't use NATURAL JOIN, as it makes it very difficult to determine what the join conditions are. Always state the join conditions explicitly so that everyone can see them. And always include a field list on your INSERT statements - that way your INSERTs will either still work after the table is modified, or you'll be able to easily find the statements which need to be updated. Best of luck. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Aug 4, 2018 at 3:25

eaolson · Accepted Answer · 2018-08-03 23:42:31Z

1

You need:

insert into questions values(:new.qid, :new.questions);

Colons in front of NEW

answered Aug 3, 2018 at 23:42

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bob Jarvis - Слава Україні Over a year ago

Or else add USING NEW AS NEW, OLD AS OLD to the trigger definition.

Collectives™ on Stack Overflow

Creating a insert trigger on oracle views

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related