Playing with triggers

First create a table

CREATE TABLE points (user VARCHAR(255), points INT(11) );

Triggers are created with "CREATE TRIGGER" and dropped with "DROP TRIGGER" statement.

The following will create a trigger named mytrigger BEFORE (can use AFTER) INSERT (can use DELETE and UPDATE) on the table points
CREATE TRIGGER mytrigger BEFORE INSERT ON points
-> FOR EACH ROW SET @sum_points = @sum_points + NEW.points;

The statement following FOR EACH ROW defines the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering statement In the example, the triggered statement is a simple SET that accumulates the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.”

Using trigger
> SET @sum_points=0;
> INSERT INTO points SET user='me', points=50;
> INSERT INTO points SET user='me', points=60;


> SELECT @sum_points;
+-------------+
| @sum_points |
+-------------+
| 110 |
+-------------+
1 row in set (0.00 sec)


The trigger we created can be dropped using

DROP TRIGGER db.mytrigger;


If you logout and then log back in, @sum_points will have no value


mysql> SELECT @sum_points;
+-------------+
| @sum_points |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)



According to the MySQL manual:

you cannot have two triggers for a table that have the same activation time and activation event. For example, you cannot define two BEFORE INSERT triggers or two AFTER UPDATE triggers for a table. This should rarely be a significant limitation, because it is possible to define a trigger that executes multiple statements by using the BEGIN … END compound statement construct after FOR EACH ROW.

The MySQL manual also provides us with a way to define a trigger consisting of multiple statements


mysql> delimiter //
mysql> CREATE TRIGGER mytrigger BEFORE UPDATE ON points
-> FOR EACH ROW
-> BEGIN
-> IF NEW.points <> SET NEW.points = 0;
-> ELSEIF NEW.points > 100 THEN
-> SET NEW.points = 100;
-> END IF;
-> END;//
mysql> delimiter ;


To set the modified date automatically, add a field of type DATETIME to the table and cerate the trigger (after dropping)

CREATE TRIGGER mytrigger BEFORE INSERT ON points FOR EACH ROW SET NEW.modified = NOW();


Now when we run the query

INSERT INTO points SET user='me', points=60;

we get
mysql> SELECT * FROM points;
+------+--------+---------------------+
| user | points | modified |
+------+--------+---------------------+
| me | 60 | 2006-01-24 21:36:41 |
+------+--------+---------------------+
3 rows in set (0.00 sec)


More to come.