MySQL does not contain cursors for the simple reason that they are slow. The only way to accomplish
cursor-type tasks is to create the algorithm in an API. This is faster and easier to read and follow. The
MySQL developers did a good job of weeding out the features that are not necessary and would take
away from what MySQL is good at—being fast.Triggers
A trigger is a stored procedure that is executed when a specified action is taken on a table, usually on an
UPDATE, INSERT, or DELETE. For example, every time a user deletes a record from the Customers table,
you want to archive that record in the Customer_Archive table. Instead of adding code to the application
that deletes the record, or calling a stored procedure manually from the application, you could use a trigger
that would do this. The trigger would fire and insert a record into the Customer_Archive table whenever a
record was deleted from the Customers table.
Triggers have many benefits. They can help ensure database integrity by maintaining table
relationships. Because they are fired on a particular action, they can perform cleanup automatically—
helping the DBA keep things in order. In a one-to-one relationship, for example, if a record was deleted
from one table, a trigger could be set to delete it from the corresponding table. In a one-to-many
relationship, a trigger could be set to fire when a record is deleted from the parent table and all the
children in the corresponding table would be deleted as well. This could save a lot of work and extra
code.
Another neat feature of triggers is their cascading effect. This occurs when a trigger that is fired on one
table causes another trigger to be fired on another table, and so on. This can be very helpful and very
dangerous. Suppose that you wanted to delete everything in the Meet_A_Geek database relating to a
customer when that customer terminates his or her membership. Instead of writing a lot of code to
perform cleanup, you could use triggers to do the job. You could create a trigger that deletes all that
customer's orders from the Orders table when that customer is deleted from the Customers table.
You can also have a trigger on the Orders table to delete any transactions with that order number
whenever an order is deleted. Now, when you delete a customer, two triggers are fired. One deletes all
the corresponding orders from the Orders table, which in turn fires the second trigger that deletes all
the orders from the transaction table. This saves time and performs some valuable functions.The downside to using triggers is that they slow down the system. Every time an action is taken, the
database must first determine if there is a trigger for that action, and then it must perform that action.
This can take away from the processing time for other actions. That is the primary reason why MySQL
doesn't have triggers; They add a bunch of overhead to the database system. By keeping that overhead
low, MySQL can outperform any database in its class.There are really no workarounds for using triggers in MySQL. If your application code is good and your
database design is good, the need for triggers is really minimal. The best advice—handle your integrity
in your code. Clean up after yourself, and things will be okay.Constraints
A lot of databases use constraints to help ensure data integrity. A constraint is a way to enforce relationships
and limit or ensure that data is in the expected format. There are several types of constraints—CHECK,
FOREIGN KEY, and UNIQUE, just to name a few. They all pretty much share a common goal—ensuring data
integrity.
There are many benefits to using constraints. One benefit is that there is no way you can break the
rules of a relationship between tables. If a record in a one-to-one relationship is deleted, the
corresponding record must also be deleted. If a record is added in one table, a new record must be
added in the other table as well. This is a great way to take the code out of the client and keep it on the
server—it's already built in.Another benefit is to ensure data integrity. If an alphanumeric column is supposed to contain only alpha
characters, you could use a check constraint to make sure that numbers are never entered in your
column. Again, this allows you to remove the code from your client application and place it on the
server.The biggest detriment of using constraints is that they add a lot of overhead, especially when inserting
and updating records. The system has to slow down, check the constraints, and then perform them.