Triggers in oracle

Question

What are the condtions where triggers can enhance or hinder the performance? When to use and when not to use the triggers in the system?

How triggers can be used to impose complex constraints?

Answer 1

Executing a trigger always has some overhead-- at a minimum, you are doing a context shift from the SQL engine to the PL/SQL engine for every row that causes the trigger to fire. While the absolute magnitude of the overhead of firing a trigger is relatively constant, the percentage overhead is highly variable depending on how you are doing DML. If you have an application that is adding or modifying rows in sets, which is the fastest way to operate on relational data, triggers have a much larger relative impact on performance because the cost of those context shifts plus the cost of whatever the trigger is actually doing, quickly dominates the cost of doing the triggering DML.

In theory, a trigger can be used to enforce complex constraints because a trigger can query other tables or call functions to do complex comparisons. In practice, however, it is extremely difficult if not impossible to code these triggers in a way that is actually correct in a multi-user environment so it is generally not a good idea to design a system that would need constraints that look at data across tables. That generally indicates a problem with the data model.

Answer 2

That's a very open question (a homework assignment possibly?). The Oracle Concepts Guide section on triggers is a good place to start learning about them.

Answer 3

Just a link to an interesting post of Tom Kyte about trigger vs declarative constraint.