Creating an insert trigger on views

Question

My table definitions look like this:

enter image description here

I've created a view as:

users_questions(qid,username,question)

I want to create an instead of trigger on the view which puts the data going to be inserted on the view to the table question.

I've tried:

CREATE OR REPLACE TRIGGER TRIGGER1
 INSTEAD OF INSERT ON USERS_QUESTIONS
 FOR EACH ROW AS
BEGIN
 INSERT INTO questions(qid,question)
 VALUES (:NEW.qid,:NEW.question);
END TRIGGER1;

It says something like:

Error report - ORA-04079: invalid trigger specification
04079. 00000 - "invalid trigger specification"
*Cause: The create TRIGGER statement is invalid.
*Action: Check the statement for correct syntax.

Tried this also.

CREATE OR REPLACE TRIGGER TRIGGER1 
INSTEAD OF INSERT ON USERS_QUESTIONS 
BEGIN
 INSERT INTO questions(qid,question)
 SELECT qid,question FROM inserted;
END;

Error:

Error(4,3): PL/SQL: SQL Statement ignored Error(5,32): PL/SQL:
ORA-00942: table or view does not exist

I am using:

• Oracle OracleXE112
• sqldeveloper-18.2.0.183.1748

• Colin 't Hart: I think you need to just remove as after for each row.

Answer 1

The error "Table or view does not exists" (at your second try) is caused by the SELECT qid,question FROM inserted; - As far as i can see, there’s no inserted table in your question. Is this table/view yours? Or it’s another user’s?

Hope this helps.

• Surya Bhusal: Actually its inbuilt table.