mysql触发器删除实例1

mysql>create table t1 (a1 int);
mysql>create table t2 (a2 int , b2 char(5));

其中t2.a2和t1.a1是对应的，a2是全集 a1是子集,我想在删除t2里a2时,相应地删除t1里a1的值

mysql>delimiter |

>create trigger tt2_ad after delete on t2
>for each row begin
>delete from t1 where a1 not in(select a2 from t2);
>end;

>|
查看触发器是否创建成功?
mysql>show triggers;

----+
| Trigger | Event  | Table | Statement                                 | Timing | Created | sql_mode | Definer     |
+---------+--------+-------+-------------------------------------------+--------+---------+----------+----------------+
| tt2_ad  | DELETE | t2    | begin delete from t1 where a1=old.a2; end | AFTER  | NULL    |          | root@localhost |
+---------+--------+-------+-------------------------------------------+--------+---------+----------+----------------+

触发器的命名规则:ttable_(a|f)(d|i|u) t-触发器 table-表名 a|b-触发时间before | after d|i|u-触发动作 delete | insert | update


如果触发器编写错误就有可能引起:
ERROR 1442 (HY000): Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
首先检查触发器语法是否错误，我遇到的问题就是这样解决的。

delimiter |

create trigger tff_ad after delete on ff for each row
begin
if 2 = old.type then
update zz set a=0 where a=old.zid;    
delete from mm where b=old.zid;
elseif 1 = old.type then
update yy set c=0 where c=old.yid;
delete from mm where dd=old.yid;
end if;

end;|