0

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
  • I think you need to just remove as after for each row. – Colin 't Hart Aug 4 '18 at 9:44
0

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.

  • Actually its inbuilt table. – DevProg Sep 30 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.