MDBitz Development Resources

A Trigger is a Stored Procedure executed upon the modification to a database table. Triggers can be set to occur on INSERT, DELETE, and UPDATE of a table row, and can even be set to happen before or after the change occurs. Upon a change to a table that has a trigger the body of the Trigger is fired performing its functionality. This allows the database manager the ability to add in checks on data validity as well as modifying additional tables if needed.

Often Triggers are used for many functions the most common being:

• Relationship Enforcement
  By use of triggers you can create complex rules for data integrity. If you find that checks and reference constraints are not enough a trigger gives you the ability to define your complex rule.
• Cascading Operations
  By use of triggers you can cascade impacts from a change to a table throughout the rest of the database. For example if a salesperson is deleted or set to inactive you may want to change the sales rep who is in charge of their accounts.
• Audit Changes
  By use of triggers you can limit changes that occur to the database by disallowing updates and changes that should not be permitted.

Although triggers can be helpful in enforcing the integrity of your database there are 2 main disadvantages to using triggers.

• Activity Masking
  By containing business logic in the database by use of triggers then activity can be masked by the end user. Meaning that a user could mean to make a simple change to a table and instead causes changes throughout the database as they were unaware of the triggers.
• Potential Performance Impact
  Triggers if mismanaged can have high performance impacts on your database. As triggers are executed on every row modification they increase activity and if a Trigger modifies a table that has another trigger they can cause large cascades of activity.

A VIEW is a virtual table in a database whose contents is defined by a query. To the end user the view functions and seems like an actual table with defined columns and rows of data. However these rows and columns are visible by the query results of the view. For most practical purposes views allow an easier understanding of the Database for gathering information, but some DBMS allow views that you can do INSERT, DELETE, and UPDATE statements on.

Main Advantages of Using a VIEW

• Simplification of Queries
  
  As a view is actually a query on the database, it can draw information from multiple different tables. This allows the user the ability to query the single virtual table of the view instead of doing complex joins across multiple tables
• Security of Data
  
  A main concern in database is users access to data. If your database table contains personal or confidential data then you do not want to grant access to it to all users. Instead by use of a view you can grant access to a subset of columns and data keeping the confidential data secure.

Main Disadvantage of a VIEW

• Query Performance
  
  Although a view may appear as a table, the DBMS must still perform the underlying query when a query is executed against the view. If the view is a complicated multiple table query then a simply query of the view still results in a complex join. Which may take a long time to execute.