My question is linked to the one below:
Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql
In my case, the trigger below works fine on my local setup (LAMPP on Ubuntu):
-- trigger to increment balance on posting of a new bill
DELIMITER
//
CREATE TRIGGER trig_increment_balance_new_bill AFTER INSERT ON
tbl_bill FOR EACH ROW
BEGIN
DECLARE
vBillAmount DOUBLE ;
SELECT NEW
.amount
INTO vBillAmount ;
UPDATE
tbl_client T
SET
balance = balance + vBillAmount
WHERE
id =(
SELECT
id
FROM
tbl_client C
WHERE NEW
.metreNo = C.metreNo
LIMIT 1
) ;
END ; //
DELIMITER
;
However, the error below occurs on my collaborator's setup (XAMPP on Windows) when they recreate the same code and test it.
SQLSTATE[HY000]: General error: 1093 Table 'T' is specified twice, both as a target for 'UPDATE' and as a separate source for data
I thought it was related to strict mode, but I found strict mode is activated; 'STRICT_ALL_TABLES' in my case. Why would errors occur on his setup but not on mine? I know the solution provided in the link above may cure this error but the question is why the error is occurring selectively?
There's no need for the subquery, just put the criteria directly in the WHERE
clause. You also don't need the vBillAmount
variable.
CREATE TRIGGER trig_increment_balance_new_bill AFTER INSERT ON tbl_bill
FOR EACH ROW
UPDATE tbl_client c
SET c.balance = c.balance + NEW.amount
WHERE c.metreNo = NEW.metreNo
LIMT 1;