Exploring triggers

A trigger runs automatically when a predefined action is performed on the table. You should use triggers when data has changed in a database and you want to take action. There are two types of triggers in MySQL. The first is called a row-level trigger, which executes once for each row in the transaction. The second is called a statement-level trigger, which executes only once for each transaction.

There are three possible EVENTS a trigger can be assigned to – INSERT, UPDATE, and DELETE. A trigger can be run at a specific time concerning the event. The time can be either before or after the event occurs. A trigger can be used to validate data, log the old and new values in an audit trail, or ensure business rules are adhered to.

You can create a trigger using the following syntax:

CREATE TRIGGER trigger_name
(AFTER|BEFORE) (INSERT|UPDATE|DELETE)
ON table_name FOR EACH ROW BEGIN
SQL to execute
END

Let's look at various aspects of triggers.

...