mysql触发器学习

青旅半醒 2022-08-13 18:58 161阅读 0赞

MySQL触发器四要素：

监视地点：监视的哪个表？

监视操作：insert,update,delete?

触发操作：insert,update,delete?

触发时间：after,before?

1、创建触发器语法：

Create trigger triggerName

after/before insert/update/delete on tableName

for each row  \#mysql此处写死，仅支持行触发，oracle、sql\_server支持表触发\#

Begin

sql语句 \#一句或多句 insert/update/delete语句\#

End

2、删除触发器语法：

Drop trigger triggerName;

3、显示已有触发器语法：

Show triggers

对insert而言，新插入的行用new来表示，行中每一列的值用new.列名来表示。

对于delete而言，删除的行用old来表示，行中每一列的值用old.列名来表示。

对于update而言，被修改的行，修改前的数据，用old来表示，old.列名；修改后的数据，用new来表示，new.列名。

after是先完成数据的增删改，再触发，触发中的语句晚于数据的增删改。

before是先触发再做数据的增删改，触发中的语句先于监视的增删改，我们有机会判断，修改即将发生的操作。

4、deom

\#deom1 -- 添加订单，库存减少（商品库存表g,订单表o）

delimiter $   \#定义$为结束符

truncate o$ \#清空table o

create trigger tg1

after insert on o

for each row

begin

update  g set num=num-new.much where id=new.gid;

end$

\#demo2  -- 删除订单，库存增加

create trigger tg2

after delete on o

for each row

begin

update g set num=num+old.much where id=old.gid;

end$

\#demo3   -- 修改订单数量，库存相应改变

create trigger tg3

after update on o

for each row

begin

update g set num=num+old.much-new.much where id=old.gid;

end$

\#demo4   -- before例子，判断insert 的数据是否合法

drop trigger tg1\#删除掉之前的insert触发器，一个action只能有一个触发器

create trigger tg4

before insert on o

for each row

begin

if new.much>5

then  set new.much=5;

end if;

update g set num=num-new.much where id=new.id;

end$

\--insert 触发器  
create trigger tri\_insert  
on student  \--将要进行更改的表名 for insert  \--给表插入一条数据的时候触发  
as  
declare @student\_id char(10) \--定义一个变量 select @student\_id=s.student\_id from  \--把查询出的指定字段的值赋值给变量student s inner join inserted i  
on s.student\_id=i.student\_id  
if @student\_id='0000000001'  
begin  
    raiserror('不能插入1的学号!',16,8)  
    rollback tran  
end  
go  
  
\--update触发器  
create trigger tri\_update  
on student \--将要进行更改的表名  
for update \--给表修改一条数据的时候触发  
as  
if update(student\_id)  
begin  
    raiserror('学号不能修改!',16,8)  
    rollback tran  
end  
go

\--delete触发器  
create trigger tri\_delete  
on student \--将要进行更改的表名 for delete \--给表删除一条数据的时候触发  
as  
declare @student\_id varchar(10)  
select @student\_id=student\_id from deleted  
if @student\_id='admin'  
begin  
    raiserror('错误',16,8)  
    rollback tran  
end

解释：在删除（delete）数据的时候，可以假定数据库将要删除的数据放到一个deleted临时表中，我们可以向读取普通的表一样，select 字段 from deleted  
而insert的时候道理一样，只不过是把要插入的数据放在inserted表中。  
更新操作可以认为是执行了两个操作，先把那一行记录delete掉，然后再insert，这样update操作实际上就对deleted表和 inserted表的操作，所以不会有updated表了，有的时候两个表是主外键关系，想删除主表数据的同时把子表相关的数据也删除，这个时候如果用触 发器就没有效果了，因为这个触发器是在你删除表后才触发的，这个时候直接终止，提示“有主外键关系，不能删除等”，所有这样的删除触发器是没有效果的