删除和创建触发器MYSQL语句

 

删除触发器：

use 数据库名;

drop trigger 触发器名；

创建触发器:

1.插入语句时触发：

例：向表desk插入语句时，触动触发器，向表booksheet中插入数据

mysql> delimiter $
mysql> create trigger Desk1（触发器名） after insert on desk（表名） for each row
    -> begin
    -> declare Name_1 varchar(10);
    -> declare Sex_1 char(2);
    -> set Name_1 = (select User.Name from User where new.Phone=User.Phone);
    -> set Sex_1 = (select User.Sex from User where new.Phone=User.Phone);
    -> insert into booksheet (Phone,Number,Time,Date,Environment,Name,Sex,id)values(new.Phone,new.Number,new.Time,new.Date,new.Environment,Name_1,Sex_1,new.id);
    -> end;$

2.删除语句时触发：

例：删除表desk中信息时，触动触发器，向表booksheet删除数据

mysql> delimiter $

mysql> create trigger Desk2(触发器名)  after delete on desk(表名) for each row 

    -> begin
    ->delete from booksheet where Phone=old.Phone and Number = old.Number and Time=old.Time and Date=old.Date and Environment=old.Environment;
    ->end ;$