Re: PL/SQL code in constraints?

sybrandb_at_yahoo.com wrote:
> Constraints signify relationships between entities.
> Consequently it is not possible to add any procedural logic.
> You'll need to use triggers.
> However note, you should avoid replacing constraints completely by
> triggers.

... especially if even the Oracle documentation doesn't get it right:

<quote>
You can use both triggers and integrity constraints to define and enforce any type of integrity rule. </quote>

This is simply not true.

Consider the following:
The business rule is to make sure that every value must be unique (ok, that's not hard to achieve, but you just can't with a trigger):

SQL> create table demo (id int);

Table created.

SQL> create trigger trg_demo

   2 before insert on demo for each row    3 declare
   4 l_cnt pls_integer;
   5 begin
   6 select count (*) into l_cnt from demo where :new.id = id;    7 if l_cnt > 0 then raise_application_error(-20000, 'Count larger 1 encountered');    8 end if;
   9 end;
  10 /

Trigger created.

SQL> insert into demo values (1);

1 row created.

Now in a second session, issue the same insert statement. Commit both.

No error raised.

But now we have 2 lines although we wanted to enforce only one?!

Baseline as far as I'm concerned:

If you can enforce integrity with a constraint, do it with a constraint. If you can't, then make sure that the read consistency you get with oracle doesn't get in the way.

> Enforcing integrity by means of triggers is about 8 times slower.

Now that's an interesting number. Would you care to elaborate on that?

Holger Received on Mon May 30 2005 - 09:58:44 CDT