Triggers in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 4

Forward: In this part of the series, we look at the nature of triggers in MySQL. Remember, we are dealing with MySQL 5.1.

By: Chrysanthus Date Published: 6 Aug 2012

Introduction

This is part 4 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at the nature of triggers in MySQL. Remember, we are dealing with MySQL 5.1.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

A trigger
Consider a trigger as an event handler. An event occurs, code is executed; that code is the event handler. When a trigger is executed we say the trigger is fired.

Trigger Execution Privileges
A trigger is associated with a table. To make things simple let the Database Administrator (DBA) code the trigger. It is possible that a trigger may access some other table. Let the user of the trigger (and of course its table), have the privilege to access the other table to be access by the trigger. I will access security issues in an entire different series.

Trigger Events
A trigger is fired immediately an event occurs. A trigger is defined for one of the following events:

INSERT: Invokes the trigger whenever a new row is inserted into the table associated with the trigger.
DELETE: Invokes the trigger whenever a row associated with the table is deleted.
UPDATE: Invokes the trigger whenever a row associated with the table is updated.

Trigger Times
BEFORE trigger: A BEFORE trigger fires before a triggering action is performed; that is before the event of INSERT, DELETE or UPDATE is performed.

AFTER trigger An AFTER trigger fires after the triggering action is complete; that is after the event of INSERT, DELETE or UPDATE is performed.

Trigger and Compound Statement
The core code of a trigger is a compound statement.

When does a Trigger Fire?
A trigger is associated with a table, and it fires when an update, insert or delete event occurs. So a trigger is fired automatically as soon as any of these events occur. Remember that a trigger has to be written first by the programmer. If no trigger is written, nothing is fired.

The CREATE TRIGGER Syntax
The CREATE TRIGGER Statement is used to create a trigger. Once you use this statement to create a trigger, the trigger is saved. You do not need any special command to save the trigger. The table name typed in the create trigger statement relates the trigger to the table. A simplified CREATE TRIGGER syntax is,

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body

Remember, for SQL syntaxes, [] means optional and | means and/or, at that position.

trigger_name
trigger-name is the name of your choice you give to the trigger.

trigger_time
trigger-time is the keyword, BEFORE or AFTER and is to do with the timing.

trigger_event
This is the Delete, or Update or Insert event.

Note: There cannot be two triggers for a given table that have the same trigger time and event. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

tbl_name
This is the name of the table associated with the trigger.

FOR EACH ROW
A MySQL trigger acts on a row. So this phrase has to be there.

trigger_body
This is the compound statement.

Referencing Old or New Row
You have the Insert row event and the delete row event. The Update process is equivalent to deleting a row and then inserting a new one. In the update process, you refer to the deleted row as OLD in the trigger_body; you refer to the inserted row as NEW. If you want to access any cell in the OLD row, the syntax is:

    OLD.col-name

If you want to access any cell in the NEW row, the syntax is:

    NEW.col-name

Note the use of the dot.

So far as the CREATE TRIGGER syntax is concerned, the INSERT event can only have a NEW row, since during Insert, a new row is inserted with no old row deleted. Also the Delete event can only have an OLD row, since during Delete, the old row is deleted and no new row is added.

Note: There is no semicolon after the instructions in the CREATE TRIGGER statement before the BEGIN statement.

Note: With triggers, after the Insert or Update or Delete has taken place, you can reverse the event. I will address that later.

Dropping a Trigger
You can drop (erase) a trigger. You use the DROP TRIGGER statement for this. In simple terms, the syntax is:

    DROP TRIGGER trigger_name;

That is it for this part of the series. Now, we are ready to code a trigger. Rendezvous in the next part.

Chrys

Broad Network

Related Articles

Triggers in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 4

Introduction

Related Links

Comments