Constraint at database table level instead of function

Question

I have the following select statement which I am using in a function to check overlapping of records and this function is called from application front end.

SELECT count(*),product_no 
from products p where lower(p.PRODUCT_REG_NO) ='GB-UNIGAS' 
and (NVL (p.return_date, p.end_date) >= '01-Jan-2015') 
and p.product_no in (select product_no from PRODUCT_MASTER  where  EXPIRED='N' 
                    and product_no  = p.product_no)

Instead of a function to check overlapping of records I would like to create a constraint in table so that even at database level there will not be any inserts or updates.

How can I create a constraint with the above sql statement?

Any help is highly appreciable.

Thanks

Use a BEFORE INSERT OR UPDATE trigger http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg13trg.htm (look 3/4 of the way down, at "Trigger for Complex Check Constraints: Example") — lc., Sep 24 '12 at 08:32
Not directly related to your problem, but I'd strongly recommend using to_date() with a defined date format instead of sth like NVL (p.return_date, p.end_date) >= '01-Jan-2015'). Otherwise, your application won't work properly for other locales. — Frank Schmitt, Sep 24 '12 at 08:38
@lc.It is better to have constraints or triggers to implement business rules? Regards — Jacob, Sep 24 '12 at 10:10
A trigger is the way to do this - but do read http://stackoverflow.com/questions/460316/are-database-triggers-evil first. In my experience, triggers tend to need a very high payback to pay for the additional complexity; if you've had real-world problems with this, consider it, but if it's a "just in case", I would probably not create the trigger.... — Neville Kuyt, Sep 24 '12 at 10:31

score 3 · Answer 1 · answered Sep 24 '12 at 08:39

3

You could create a Before Insert or update Trigger, check your condition, and raise an error if the new data don't meet your requeriments. This link will help you.

answered Sep 24 '12 at 08:39

Jonathan

11,809
5
57
91

Nick Krasnov · Accepted Answer · 2012-09-28T07:26:41.083

1

Keeping that kind of logic in a stored procedure is a good choice. Try to implement that logic in triggers and in some way or another you will end up looking at

ORA-04091: table <your table> is mutating, trigger/function may not see it

For example having implemented that in a trigger will allow you to use insert like

insert into <Yourtable> (<col1>...<coln>)
   values(val1... valn)

But you will definitely get a mutating table error if you try to execute insert like this one

 insert into <Yourtable> (<col1>...<coln>)
    select col1, .. coln 
      from some_table

If you do not want to use a stored procedure, your other choice might be a view and instead of trigger

Create or replace view <ViewName> as
  select *
    from your_table;

 create or replace trigger <Trigger_name> 
   instead of insert on <View_Name>
 begin
   -- your logic
 end;

IN ADDITION

Also to enforce business rules though constraints (CHECK constraint), you can combine CHECK constraints and materialized view as follows: Create materialized view (do not forget to create materialized view log before) for your table that would query data violating your business rules constraints.

 Create materialized view MV_1
  refresh complete on commit as
  --here goes your query
  -- selecting data you want to raise exception on

Add check constraint that will always be false. Something like this

alter table MV_1 add constraint CHK check(1=2) deferrable;

Having done that you will get check constraint <..> violated when you try to insert data in your table that violate your business rule constraints.

edited Sep 28 '12 at 07:26

answered Sep 24 '12 at 10:24

Nick Krasnov

26,886
6
61
78

I was getting the problem of mutating error and I tried your solution. Mutating issue resolved, however trigger did not fire and not application error was raised. What could be the reason for this? Regards – Jacob Sep 24 '12 at 10:30
1

you have to insert in a view not in the base table – Nick Krasnov Sep 24 '12 at 10:31
oh yes, you are spot on again. Thanks – Jacob Sep 24 '12 at 10:32
One more thing, for these kind of things like applying business rules do you recommend stored procedures/functions or triggers or constraints? Regards – Jacob Sep 24 '12 at 10:34
It's better to use stored procedure or procedures grouped in a package and/or constraints to enforce business rules – Nick Krasnov Sep 24 '12 at 10:57
As mentioned in my original post how this can be done using constraints to enforce my logic? Thanks – Jacob Sep 24 '12 at 11:04
Caveat programmor - we shall now read from the Book of Relations, chapter 2, verses 9 through 21 - "And then St. Codd said, 'Oh Lord, bless this, thy holy database design', and the Lord did bless it. Then the Lord said, 'First shall be the relations and the tuples, and the fields in their multiplicity. Next shall be the constraints, that the data shall be fair to look upon. And next shall be the triggers, but rememberest thou this - THOU SHALT IMPLEMENT NO APPLICATION LOGIC IN TRIGGERS or, being naughty in my sight, thou shalt suffer greatly". So, like, hey, what God wants, God gets... :-) – Bob Jarvis - Слава Україні Sep 24 '12 at 11:10
1

@Polppan You cannot use `check` constraints on base table to enforce that kind of rules, simply because they single-table, single_row constraints and cannot handle that. But you can use them in combination with materialized views. I've updated my answer. – Nick Krasnov Sep 24 '12 at 12:19
@NicholasKrasnov many thanks for your valuable suggestions and comments. Much appreciated. – Jacob Sep 24 '12 at 13:02

Constraint at database table level instead of function

2 Answers2