Results 1 to 9 of 9

Thread: Constraints Vs Triggers

  1. #1
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Constraints Vs Triggers

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


  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Constraints Vs Triggers

    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.


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Constraints Vs Triggers

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


  4. #4
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Re: Constraints Vs Triggers

    Quote Originally Posted by debasisdas View Post
    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?


  5. #5
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Constraints Vs 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.


  6. #6
    Junior Member
    Join Date
    Jun 2008
    Answers
    1

    Re: Constraints Vs Triggers

    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.


  7. #7
    Junior Member
    Join Date
    Jul 2008
    Answers
    1

    Re: Constraints Vs Triggers

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


  8. #8
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Constraints Vs Triggers

    Quote Originally Posted by khvaraprasad View Post
    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.


  9. #9
    Junior Member
    Join Date
    Mar 2009
    Answers
    28

    Wink Re: Constraints Vs Triggers

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact