触发器的使用

-- 触发器trigger格式
delimiter $$ -- 修改分隔符；改为$$ 这样在触发器中；就不会阻断程序了
create trigger user_insert_trigger
after insert on `user`
for each row -- 每行都要触发这个触发器
begin -- 开发触发器
 -- 自定义异常
 signal sqlstate '45000' set message_text='signal 停止设置，到此程序终止了 return';
  -- 程序代码
	insert into user_wallet (user_id,balance)
	VALUES(new.user_id,'0.00');
	
end ; -- 由于我已经将；改为$$所以这里的分号就不代表结束
$$ -- 结束了
delimiter ; -- 最后再将分隔符改回；

insert into `user` (username,`password`) VALUES('777','123456');

delimiter $$
create trigger delete_user_trigger
before delete on `user`
for each row
begin
 -- 删除代码
 delete from user_wallet where user_id=old.user_id;
 delete from user_wallet_log where user_id=old.user_id;
 end;
 $$
 delimiter ;
 
 delete from `user` where user_id=3;

#商品价格修改每次不能超过原价格的10%
delimiter $$
create trigger update_product_price
before update on product
for each row
begin
 -- 控制代码
 DECLARE result decimal(10,4);
 if new.price=0 then
  signal sqlstate '45000' set message_text='价格不允许改为0';
	end if;
	set result=(new.price-old.price)/old.price*100;
	signal sqlstate '45000' set message_text=result;
	if result>10 then
	signal sqlstate '45000' set message_text='价格上下浮动不能超过10%';
	end if;
	end;
	$$
	delimiter;