Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, madhan.

Asked: November 26, 2016 - 5:04 am UTC

Last updated: April 09, 2020 - 1:12 am UTC

Version: Oracle 10G XE

Viewed 1000+ times

You Asked

INSTEAD OF triggers are executed instead of DML statements that fired it. Is it correct??

and Connor said...

"Yes" but you can't have instead of triggers on a table, eg


SQL> create table t ( x int );

Table created.

SQL> create table t1 ( x int );

Table created.

SQL>
SQL> create or replace
  2  trigger TRG
  3  instead of insert on T
  4  for each row
  5  begin
  6    insert into t1 values (:new.x);
  7  end;
  8  /
instead of insert on T
                     *
ERROR at line 3:
ORA-25002: cannot create INSTEAD OF triggers on tables


SQL>
SQL> create or replace view V as select * from T;

View created.

SQL>
SQL> create or replace
  2  trigger TRG
  3  instead of insert on V
  4  for each row
  5  begin
  6    insert into t1 values (:new.x);
  7  end;
  8  /

Trigger created.

SQL>
SQL> insert into v values (1);

1 row created.

SQL>
SQL> select * from t;

no rows selected

SQL> select * from t1;

         X
----------
         1

1 row selected.

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

mala

victor rodiguez, May 15, 2018 - 8:40 pm UTC

bad
Connor McDonald
May 18, 2018 - 2:14 am UTC

very constructive ... thank you

Excellent response.

Dom Giles, April 06, 2020 - 10:48 am UTC

No idea what they mean by bad... I'm assuming that they can't use "Instead of" on the base table... However I loved your response....
Connor McDonald
April 09, 2020 - 1:12 am UTC

:-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library