June 25, 2011, 3:42 a.m.
posted by novinick
An Example of a Trigger
In most examples in this section and the next section, we use a new table in the database of the tennis club: the CHANGES table. Imagine that this table is used to record which users have updated the PLAYERS table and at what moment.
Figure. Create the CHANGES table.
Explanation: The meaning of the first two columns is obvious. In the third column, CHA_PLAYERNO, the player number of the player who was added or removed, or whose column value was changed, is recorded. If the player number of a player is changed, the new player number is recorded in the CHA_PLAYERNO_NEW column. This column is therefore used only when the player number is updated; otherwise, a NULL value is stored. In the CHA_TYPE column, the type of change is filled in: I(nsert), U(pdate), or D(elete). The primary key of this table is formed by the columns USER, CHA_TIME, CHA_PLAYERNO, and CHA_TYPE. In other words, if a user executes two changes of the same type on the same player at the same moment, it needs to be recorded only once.
The definition of the CREATE TRIGGER statement is given next. Triggers consist of four main elements: the trigger moment, the trigger event, the trigger condition, and the trigger action. These elements appear clearly in the following definition. For a description of the concept of begin-end blocks, refer to Section 30.4, in Chapter 30, "Stored Procedures."
Note that in the more academic literature, triggers are sometimes called ECA rules (for "Event, Condition, Action"). However, terms such as production rules, forward-chaining rules, assertions, and just rules are also used. See [WIDO96] for an extensive description of triggers.
We begin in a way that is now familiar, with a simple example in which a minimal set of specifications is used.
Figure. Create the trigger that updates the CHANGES table automatically as new rows are added to the PLAYERS table.
Explanation: Just like every SQL statement for creating a database object, the statement begins by assigning a name to the trigger: INSERT_PLAYER. Next, all the other specifications follow.
The second line contains the trigger moment (AFTER). This element specifies when the trigger must be started. In this case, it happens after the INSERT statement on the PLAYERS table has been processed.
The third line contains the trigger event. This element specifies the operations for which the trigger has to be activated. In this case, the trigger must be activated at an INSERT statement on the PLAYERS table. Sometimes, this is called the triggering statement, and the PLAYERS table is called the triggering table. If the triggering statement has taken place, the body of the trigger, or the trigger action, must be executed. The trigger action is, in fact, what the trigger is about to do. It is usually a number of statements that are executed. We focus on the trigger action in more detail later.
The words ON, OF, FROM, and INTO after the words INSERT have no special meaning. You can use them as you want.
The word AFTER as a trigger moment, however, is important. If we use a SELECT statement in the trigger action to query the number of rows of the PLAYERS table, the row added is actually counted. The reason for this is that the trigger action is started after the triggering statement has been processed. If we had specified BEFORE, the row would not have been included because the trigger action would have been executed first. AFTER is usually used if we want to execute several more changes after the triggering statement and BEFORE if we want to verify whether the new data is correct (meaning, satisfying the constraints applied).
A third possible trigger moment is INSTEAD OF. If this specification is used, the triggering statement is not executed at allonly the trigger action is. The trigger action is then executed instead of the triggering statement.
The trigger event contains the specification FOR EACH ROW. This is used to specify that, for each individual row that is inserted into the PLAYERS table, the trigger action has to be activated. So, if we add a set of rows to the PLAYERS table with one INSERT SELECT statement in one operation, the trigger will still be executed for each row. (See Section 14.3, in Chapter 14, "Updating Tables," for a description of this statement.) The counterpart of FOR EACH ROW is FOR EACH STATEMENT. If we had specified this, the trigger would have been activated only once for each triggering statement. This means that if we inserted a thousand rows with one INSERT SELECT statement, the trigger would still be executed only once. Alternatively, if we remove a million rows with one DELETE statement, and if the triggering statement is a DELETE, the trigger is still executed only once if FOR EACH STATEMENT is specified.
A trigger action can be just as simple or as complex as the body of a stored procedure. The trigger action in our example is very simple because it consists of only one INSERT statement. This additional INSERT statement inserts one row, consisting of four values, in the CHANGES table. These are, respectively, the value of the system variable USER, the system date and time, the player number of the new player, and the literal I to indicate that it is an INSERT.
NEW is specified in front of the column name PLAYERNO. This is an important specification. If a row is inserted, it looks as if there is a table called NEW. The column names of this NEW table are equal to those of the triggering table (those in which the new row appears). As a result of specifying NEW in front of PLAYERNO, the player number that is added to the PLAYERS table is used. Its use will be obvious when we change rows in the PLAYERS table. We come back to this issue later.
To conclude this section, we mention that triggers can also call stored procedures. Therefore, the previous CREATE TRIGGER statement can be divided into two parts. First, we create a stored procedure:
CREATE PROCEDURE INSERT_CHANGE (IN CPNO INTEGER, IN CTYPE CHAR(1), IN CPNO_NEW INTEGER) BEGIN INSERT INTO CHANGES (USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_PLAYERNO_NEW) VALUES (USER, CURDATE(), CPNO, CTYPE, CPNO_NEW); END
CREATE TRIGGER INSERT_PLAYER AFTER INSERT ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGE(NEW.PLAYERNO, 'I', NULL); END