CODEWITHSUNDEEP
oracle-databasetriggersconstraints
zerkms
zerkms

Reputation: 254926

Oracle: constraint, that depends on another table

Sorry for weird title, don't know how to name the Q better. So:

I have 3 tables. EntityA, EntityB, AB. The classical many-to-many implementation.

Is there any possibility to create constraint that mandates ALWAYS to have at least one relation between A and B.

Example workflow:

a) Insert A, Insert B, insert relation, commit; SUCCESS
b) Insert A, Insert B, commit; FALSE

So the question is: is there any on commit trigger? Or something similar to.

Upvotes: 1

Views: 408

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

There is no such thing as an ON COMMIT trigger. However, you can generally simulate the behavior of an ON COMMIT trigger using materialized views. In your case, you could

  • create materialized view logs on the three tables
  • create a fast-refreshable materialized view that joins the three tables
  • create a constraint on the materialized view that raises an exception if there are any rows that fail the validation

When you commit, the materialized view refresh takes place. If a constraint on the materialized view fails, the commit fails.

Upvotes: 2

V4Vendetta
V4Vendetta

Reputation: 38210

You can set up deferred constraints on AB which will check for the validity of the values only on commit of the same. You can set up the PK of AB Table in EntityA and EntityB tables for referencing or vice versa(depending on that set the deferred constraints). Assuming this inserts are within a same transaction you maybe able to rollout the changes, if no valid entry goes into AB table.

Upvotes: 0

Related Questions