[打卡]触发器

#触发器
delimiter $$

CREATE TRIGGER after_user_insert

AFTER INSERT ON user

FOR EACH ROW

BEGIN

INSERT INTO user_wallet(user_id,balance)
VALUES(new.user_id,0.00);

END;

$$

delimiter;

INSERT INTO user(username,password,email,phone)
VALUES('孙悟空','123456','123@qq.com','110');

#触发器——删除操作
delimiter $$

CREATE TRIGGER user_delete_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;

#商品价不允许改为 0
delimiter $$

CREATE TRIGGER update_product_price
before update ON product
FOR EACH ROW
BEGIN

if new.price = 0 then
signal sqlstate '42001' set message_text = '价格不允许改为0';
end if;

END;
$$
delimiter;

#商品价不能超过原价的 10%
delimiter $$

CREATE TRIGGER update_product_price
before update ON product
FOR EACH ROW
BEGIN

DECLARE result DECIMAL(10,2);
if new.price = 0 then
signal sqlstate '42001' set message_text = '价格不允许改为0';
end if;

set result = (new.price-old.price)/old.price*100;


if result > 10 then
signal sqlstate '42001' set message_text = '价格上下浮动不能超过10%';

end if;
END;
$$
delimiter;