MySQL trigger set values for NEW row and update another in the same table

mysql trigger after insert, update another table
mysql trigger before update
how to write after insert event update trigger on the same table in mysql
mysql trigger after update
mysql trigger example
mysql trigger before delete
mysql trigger update same table
mysql trigger if

I have a table that I keep track of fees for a specific item. These fees can change over time so I have two columns (startDate, endDate) with the current set of fees always having an endDate in the far future. I already have a trigger that I use to do some calculations on the new row being entered but what I also want to have happen is if I enter an item that already has an entry I want to set the previous entry's endDate to the day before the new entry's startDate and the new endDate to a predetermined far-away date. Here is the code for what I tried first:

CREATE
DEFINER=`root`@`%`
TRIGGER `im`.`splitBeforeIns`
BEFORE INSERT ON `im`.`split`
FOR EACH ROW
BEGIN
    SET NEW.tcPercent = (NEW.tcOfficeFee / NEW.globalFee) * 100 , NEW.proPercent = 100 - NEW.tcPercent, NEW.endDate = 20501231;
    UPDATE im.split set endDate = ADDDATE(NEW.startDate, -1) where procKey = NEW.procKey AND endDate = 20501231;
END$$

The error I get is:

ERROR 1442: Can't update table 'split' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The answer to this might be unwelcome, but it is: You can't do that.

A Trigger can't update another row of the same table as the row, the trigger was called from.

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

The trigger acts as an accumulator, summing the values inserted into one of the To use the trigger, set the accumulator variable to zero, execute an INSERT definition defines another BEFORE INSERT trigger for the account table: use a trigger to modify the values to be inserted into a new row or used to update a row. The following shows the syntax of creating a MySQL AFTER UPDATE trigger: CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW trigger_body. In this syntax: First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause. Second, use AFTER UPDATE clause to specify the time to invoke the trigger.

If you have a UNIQUE KEY defined on (procKey,EndDate), then perhaps you can remove the second line of the trigger. Also remove the hardcoded date from the trigger.

CREATE
DEFINER=`root`@`%`
TRIGGER `im`.`splitBeforeIns`
BEFORE INSERT ON `im`.`split`
FOR EACH ROW
BEGIN
    SET NEW.tcPercent = (NEW.tcOfficeFee / NEW.globalFee) * 100 , NEW.proPercent = 100 - NEW.tcPercent;
END$$

and do an INSERT ON DUPLICATE KEY UPDATE like this:

INSERT INTO im.split ...
ON DUPLICATE KEY UPDATE
endDate = ADDDATE(startDate, -1);

You may also want to define endDate in im.split as follows

enddate DATE DEFAULT '2050-12-31'

Changing the value on a different table is giving me no problem, so I will exclude AFTER UPDATE, I want my trigger to SET the URL of any row to the URL of the NEW row IF that row's Parent is equal to the NEW row's ID this limitation in Mysql, by calling a stored procedure that does the actual update. A Trigger can't update another row of the same table as the row, the trigger was called from. The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

I managed to get it to work, by creating a "wrapper table" which is defined with the same fields as your target table, but by using the FEDERATED storage engine. The federated server I defined to target the same mysql server/itself, so at "localhost". The trigger I then made change a row in the wrapper table. Of course you have to be very sure what you do in the trigger, to avoid a recursive loop. Also potentially the performance isn't that good; I haven't tested performance, but it has been working for years in a production environment.

Third, specify the name of the table to which the trigger belongs after the ON keyword. In a AFTER UPDATE trigger, you can access OLD and NEW rows but cannot Finally, use the IF-THEN statement inside the trigger body to check if the new value is not the same as UPDATE Sales SET quantity = 350 WHERE id = 1;. The following shows the basic syntax of creating a MySQL AFTER INSERT trigger: CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW trigger_body. In this syntax: First, specify the name of the trigger that you want to create after the CREATE TRIGGER keywords.

From the MySQL Docs:

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.

You will need to find some other way of doing what you're trying to do.

In an AFTER INSERT trigger, you can access the NEW values but you cannot Second, create another table called reminders that stores reminder message) VALUES(new.id,CONCAT('Hi ', NEW.name, ', please update your date of birth. value NULL , therefore, the trigger inserted only one row into the reminders table. In a BEFORE UPDATE trigger, you can update the NEW values but cannot update the OLD values. MySQL BEFORE UPDATE trigger example. Let’s look at an example of using a BEFORE UPDATE trigger. Setting up a sample table. First, create a new table called sales to store sales volumes: DROP TABLE IF EXISTS sales; CREATE TABLE sales (

MySQL is just returning: Can't update table 'city' in stored function/trigger CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city` FOR EACH ROW SET NEW.country_id = (SELECT CASE WHEN NEW.country_id = 0 If NEW.country value is absent in country table then anycase inserted data is wrong. I need to Update the rows from table company_id to table places when insert a new row on table company Example, if i insert a new row to Offer_company values as ('1', CSB(pvt)limited, srilanka,123) then the the trigger should to UPDATE the rows from table company_id to table places Places_id select form places table places table

CREATE TRIGGER creates a new trigger in MySQL. A trigger is a set of actions that are run automatically when a specified This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. Now insert one record in emp_details table see the records both in  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.

But you can also update whole sets of records at once, and in very powerful ways​. For DB2 and MySQL you have the option of inserting one row at a time or multiple rows at a The INSERT statement allows you to create new rows in database tables. You wish to update rows in one table using values from another. After playing earlier with triggers, I wanted to see whether one can update another table using MySQL triggers. I tried something like CREATE TRIGGER mytrigger BEFORE INSERT ON odp.points FOR EACH ROW SET total_points.points = total_points.points + NEW.points;

Comments
  • Try doing your update in an AFTER INSERT trigger.
  • @BrianDriscoll I tried that :-( I got the same error.
  • The thing is that you can't explicitly update the target of your trigger from within the trigger. You might have to find another way to go about your task.
  • Please show the following : 1) SHOW CREATE TABLE im.split\G and 2) a sample INSERT command into im.split.
  • @RolandoMySQLDBA I am not sure how this helps . . . according to the answers below, it is not possible to update another row in the same table with a trigger. Do you think it is possible?
  • Thank you for suggesting a way for me to achieve what I was going for.