我写了一个表A的插入触发器,但是在给参数赋值时出现了问题。具体的实现是通过表A的插入ID关联查询另一张表中的数据。但是查出来的数据为空...第一次发帖求教,不知道问题描述的是否清晰,触发器代码如下
DELIMITER $
CREATE TRIGGER trigger_insert_order_orderinfo AFTER INSERT ON orderinfo FOR EACH ROW
BEGIN
DECLARE totalprice DOUBLE;/*订单金额*/
DECLARE clearingtype VARCHAR(1);/*结算类型*/
DECLARE account DOUBLE;/*余额*/
DECLARE useedquota DOUBLE;/*已用额度*/
SET totalprice = (SELECT ordertotal FROM orderinfo WHERE orderID = new.orderID);
SET clearingtype = (SELECT clearingtype FROM agentcontrolinfo WHERE agentID = new.agentID);
SET account = (SELECT account FROM agentcontrolinfo WHERE agentID = new.agentID);
SET useedquota = (SELECT useedquota FROM agentcontrolinfo WHERE agentID = new.agentID);
IF clearingtype = '0' THEN
UPDATE agentcontrolinfo SET useedquota = useedquota+totalprice;
ELSEIF clearingtype = '1' THEN
UPDATE agentcontrolinfo SET useedquota = useedquota+totalprice;
ELSEIF clearingtype = '3' THEN
UPDATE agentcontrolinfo SET account = account-totalprice;
END IF;
END$
DELIMITER ;
DELIMITER $
CREATE TRIGGER trigger_insert_order_orderinfo AFTER INSERT ON orderinfo FOR EACH ROW
BEGIN
DECLARE totalprice DOUBLE;/*订单金额*/
DECLARE clearingtype VARCHAR(1);/*结算类型*/
DECLARE account DOUBLE;/*余额*/
DECLARE useedquota DOUBLE;/*已用额度*/
SET totalprice = (SELECT ordertotal FROM orderinfo WHERE orderID = new.orderID);
SET clearingtype = (SELECT clearingtype FROM agentcontrolinfo WHERE agentID = new.agentID);
SET account = (SELECT account FROM agentcontrolinfo WHERE agentID = new.agentID);
SET useedquota = (SELECT useedquota FROM agentcontrolinfo WHERE agentID = new.agentID);
IF clearingtype = '0' THEN
UPDATE agentcontrolinfo SET useedquota = useedquota+totalprice;
ELSEIF clearingtype = '1' THEN
UPDATE agentcontrolinfo SET useedquota = useedquota+totalprice;
ELSEIF clearingtype = '3' THEN
UPDATE agentcontrolinfo SET account = account-totalprice;
END IF;
END$
DELIMITER ;
You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.
SET clearingtype = (SELECT clearingtype FROM agentcontrolinfo WHERE agentID = new.agentID);
SET account = (SELECT account FROM agentcontrolinfo WHERE agentID = new.agentID);
SET useedquota = (SELECT useedquota FROM agentcontrolinfo WHERE agentID = new.agentID);
检查这4个变量的值