mysql——触发器

 触发器，是与表操作有关的一个数据库对象，当触发器所在表上发生指定操作时，将调用该对象，

一、基本触发器

二、使用操作值的触发器

1、使用新值NEW：插入、更新

 

2、使用旧值OLD：删除、更新

 

三、常用的触发器有6中：插入前、插入后、更新前、更新后、删除前、删除后，

-- 创建触发器：插入前
delimiter %%
drop trigger if exists alluser_to_woman %%
create trigger alluser_to_woman before insert on all_user for each row
begin 
    if NEW.sex = 'm' then
        insert into man(name) values(NEW.name);
    else
        insert into woman(name) values(NEW.name);
    end if;
end %%
delimiter ;


-- 触发
insert into all_user(name,pwd,sex) values('xue','123456','f');

-- 创建触发器：插入后
delimiter %%
drop trigger if exists alluser_to_woman %%
create trigger alluser_to_woman after insert on all_user for each row
begin 
    if NEW.sex = 'm' then
        insert into man(name) values(NEW.name);
    else
        insert into woman(name) values(NEW.name);
    end if;
end %%
delimiter ;


-- 触发
insert into all_user(name,pwd,sex) values('lucy','123456','f');

-- 创建触发器：删除后
delimiter %%
drop trigger if exists alluser_to_woman %%
create trigger alluser_to_woman after delete on all_user for each row
begin 
    if OLD.sex = 'm' then
        delete from man where name=OLD.name;
    else
        delete from woman where name=OLD.name;
    end if;
end %%
delimiter ;


-- 触发
delete from all_user where name='dong';

 

-- 创建触发器：删除前
delimiter %%
drop trigger if exists alluser_to_woman %%
create trigger alluser_to_woman before delete on all_user for each row
begin 
    if OLD.sex = 'm' then
        delete from man where name=OLD.name;
    else
        delete from woman where name=OLD.name;
    end if;
end %%
delimiter ;


-- 触发
delete from all_user where name='lucy';

-- 创建触发器：更新前
delimiter %%
drop trigger if exists alluser_to_woman %%
create trigger alluser_to_woman before update on all_user for each row
begin 
    if OLD.nid!=1 then
        update woman set nid=1 where name=NEW.name;
    end if;
end %%
delimiter ;


-- 触发
update all_user set nid=1 where name='luna';

-- 创建触发器：更新后
delimiter %%
drop trigger if exists alluser_to_woman %%
create trigger alluser_to_woman after update on all_user for each row
begin 
    if OLD.nid=1 then
        update woman set nid=NEW.nid where name=NEW.name;
    end if;
end %%
delimiter ;


-- 触发
update all_user set nid=2 where name='luna';