I'm a bit confused on this topic; I am wanting to insert a record and at the same time do an update on the same table through a trigger. I'm working on MariaDB, I'm giving you the trigger code and the error code 1442 that says (Can't update table 'purchases' in stored function/trigger because it is already used by statement which invoked this stored function/trigger). That same code I have made in Postgres and it does not give me any problem.
DELIMITER $$
USE `ft_comercial`$$
DROP TRIGGER /*!50032 IF EXISTS */ `tg_pupi`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `tg_pupi` AFTER INSERT ON `compras`
FOR EACH ROW BEGIN
`compras` SET `estado` = 'bb' WHERE `id_compra` <> new.`id_compra` AND `id_producto` = new.`id_producto`;
END;
$$
DELIMITER ;