MySQL 5.0 新特性教程 触发器:第一讲

mysql> drop function f;
query ok, 0 rows affected (0.00 sec)

mysql> create procedure p ()
-> begin
-> /* this procedure does nothing */ <--
-> end;//
query ok, 0 rows affected (0.00 sec)

alter 'cache index' call commit create delete

drop 'flush privileges' grant insert kill

lock optimize repair replace revoke

rollback savepoint 'select from table'

'set system variable' 'set transaction'

show 'start transaction' truncate update

create trigger <触发器名称> <--
{ before | after }
{ insert | update | delete }
on <表名称>
for each row
<触发器sql语句>

create trigger <触发器名称>
{ before | after } <--
{ insert | update | delete }
on <表名称>
for each row
<触发的sql语句>

create trigger <触发器名称>
{ before | after }
{ insert | update | delete } <--
on <表名称>
for each row
<触发的sql语句>

create trigger <触发器名称>
{ before | after }
{ insert | update | delete }
on <表名称> <--
for each row
<触发的sql语句>

create trigger <触发器名称>
{ before | after }
{ insert | update | delete }
on <表名称>
for each row <--
<触发的sql语句>

create trigger <触发器名称>
{ before | after }
{ insert | update | delete }
on <表名称>
for each row
<触发的sql语句> <--

grant create trigger on <表名称> to <用户或用户列表>;

revoke create trigger on <表名称> from <用户或用户列表>;

create trigger t21_au
before update on t22
for each row
begin
set @old = old . s1;
set @new = new.s1;
end;//

create table t22 (s1 integer)//

create trigger t22_bi
before insert on t22
for each row
begin
set @x = 'trigger was activated!';
set new.s1 = 55;
end;//

mysql> insert into t22 values (1)//

mysql> select @x, t22.* from t22//
+------------------------+------+
| @x | s1 |
+------------------------+------+
| trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)

create table t25
(s1 int, s2 char(5), primary key (s1),
check (left(s2,1)='a'))
engine=innodb;

create table t25
(s1 int, s2 char(5),
primary key (s1))
engine=innodb//

create trigger t25_bi
before insert on t25
for each row
if left(new.s2,1)<>'a' then set new.s1=0; end if;//

create trigger t25_bu
before update on t25
for each row
if left(new.s2,1)<>'a' then set new.s1=0; end if;//

insert into t25 values (0,'a') /* priming the pump */ //
insert into t25 values (5,'b') /* gets error '23000' */ //

don't believe the old mysql manual
该抛弃旧的mysql的手册了

　　我在这里警告大家不要相信过去的mysql手册中所说的了。我们已经去掉了关于触发器的错误的语句，但是仍旧有很多旧版本的手册在网上，举个例子，这是一个德国的url上的：

　　http://dev.mysql.com/doc/mysql/de/ansi_diff_triggers.html.

　　这个手册上说触发器就是存储过程，忘掉吧，你也已经看见了，触发器就是触发器，而存储过程还是存储过程。

　　手册上还说触发器可以从其他表上来删除，或者是当你删除一个事务的时候激发，无论他说的是什么意思，忘掉吧，mysql不会去实现这些的。

　　最后关于说使用触发器会对查询速度产生影响的说法也是错的，触发器不会对查询产生任何影响。

　　bugs
　　（不好的东西就不翻译了）
　　on december 14 2004, i did an "advanced search" in http://bugs.mysql.com for 'trigger' or
　　'triggers', i found that there were 17 active bugs as of that date. of course they might disappear
　　before you read this, but just in case they haven't, i'll mention the important ones. if they're still
　　there, you'll have to work around them when you're trying triggers.


　　bug#5859 drop table does not drop triggers.
　　（删除表的时候没有自动删除触发器）
　　when you drop a table, dropping the table's triggers should be automatic.


　　bug#5892 triggers have the wrong namespace.
　　（触发器的命名空间有错，你必须在前面加上表的名字才能删除触发器，下面是例子）
　　you have to say "drop trigger <table name> . <trigger name>".
　　the correct way is "drop trigger <trigger name>".


　　bug#5894 triggers with altered tables cause corrupt databases.
　　（触发器对表的改变可能会造成数据库数据被破坏）
　　do not alter a table that has a trigger on it, until you know this is fixed.


conclusion 最后

　　到了书的最后，我认为不需要给大家复习或者是重温一下了，因为相信大家可以很轻松的记住上面所说的。