#1442 - Can't update table in stored function/trigger because it is already used

Question

I have a table called permitissued. When I do an instert, I would like the trigger to be called and update the BRIMSCode column. when I run I get the error #1442 - Can't update table 'permitissued' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

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)

This is my sql insert

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')

see this https://stackoverflow.com/questions/51543892/mysql-recursive-update-trigger — Nikos M., May 24 '20 at 06:58

Nick · Accepted Answer · 2020-05-24T07:08:26.597

You could use a BEFORE INSERT trigger instead and overwrite the BRIMSCode value:

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)

Demo on dbfiddle

#1442 - Can't update table in stored function/trigger because it is already used

1 Answers1