usage of many triggers in Oracle 8-9 tables could become dangerous?

Posted on 2003-02-26
Medium Priority
Last Modified: 2008-03-10
We are working in a project using J2EE technologies along with Oracle 8 (and 9 in the future) as our database system.

When using logical deletes of registries and updates, we wanted to use triggers in order to ensure data integrity, and leaving this work to database system. We don't want to delegate this work on our EJBs, because we think that our system's behaviour would be affected. Furthermore, we could retrive data that could be changed by other processes, etc.

Is it correct to use triggers before or after update for every table, without charging them too much or should we do that work on our EJBs?
Question by:boxy73
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

ID: 8030744
The question of whether to use triggers or to do the work outside the DB (i.e. on the client) is a server/client question.

Basically, any business rule (logic) that determines what data should be stored in the database and how it should be manipulated should be implemented on the database (i.e. through constraints or triggers). Any business rule or logic that relates to the *presentation* of data (i.e. related to the user interface) should be implemented outside the database (e.g. in EJBs).

The idea is that the interface and the database are separate and loosely coupled. This way, you could (theoretically) drop your interface and write an entirely new one without worrying about maintaining data integrity.


Author Comment

ID: 8031646
Thanks for your answer, but bearing that in mind, which is quite important, I'm worried about the database's performance when using too much triggers.

Is there any doc or white paper referring to problems when using too much triggers in Oracle's databases? Anyway, these triggers wouldn't be too heavy. They just would seek if logical deletes can or cannot be done by executing simple selects.


Accepted Solution

jpkemp earned 150 total points
ID: 8031750
I'm not sure about any real problems with having heaps of triggers; I would first determine what is actually required, implement it, then see if any real performance problems ensue.

For example, if you want to execute some code whenever someone updates a particular column on a table, don't put a general "CREATE TRIGGER ... AFTER INSERT OR UPDATE ON ..." trigger; make it specific: "CREATE TRIGGER ... AFTER INSERT OR UPDATE OF thecolumn ON ...".

That said, often a better data model design will eliminate the need for many triggers, especially those for enforcing constraints. If at all possible, enforce constraints declaratively; only if this is impossible use a trigger to enforce them.


Author Comment

ID: 8034016
I know that, and it is true that the specification of the column will make it work better. The problem is that our data model design is not ours. We must use one that already existed, and we cannot change it in order to use constraints to make the things we need. That's the reason to decide between triggers on almost every table or using code in EJBs. And that's why I make this question.

Any other opinion?

Author Comment

ID: 8094251
Thanks for your approach. Relying updates to just the column that is involved in the logical deletion is a good idea, but anyway, I still don't know about performance problems of using too triggers, but I suspect it will affect server's load.

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question