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 10K+ times! This question is

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