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 ;
The problem is that you can't do an INSERT and then an UPDATE for the same table if you want to update some element just use SET :-)
As with a trigger you cannot modify other records of the same table, you will have to find another way to achieve what you are looking for.
Placing the state in the product and not in the purchase, and saving the state
id
of the last purchase, for example, it could be resolved with a trigger.Let's solve it starting from this dataset:
We define the trigger:
And we try to make these inserts:
Since the product status is no longer in
compras
, that information will need to be accessed with this query:Getting something like this:
And, just out of curiosity, why do you need something like that?
There are basically two methods to add records to a table. The first is to add one record at a time, the second is to add multiple records at once. In both cases, the INSERT INTO SQL statement is used to accomplish the task. INSERT INTO statements are often referred to as "append queries."
To add a record to a table, you must use the field list to define which fields to put the data into, and then provide the data in a list of values. To define the list of values, use the VALUES clause. For example, the following statement inserts the values "1", "Kelly", and "Jill" into the CustomerID, Last Name, and First Name fields, respectively.