MYSQL触发器更新所有行

y1aodyip  于 2023-05-28  发布在  Mysql
关注(0)|答案(1)|浏览(129)

我有两张table

CREATE TABLE `accounts` (
  `account` varchar(50) NOT NULL,
  `account_name` varchar(100) NOT NULL,
  `balance` decimal(10,2) DEFAULT NULL,
  `currecny` varchar(45) NOT NULL,
  PRIMARY KEY (`account`),
  UNIQUE KEY `account_UNIQUE` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

enter image description here

CREATE TABLE `dividends` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stock_symbol` varchar(10) NOT NULL,
  `account_id` varchar(50) NOT NULL,
  `gross_amount` decimal(6,2) NOT NULL,
  `withholding_tax` decimal(6,2) DEFAULT NULL,
  `net_amount` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

enter image description here
触发器

CREATE TRIGGER `balance_update_after_divi_insert` 
BEFORE INSERT ON `dividends` 
FOR EACH ROW update accounts, dividends 
SET balance = balance + NEW.net_amount
WHERE dividends.account_id = accounts.account

当我插入新的股息行时,它将更新所有帐户。
这个触发器有什么问题。

uttx8gqw

uttx8gqw1#

update accounts
SET accounts.balance = accounts.balance + NEW.net_amount
WHERE accounts.account = NEW.account_id

相关问题