Mysql - Trigger for Updating Same Table After Insert

MySQL - Trigger for updating same table after insert

It seems that you can't do all this in a trigger. According to the documentation:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

According to this answer, it seems that you should:

create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

With a stored proc you'll manually commit the changes (insert and update). I haven't done this in MySQL, but this post looks like a good example.

MySQL updating same table after insert

Each time you insert a new male record, it is necessary to fill the female counterpart. However, this should be executed only if a new record was actually created (reason why after trigger was chosen).

The requirement for the change to be applied only if the row is created is unnecessary, because the row won't be created if it violates a constraint, regardless of whether you modify a value in the NEW row or not.

As @juergend commented above, change the female column with a SET statement in a BEFORE INSERT trigger.

BEGIN    
    SET NEW.`female` = CASE NEW.`male` WHEN 'Peter' THEN 'Brenda' END;
END

If the INSERT is a primary key violation, the whole INSERT will be cancelled, and the whole row you tried to insert will be discarded, along with the modified value set in the BEFORE INSERT trigger.

If the INSERT is not a primary key violation, the row will be inserted successfully, and it will include the value you set in the BEFORE INSERT trigger.

You cannot change column values in an AFTER trigger.

https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html says:

In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

(emphasis mine)

---

You seem to be focused on avoiding assigning a value to the female column, so I guess it's not just a simple assignment of 'Brenda'. I guess you are concerned because it must be a costly SQL query.

I suggest in that case that you need to make it a quick SQL query. Optimize whatever lookup you need to do to find the value of 'Brenda' so it's not a big overhead, then call that from your BEFORE trigger.

The only other option is to leave the female column alone, or else set it to some placeholder value. Then after the INSERT is confirmed successful by your application (and rows-affected is 1), then issue an UPDATE to set the female column's value.

But you can't do it in an AFTER trigger, full stop.

Updating table in trigger after update on the same table

If you change your trigger to BEFORE instead of AFTER you could do it like this:

CREATE TRIGGER upd_total_votes BEFORE UPDATE ON products_score 
FOR EACH ROW 
BEGIN
    SET new.votes_total = new.votes_1 + new.votes_2 + new.votes_3 + new.votes_4 + new.votes_5 
END
;

AFTER INSERT trigger on same table

You can not update the same table where the trigger is being executed.
So you need to use before insert

delimiter //
create trigger table_before_insert before insert on table_name
for each row
begin
 set new.end_time = date_add(/*your increment logic here */);
end;//

delimiter;

you can access all the values using new and use them inside the date_add function as needed.

Insert into same table trigger mysql

Why don't you just change your INSERT code into something like this? :

INSERT INTO table1 (field1, field2, ...) 
VALUES ( @item, @price, ....)
     , ( @item, @discount, ...) ;

---

Another thing would be to re-evaluate your data structure. The way it is now, it seems - from the limited information you have provided - that it's not normalized. You are storing two types of info in the table.

Perhaps you can combine the two rows (that are to be inserted every time) into one, by adding a few columns in the table.

Or by splitting the table into two tables, one for the "normal" item rows and one for the discount items.

MySQL update TRIGGER on the same table

Your trigger could be like this:

DELIMITER //

CREATE TRIGGER persons_updt BEFORE UPDATE ON persons
FOR EACH ROW
  IF (NEW.age <=> OLD.age) THEN SET NEW.Update_date=current_date();
END IF//

DELIMITER ;

Here I'm using the null-safe operator <=>. Please see a fiddle here.

---

Related Topics