Thread: Constraints Vs Triggers

We can prevent invalid data using Triggers and Constrains?
Which one the better option? And what are advantages and disadvantages?

In my opinion Constraints is the better option. Always use constraints to reject inconsistent or bad data.Do not use triggers that duplicate features already built into Oracle Database.

It is always better to use constraints and avoid triggers. Triggers have a lot of side effects and drawbacks.

Originally Posted by debasisdas

It is always better to use constraints and avoid triggers. Triggers have a lot of side effects and drawbacks.

What are side effects and drawbacks of triggers?

A TRIGGER doesn't scale in a cost effective manner .

Data manipulation (INSERT, UPDATE, DELETE) often requires locks on tables, and even on indexes. That impedes other users access to data. The more code that must execute in the TRIGGER, the longer the locks are being held, the longer the disruption to other users, the higher the block/deadlock frequency.

That is true what debasisdas mentioned. Triggers do have these side effects. Having many triggers will slow down overall performance of the system in general.

what is difference in execution of Constraints and Triggers ? which is executes the first ???

Originally Posted by khvaraprasad

what is difference in execution of Constraints and Triggers ? which is executes the first ???

This is the execution order

- Executes before statement triggers
- Temporarly disable all integrity constraints upon that table
- Executes before row trigger
- Peform integrity constraint checking
- Execute after row trigger
- Complete differed integrity constraint checking
- Execute after statement triggers

Which executes first depends upon the type of trigger you defined.

Hi

Constraints Vs Triggers have its own advantages and dis-advantages.
My opinion is to follow constraints. in rare case better to follow triggers.
According to requirement first of all go through constraints is match with requirement are not, then go throught trigger.

Constraints: we can give secure to table content (eg avoid duplicates) but not table
Triggers:we can give secure to table content as well as table