我有一张叫被许可的桌子。进行插入操作时，我希望调用触发器并更新BRIMSCode列。当我运行时，出现错误＃1442-无法更新存储的函数/触发器中的表'permitissued'，因为调用该存储的函数/触发器的语句已使用该表。
DROP TRIGGER IF EXISTS `updatePermitissuedBRIMSCode`;CREATE DEFINER=`root`@`localhost` TRIGGER `updatePermitissuedBRIMSCode` AFTER INSERT ON `permitissued` FOR EACH ROW UPDATE permitissued SET permitissued.BRIMSCode=(SELECT ParentBRIMSCode FROM subcategorycharges WHERE permitissued.ActivityCode = subcategorycharges.BRIMSCode)
这是我的SQL插入
INSERT INTO `businesses`.`permitissued` (`id`, `BusinessID`, `BillNo`, `ReceiptNo`, `CalenderYear`, `ActivityCode`, `SBPFee`, `RegistrationFee`, `DateIssued`, `AmountBilled`, `AmountPaid`, `Period`, `StartDate`, `EndDate`, `DateModified`, `printable`, `businessStatus`, `Status`, `BRIMSCode`) VALUES (NULL, '55555', '55555', '55555', '2020', '5', '5', '4', '4', '4', '4', '1', '4', '4', '4', '4', '1', '1', '0')
您可以改用BEFORE INSERT触发器并覆盖BRIMSCode值：
DROP TRIGGER IF EXISTS `updatePermitissuedBRIMSCode`;
CREATE DEFINER=`root`@`localhost` TRIGGER `updatePermitissuedBRIMSCode`
BEFORE INSERT ON `permitissued`
FOR EACH ROW
SET NEW.BRIMSCode = (SELECT ParentBRIMSCode
FROM subcategorycharges
WHERE NEW.ActivityCode = subcategorycharges.BRIMSCode)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句