Home arrow Site Administration arrow Page 8 - Database Essentials

Pulling The Trigger - Administration

Are you new to the wonderful world of databases? Confused by thesudden flood of technical jargon? Don't know the difference between a"trigger" and a "stored procedure", a "join" and a "subquery"? Look nofurther - the solution is right here!

  1. Database Essentials
  2. The Customer Is King
  3. Relationships
  4. Invasion Of The Foreign Keys
  5. Looking Up The Index
  6. Joined At The Hip
  7. Room With A View
  8. Pulling The Trigger
  9. So That's Where All My Money Went...
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 7
February 26, 2001

print this article


If your database supports it, you can also use "triggers" to automate certain database actions. A "trigger", as the name suggests, is a built-in database notification of specific events - inserting a new record, updating an entry, deleting a set of entries - and you can use this notification to automatically perform pre-defined actions.

To illustrate this, let's go back to our example database for a minute. In this database, each time a customer closes an account, the entry is deleted from the database, never to be recreated. If, instead, you'd like to set things up so that the entry is also backed up to another archive table, you could define a trigger that would run on all DELETE events, and copy the relevant data to another table prior to deleting it.
Here's an example of what an Oracle trigger might look like, in the situation just described:

sql> CREATE TRIGGER backupdata BEFORE delete ON balance FOR EACH ROW BEGIN INSERT INTO backup VALUES (CustomerID, AccountBalance); END;

Typically, triggers can be customized to a great extent - you can decide whether the action is to be taken before or after the event, once or multiple times, for specific records or all records, and even specify additional criteria to further focus the effect of the trigger.

For complex Web applications (typically those with mission-critical data), triggers come in very useful, since they make it possible to export simple rules to the database layer, thereby reducing the dependency on the application layer and sometimes even improving performance. I say "sometimes", because triggers can also increase database load significantly if used incorrectly - take the situation above, and extrapolate to imagine 5,000 records being deleted every second, and you'll immediately see why this could significantly hamper database performance.

>>> More Site Administration Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Coding: Not Just for Developers
- To Support or Not Support IE?
- Administration: Networking OSX and Win 7
- DotNetNuke Gets Social
- Integrating MailChimp with Joomla: Creating ...
- Integrating MailChimp with Joomla: List Mana...
- Integrating MailChimp with Joomla: Building ...
- Integrating MailChimp with Joomla
- More Top WordPress Plugins for Social Media
- Optimizing Security: SSH Public Key Authenti...
- Patches and Rejects in Software Configuratio...
- Configuring a CVS Server
- Managing Code and Teams for Cross-Platform S...
- Software Configuration Management
- Back Up a Joomla Site with Akeeba Backup

Developer Shed Affiliates


Dev Shed Tutorial Topics: