mysql触发器联立删除_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;|