mysql——触发器——示例

create table employee ( num int(50),
                        d_id int(50),
                        name varchar(50),
                        age int(50),
                        sex varchar(50),
                        homeadd varchar(50)
                       );


insert into employee values(1,1001,‘zhangsan‘,26,‘nan‘,‘beijing‘);
insert into employee values(2,1001,‘lisi‘,24,‘nv‘,‘hunan‘);
insert into employee values(3,1002,‘wangwu‘,25,‘nan‘,‘jiangsu‘);
insert into employee values(4,1004,‘aric‘,15,‘nan‘,‘yingguo‘);


select * from employee;



create table department ( d_id int(50),
                          d_name varchar(50),
                          functione varchar(50),
                          address varchar(50)
                        );


insert into department values(1001,‘keyanbu‘,‘yanfachanpin‘,‘3lou5hao‘);
insert into department values(1002,‘shengchanbu‘,‘shengchanchanp‘,‘5louyiceng‘);
insert into department values(1003,‘xiaoshoubu‘,‘cehuaxiaoshou‘,‘1louxiaoshoudating‘);


select * from department;

create trigger 触发器名  before| after 触发事件
on 表名 for each row

执行语句；

----------------------------------------------------------------------------

delimiter &&
create trigger 触发器名  before| after 触发事件
on 表名 for each row
  begin
        执行语句列表
  end
  &&
delimiter;


触发事件是指触发条件，包括insert、update、delete;

表名指触发事件操作的表的名称；

create table trigger_time ( exec_time varchar(50)
                          );



select * from trigger_time;



----------select now();

create  trigger dept_trig1 before insert on department for each row insert into trigger_time values ( now() );

delete from department where d_id = 1003;

insert into department values(1003,‘xiaoshoubu‘,‘cehuaxiaoshou‘,‘1louxiaoshoudating‘); 

select * from trigger_time;

create table trigger_time1 ( exec_time varchar(50)
                          );


select * from trigger_time1;

delimiter &&
create  trigger dept_trig2 after delete on department for each row 
begin
      insert into trigger_time1 values ( now() );
      insert into trigger_time values ( now() );
end 
&&
delimi

delete from department where d_id = 1003;

select * from department;
select * from trigger_time;
select * from trigger_time1;

select * from information_schema.triggers; /*查询所有*/

select * from information_schema.triggers where trigger_name = ‘dept_trig1‘; 

select * from information_schema.triggers where trigger_name = ‘dept_trig2‘; 

select * from information_schema.triggers;


drop trigger dept_trig1;

drop trigger dept_trig2;