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

On Fri, 26 Jul 2002 17:55:57 -0400, internetmaster <youlove_at_me.com> wrote:

>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=a20d28ee.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.

I have worked with several applications implementing the RI by means of triggers. We tested and confirmed implementing RI by means of constraints is about 8 times cheaper in terms of performance. Constraints don't use explicit sql to check RI, they are using internal mechanisms, triggers do use explicit sql. Your observations are incorrect, and I doubt whether those 2 triggers deal with all anomalies.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Jul 26 2002 - 17:33:45 CDT