Fun With Triggers

I was trying to come up something funny|cute that used new features in 5.0 and settled on a quick little bit with a coffee pot and mug:

create table pot (coffee int unsigned);
create table mug (coffee int unsigned);

delimiter |

create trigger pour_cup after delete on pot
        for each row begin
                insert into mug (coffee) values (1);
        end
|

delimiter ;

Try it out:

mysql> insert into pot values (1), (1), (1), (1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from pot;
+--------+
| coffee |
+--------+
|      1 |
|      1 |
|      1 |
|      1 |
+--------+
4 rows in set (0.00 sec)

mysql> select * from mug;
Empty set (0.00 sec)

mysql> delete from pot limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from mug;
+--------+
| coffee |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Whenever someone deletes coffee from the pot it fills the cup. Get it? I’m going to bed. ;)

October 25, 2005 • Posted in: Geek, MySQL

One Response to “Fun With Triggers”

  1. Annet - November 11, 2005

    It’s the law of conservation of energy, matter and mass ;)

Leave a Reply