How to use MYSQL Trigger with Cursor


Want to Insert multiple rows into table "txn_dbtransactionnotification" when new data is inserted/update into the "Txn_Sales" table.
Suppose 5 rows are present into the transaction table "Txn_Sales" and 1 row into the master table "Mst_Sales".
So when Data into the master table "Mst_Sales" is modified then related data into the table "Txn_Sales" must be modified and the update be sent to the table "txn_dbtransactionnotification" so how can I use a Cursor inside a Trigger so when data in table "Mst_Sales" is modified then data into the table "Txn_Sales" must be modified row by row.


Cursor support is incomplete in MySQL’s stored routine language. It does not support UPDATE ... WHERE CURRENT OF CURSOR like some other brands of SQL implementation.

But in many cases, it’s not necessary to update row by row. It’s simpler to update a batch. Think of updating sets of rows, instead of row by row.

For example:

  -- update a set of rows related to the same product that
  -- spawned this trigger. 
  UPDATE Txn_Sales SET price = NEW.price WHERE product = NEW.product;

  -- and enter a record in the notifications table
  INSERT INTO txn_dbtransactionnotification ...;

I had to imagine what type of update you were talking about, because you described the problem in such an abstract way. In a real-life scenario, I don’t think changing the price of a product would apply retroactively to past transactions. So the example above is only to show the technique, not the real code you would use.

Answered By – Bill Karwin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published