Oracle 11g R2 – Creating an Insert Trigger on Views

oracle-11g-r2oracle-sql-developeroracle-xe

My table definitions look like this:

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

Best Answer

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.

Related Solutions

Materialized views master table

From the Oracle documentation

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table.1

So you should be able to see what changes are invalidating the MV.

Next you should examine the alert log to see if anything has been logged.

I wonder if you have a complex chain of materialized views? Does one depend on another? Is only one becoming invalid and others are not? Try this query to see what depends on what

dbms_snapshot.get_mv_dependencies( list IN VARCHAR2, deplist OUT VARCHAR2);

I would not recommend your suggested workaround until you understand what changes are invalidating the view.

Edit: Metalink note 264036.1 says:

"This is expected behavior. When there is a DML on the master table, all the MVs based on this table are marked as INVALID. Though the status is INVALID, you will be able to query the mview. However, the query on MV will not return latest update done in master table unless MV is refreshed."

From the way you are using the Materialized Views I am not sure why you cannot issue a refresh of each view that is marked invalid.

Trigger to automatically add values to a table in Oracle

This is very simple and there are many tutorials out there.

Here is a sample trigger to demonstrate.

create table aaaa
( 
  a number
);

create table bbbb
(
  b number
);

create trigger aaaa_aitrig
after insert on aaaa
for each row
begin
  insert into bbbb values ( :new.a );
end;
/

In case you were wondering, ":new" is a reference to the newly inserted row, and each column can be referenced individually.

Test case:

SQL> select count(*) from bbbb;

  COUNT(*)
----------
         0

SQL> insert into aaaa values ( 1 );

1 row created.

SQL> select * from bbbb;

         B
----------
         1

SQL>

Related Question