• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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

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?
0
boxy73
Asked:
boxy73
  • 3
  • 2
1 Solution
 
jpkempCommented:
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.

Jeff
0
 
boxy73Author Commented:
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.

Thanks
0
 
jpkempCommented:
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.

Jeff
0
 
boxy73Author Commented:
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?
0
 
boxy73Author Commented:
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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now