MYSQL设置触发器权限问题的解决方法，mysql触发器

MYSQL设置触发器权限问题的解决方法，mysql触发器

本文实例讲述了MYSQL设置触发器权限的方法，针对权限错误的情况非常实用。具体分析如下：

mysql导入数据提示没有SUPER Privilege权限处理，如下所示：

ERROR 1419 (HY000): You do not have the SUPER Privilege and Binary Logging is Enabled

导入function 、 trigger 到 MySQL database,报错:

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”.

原因：
function / trigger 中有dangerous statements修改数据库，错误只在启用binary logging选项进行主从复制的服务器上出现。

解决方法如下：

1）导入数据的用户不仅需要CREATE ROUTINE, ALTER ROUTINE, CREATE TRIGGER, ALTER TRIGGER, CREATE FUNCTION 和 ALTER FUNCTION 权限，还需要SUPER privileges 权限，使用超级用户导入数据。

2）让所有用户具有执行类似functions的权限，危险，不推荐，

 o by specifying it on the server start, like: –log-bin-trust-function-creators=1
 o by setting it to 1 through the SET GLOBAL statement, like:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

3）如果不需要复制，或者是从库，关闭binlog，

# binary logging – not required for slaves, but recommended
#log-bin=mysql-bin
# binary logging format – mixed recommended
#binlog_format=mixed

希望本文所述对大家的MySQL数据库设计有所帮助。

由于 MySQL 没有直接抛出异常的语句
因此这里通过在触发器里面，插入/删除自己这个表
导致 MySQL 发生异常

发生异常了, 就会自动回滚掉 删除数据的处理了.

一个例子

货物表
CREATE TABLE Goods(
id INT,
Amount INT
);

订单表
CREATE TABLE OrderDetail(
ID INT,
GoodsID INT,
Amount INT
);

库存测试数据：
INSERT INTO Goods VALUES (1, 100);

要求：当订单表插入数据的时候，自动去货物表检查，是否有足够的库存
如果有，那么更新货物表的库存 = 原库存– 本次订单数量
如果库存不足，抱错返回.

DELIMITER //

CREATE TRIGGER BeforeInsertOrderDetail
BEFORE INSERT ON OrderDetail
FOR EACH ROW
BEGIN
DECLARE
v_nowCount INT;

SELECT
Amount INTO v_nowCount
FROM
Goods
WHERE
ID = new.GoodsID;

IF v_nowCount - new.Amount < 0 THEN
-- 由于 MySQL 没有直接抛出异常的语句
-- 因此这里通过在触发器里面，插入/删除自己这个表
-- 导致 MySQL 发生异常
DELETE FROM
OrderDetail
WHERE
ID = new.GoodsID;
ELSE
UPDATE
Goods
SET
Amount = Amount - new.Amount
WHERE
ID = new.GoodsID;
END IF;
END;
//
DELIMITER ;

处理前
mysql> select * from goods;
+------+--------+
| id | Amount |
+------+--------+
| 1 | 100 |
+------+--------+
1 row in set (0.00 sec)

mysql> select * from OrderDetail;
Empty set (0.00 sec)

mysql> INSERT INTO OrderDetail VALUES(1, 1, 90);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO OrderDetail VALUES(1, 1, 20);
ERROR 1442 (HY000): Can't update table 'orderdetail' in stored function/trigger
because it is already used by statement which invoked this stored function/trigg
er.

处理后

mysql> select * from goods;
+......余下全文>>
 

那是因为你这个数据库里面有个delete 的触发器，trigger 是用admin定义的，
解决办法；
1, 删除这个触发器
2，增加admin@localhost用户

2或1任取一个；
注意必须是admin@localhost，而不是admin@%或者admin@127.0.01这样的