Home >Database >Mysql Tutorial >How Can I Update the Same Table After an Insert Without Trigger Errors?

How Can I Update the Same Table After an Insert Without Trigger Errors?

Barbara Streisand
Barbara StreisandOriginal
2024-12-10 22:22:10750browse

How Can I Update the Same Table After an Insert Without Trigger Errors?

Trigger for Updating Same Table after Insert: An Alternative Approach

Issue Overview

You seek to implement a trigger on the ACCOUNTS table that updates a column in the same table based on the edit_on value of a newly inserted row. However, you encounter an error due to the restriction against updating the same table within a trigger that invoked it.

Alternative Workaround: Stored Procedure

To circumvent this limitation, a stored procedure can be employed to perform both the insert and update operations within a single transaction. This approach allows for manual commit of changes, enabling the desired updates.

Stored Procedure Implementation

Here's an example stored procedure that achieves your goal:

DELIMITER $$
CREATE PROCEDURE InsertAndEditStatus(IN new_pk BIGINT)
BEGIN
  -- Insert new row into ACCOUNTS
  INSERT INTO ACCOUNTS (pk, user_id, edit_on, status)
  VALUES (new_pk, /* user_id */, NULL, 'A');
  
  -- Update existing row in ACCOUNTS
  UPDATE ACCOUNTS SET status='E' WHERE pk = NEW.edit_on;
  
  -- Commit transaction
  COMMIT;
END$$
DELIMITER ;

Usage

To use this stored procedure, call it with the primary key of the newly inserted row as an argument:

CALL InsertAndEditStatus(2147483726);

This procedure will insert a new row with the specified primary key and default values, then update the existing row referenced by the edit_on column in the newly inserted row to indicate that it has been edited.

The above is the detailed content of How Can I Update the Same Table After an Insert Without Trigger Errors?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn