Database Essentials - Pulling The Trigger
(Page 8 of 9 )
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.
Next: So That's Where All My Money Went... >>
More Administration Articles
More By icarus, (c) Melonfire
/ 7 
