Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Posts
    58

    Unanswered: Advanteges & Disadvanteges of check constraints, triggers and stored procedures

    have anyone seen some articles on those issues ?
    thanks...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I cannot remeber any articles off the top of my head, but I will give it a go...

    Check Constraints: Advantages: insures data integrity. Limits data to specific values, etc. Disatvantages: Time it takes to perform constraint. Unique constraints are implemented via index

    Triggers: Advantages: Encapsulates BI into database-only one place to maintain BI logic. Disadvantages: Time it takes to execute.

    Stored Procedure: same reasons as triggers

    HTH

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Any time you have remote clients accessing a database server, there are significant benefits to using stored procedures to minimize network and communication time between client and server. If the application code is on the same server as the DBMS, there is not as much advantage. To some degree, the same is true of triggers and functions.

    But when implementing stored procedures, triggers, or functions, it is important (IMO) to maintain a high level of DBMS independence and platform independence. Often times people try to exploit proprietary features (or features that will not port to other DB2 platforms) and that can cause problems in the long run. In some cases, if performance is better, the work should be performed by the application.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    To supplement Marcus_A's comment ((or features that will not port to other DB2 platforms))

    The Cross-platform SQL Reference manual can be used as a reference

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    advantages: better data integrity, better performance, simpler apps, easier maintenance, better control, faster development...

    disadvantages: the (trivially small) effort required to declare constraints and stored procs and triggers, and the political strength to wrest the underlying business rules away from application developers, who seem to want to re-invent the wheel in application code for every single situation (e.g. do a select before every insert, to see if a row exists, so that we don't inadvertently insert a dupe, because heaven forbid the database might issue a unique constraint violation error, the users couldn't handle that...)

    and do not be misled by "oh my, what if the constraint or stored proc or trigger is not portable to other platforms? we'd better do this in application code"

    that way lies madness and truly gross application code


    rudy
    http://r937.com/

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The true madness is when people try to make the database do everything in one SQL statement and the SQL statement is several pages long.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •