MYSQL Trigger Update is not working as supposed

Question

I have created an update trigger in MySQL Community Server 5.5.16 but when I try to update statement: Update Account set credit = 100 Where Number = 14001455; I get an error "ERROR 1172 (42000): Result consisted of more than one row". I do not understand why I get this error and what's wrong with my code:

delimiter |
CREATE TRIGGER t_creditexceed AFTER UPDATE ON Account
FOR EACH ROW
BEGIN 
    DECLARE n_overdraft INTEGER;
    DECLARE n_balance INTEGER;
    DECLARE n_number INTEGER;
    DECLARE n_credit INTEGER;
    DECLARE credit_exception condition for SQLSTATE '07030';

    SELECT balance, credit, number INTO n_balance, n_credit, n_number
    FROM Account;
    IF ((n_balance < (-n_credit)) AND (n_balance >= 1.1 * (-n_credit)))
    THEN
    SET n_overdraft = n_balance + n_credit;
    INSERT INTO overdraft (account_no, over_draft) VALUES (n_number, n_overdraft);
    END IF;
    IF (n_balance < 1.1 *(- n_credit))
    THEN signal credit_exception;
    END IF;
END;
|
delimiter ;

Marc B · Accepted Answer · 2012-02-01 14:26:11Z

2

This query:

SELECT balance, credit, number INTO n_balance, n_credit, n_number
FROM Account;

does not have a WHERE clause, so you'd be selecting ALL Account records into variables, which is where the error message comes from. Selecting into variables only works when you have a single result row - with multiple rows, MySQL has no way of knowing which row you want out of the result set. It won't pick for you.

answered Feb 1, 2012 at 14:26

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aurimas · Accepted Answer · 2012-02-01 14:28:05Z

1

I think your issue is the statement: SELECT balance, credit, number INTO n_balance, n_credit, n_number FROM Account; - it probably returns more than 1 row, and thus MySQL cannot load the values into the variables you defined.

Not sure about your intention, but maybe you were looking for NEW. and OLD. values?

answered Feb 1, 2012 at 14:28

Aurimas

2,49318 silver badges23 bronze badges

Comments

schtever · Accepted Answer · 2012-02-01 14:26:46Z

1

The initial "select balance, credit, number" is returning more than one row.

answered Feb 1, 2012 at 14:26

schtever

3,25019 silver badges25 bronze badges

Collectives™ on Stack Overflow

MYSQL Trigger Update is not working as supposed

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related