Skip to Main Content
  • Questions
  • Historical question about the definition of the constraining table in the Oracle documentation

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Serge.

Asked: January 18, 2021 - 9:29 am UTC

Last updated: February 10, 2021 - 4:56 am UTC

Version: Oracle 7

Viewed 100+ times

You Asked

Hello, AskTom!

Excuse me for the "idle" question, but none of my colleagues could answer this.
Many-many years ago, back in Oracle 7, was a "constraining error" ORA-04094 "Table is constraining, trigger may not modify it".
This restriction was relaxed since Oracle 8i. In the Oracle (8i and later) documentation you can read -

"constraining error prevented a row trigger from modifying a table when the parent statement implicitly read that table to enforce a foreign key constraint"

and it's absolutely clear for me. But the Oracle 7 documentation says -

"A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint".

I never undestood the first part of the sentence - "read directly, for a SQL statement": how a reading of the table can "constraint" the table in the triggering statement if the SQL operator reads the data in the table in the state at the moment in time BEFORE the row trigger can do any changes in the table to be read?

My opinion is - it was a mistake in the Oracle documentation and a correct definition of the constraining table would be "constraining table is a table that a triggering statement might need to read indirectly, for a declarative referential integrity constraint", without the first part.

and we said...

I agree the wording in older documentation was not great.

However (and I don't have either the docs or software to prove this) I *think* it may have been a hangover from the early iterations of v7.

As I said, I cannot be 100% positive on this, but I seem to remember early in my Oracle days, that as a developer, the team I was in had a basic rule of not going near any table that the trigger was on *or* any table linked by a foreign key. So I *suspect* (but cannot prove) that in the 7.1/7.2 timeframe, you couldn't even do a SELECT on a FK related table, a restriction that was relaxed then lifted in 7.3.

(I stress - this is 25 years ago so this might be completely nonsense!)

But just for fun ... here's the constraining table error in my 7.3 virtual machine :-)

ORA73_TRIGGER

Rating

  (2 ratings)

Comments

Connor, thank you for such a detailed answer!

Serge, January 20, 2021 - 3:41 pm UTC

Connor, thank you very much for such a detailed answer!

But you write about the case where two tables are linked by a foreign key. And what about the case when the SQL operator of the triggering statement reads data from another table and there are NOT any foreign keys between these tables? Was the table from which the data was read really "constraining" (according to the old documentation - it also was)?
Connor McDonald
January 28, 2021 - 9:09 am UTC

No foreign key = no constraining

Thank you, Connor!

Serge, February 04, 2021 - 11:56 am UTC

Thank you, Connor!
Forgive me if I seem overcautious - but will a constraining error occur if you had written "UPDATE emp SET ename = 'AAA', i.e. if you would try to change not the column that is a foreign key to the DEPT table, but the column that does not have any constraints?
Connor McDonald
February 10, 2021 - 4:56 am UTC

SQL> create or replace trigger bad_trig
  2  before update on emp
  3  for each row
  4  begin
  5    insert into dept values (60,'XXX','YYY');
  6  end;
  7  /

Trigger created.

SQL> update emp set ename = 'XXX' where rownum = 1;

1 row updated.

More to Explore

PL/SQL

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