Re: Referential Integrity Enforcement: Do With Constraints or Triggers?

Hi Internetmaster (not related to a baddie in Dr Who are you :)

A nice little rule to follow with regard to triggers.

If you use a trigger to implement something that Oracle already has implemented (such as constraints, replication, auditing, delete cascade, etc. etc.), then question it. By using triggers to recreate the wheel, you may find your wheel may not be quite as round.

Cheers

Richard
"internetmaster" <youlove_at_me.com> wrote in message news:3D41C56D.2000707_at_me.com...
> In an earlier thread I mentioned the following observation:
>
>

http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=a20d 28ee.0207260158.3ecabf94%40posting.google.com&prev=/groups%3Fhl%3Den%26lr%3D %26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.oracle.tools
>
>
> I was wrong about one thing. The database does have primary keys
> defined, but no foreign keys. I went browsing through the data
> dictionary and found that the database has a ton of Triggers. It appears
> they're using Triggers to enforce the referential integrity rather than
> using constraints.
>
> They have Insert triggers on the parent tables that handle cascade
> deletes and prevent illegal inserts on child tables where they key value
> on the parent table doesn't exist.
>
> My question is it less expensive to handle the RI this way? I can see
> where creating a ton of constraints would get to be cumbersome and
> perhaps Triggers would be easier from a maintenance perspective.
>
> For example, there is one parent Table (Project) that has about 15 child
> tables associated to it logically. The developers of this application
> created 2 triggers to handle the Insert and Delete anomalies whereas 30
> constraints would had to have been coded.
>
> From a performance perspective, what are the ramifications for this
> design decision? How expensive is a constraint compared to a Trigger?
>
>
> Thanks.
>
Received on Sat Jul 27 2002 - 22:25:52 CDT