Php-mysql的触发器之实例篇(V客学院知识分享)
第一:创建触发器
在MySQL中,创建触发器语法如下:
代码如下:
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中:
trigger_name:标识触发器名称,用户自行指定;
trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;
trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;
trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。
1.CREATE TRIGGER trigger_name //触法器的名称
2 trigger_time //触法的时间 before /after
3.trigger_event //insert update delete
4.ON tbl_name //作用表
5 FOR EACH ROW trigger_stmt //执行触法语句
第二:实例
实例
表:
product
pro_id pro_name pro_price pro_num
1 aaa 5 10
2 bbb 50 20
3 cccc 50 20
orders
orders_id pro_id order_num
1 1 5
第一:insert 触法器
DROP TRIGGER IF EXISTS t_po;
CREATE TRIGGER t_po
after insert on orders
for each row
begin
update product set pro_num=pro_num-new.orders_num where pro_id=new.pro_id;
end
第二:DELETE 触法器
实例一
DROP TRIGGER IF EXISTS t_pod;
CREATE TRIGGER t_pod
BEFORE DELETE on orders
for each row
begin
update product set pro_num=pro_num+old.orders_num where pro_id=old.pro_id;
end
实例二
DROP TRIGGER IF EXISTS t_bs;
CREATE TRIGGER t_bs
BEFORE DELETE on bigclass
for each row
begin
delete from smallclass where BigClass_id=old.bigclass_id;
end ;
DROP TRIGGER IF EXISTS t_sp;
CREATE TRIGGER t_sp
BEFORE DELETE on smallclass
for each row
begin
delete from product where SmallClass_id=old.smallclass_id;
end
DELETE FROM bigclass WHERE bigclass_id=133;
SELECT * FROM bigclass;
SELECT * FROM smallclass;
SELECT * FROM product;
实例三:
DROP TRIGGER IF EXISTS t_bs;
CREATE TRIGGER t_bs
BEFORE DELETE on bigclass
for each row
begin
delete from smallclass where BigClass_id=old.bigclass_id;
delete from product where BigClass_id=old.bigclass_id;
end ;
第三:update 触法器
DROP TRIGGER IF EXISTS t_pou;
CREATE TRIGGER t_pou
BEFORE UPDATE on orders
for each row
begin
UPDATE product set pro_num=pro_num-old.orders_num where pro_id=old.pro_id;
end
//////////////////////////////////////////////////////////////////////////////////////////////
DROP TRIGGER IF EXISTS t_pou;
CREATE TRIGGER t_pou
BEFORE UPDATE on orders
for each row
begin
if old.orders_status<>new.orders_status THEN
if old.orders_status=1 THEN
UPDATE product set pro_num=pro_num-old.orders_num where pro_id=old.pro_id;
else
UPDATE product set pro_num=pro_num+old.orders_num where pro_id=old.pro_id;
end if;
end if
end
、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
DROP TRIGGER IF EXISTS t_pou1;
CREATE TRIGGER t_pou1
after UPDATE on orders
for each row
begin
if old.orders_num<>new.orders_num THEN
update product set pro_num=pro_num-(new.orders_num-old.orders_num) where pro_id=old.pro_id;
end if;
end
update orders set orders_num=orders_num+1 where orders_id=1;
select * from orders;
select * from product;
update orders set orders_num=orders_num-1 where orders_id=1;
select * from orders;
select * from product;
、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
DROP TRIGGER IF EXISTS t_pou2;
CREATE TRIGGER t_pou2
after UPDATE on orders
for each row
begin
update product set pro_time=new.orders_time where pro_id=old.pro_id;
end
DROP TRIGGER IF EXISTS t_pou2;
CREATE TRIGGER t_pou2
after UPDATE on orders
for each row
begin
update product set pro_time=new.orders_time where pro_id=new.pro_id;
end
本文通过实例分析了在项目中常用的触发器(PHP开发、web前端、UI设计、VR开发专业培训机构-v客IT学院版权所有,转载请注明出处,谢谢合作!)