MySQL trigger before insert replace the data with relational table

Question

I have 2 table orders and transactions

I want to write trigger in orders table, which will replace the value of currently inserting row (orders table INSERT query) with transactions data and also update a flag of transactions table.

---

MySQL query for insert is

INSERT INTO `cl201_orders`(`order_id`, `transaction_id`, `transaction_ammount`, `gateway_id`, `name`, `address`) VALUES ('a23', NULL, NULL, NULL, 'John', 'NZ');

MySQL query for transactions data selection will be

SELECT `transaction_id`, `transaction_ammount`, `gateway_id` 
FROM `transactions` 
WHERE `order_id` = 'a23' 
 AND gateway_id IS NOT NULL 
 AND active = 1 
ORDER BY `transaction_id` ASC
LIMIT 1;

---

Before order Insert

transactions table

transaction_id | order_id | transaction_ammount | gateway_id | active
----------------------------------------------------------------------
123 | a23 | 10 | NULL | 1
252 | a23 | 33 | 23 | 1
321 | a23 | 25 | NULL | 1
431 | 1cd | 50 | 45 | 0

orders table

order_id | transaction_id | transaction_ammount | gateway_id | name | address
-----------------------------------------------------------------------------
1cd | 431 | 50 | 45 | Roy | IN

---

After order Insert

transactions table

transaction_id | order_id | transaction_ammount | gateway_id | active
----------------------------------------------------------------------
123 | a23 | 10 | NULL | 0
252 | a23 | 33 | 23 | 0
321 | a23 | 25 | NULL | 0
431 | 1cd | 50 | 45 | 0

orders table

order_id | transaction_id | transaction_ammount | gateway_id | name | address
-----------------------------------------------------------------------------
1cd | 431 | 50 | 45 | Roy | IN
a23 | 252 | 33 | 23 | John | NZ

---

Table structure:

CREATE TABLE `prefix_orders` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `order_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `customer_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `transaction_ammount` decimal(10,2) NOT NULL DEFAULT '0.00',
 `transaction_id` int(11) unsigned DEFAULT NULL,
 `gateway_id` int(11) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `order_id` (`order_id`),
 KEY `gateway_id` (`gateway_id`),
 KEY `transaction_ammount` (`transaction_ammount`),
 KEY `transaction_id` (`transaction_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `prefix_transactions` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `transaction_id` int(11) DEFAULT NULL,
 `order_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `gateway_id` int(11) DEFAULT NULL,
 `transaction_ammount` double(10,2) DEFAULT NULL,
 `active` tinyint(1) DEFAULT '1',
 PRIMARY KEY (`id`),
 UNIQUE KEY `transaction_id` (`transaction_id`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

---

I can achieve this at PHP end but I'm curious to know how I can do it with trigger

Please Note:

• For The Sake Of Simplicity I have omitted several column from both the tables.
• MySQL version: 10.0.24

• Rick James: INDEX(order_id, active, gateway_id) will help performance. Could we see SHOW CREATE TABLE.
• Raunak Gupta: @RickJames: I have updated my question, please have a look.

Answer 1

It may help you in case of AFTER INSERT.

CREATE TRIGGER trigger_name AFTER INSERT ON orders 
 FOR EACH ROW BEGIN IF (SELECT COUNT(*)
 FROM transaction
 WHERE order_id=NEW.order_id) != 0 
 THEN 
 UPDATE transaction SET active = 0 WHERE order_id=NEW.order_id; 
 END IF; 
END

• Raunak Gupta: thanks for you answer, just one thing that how the data of orders table will be updated/replaced with the data of transactions table? fields like transaction_id , transaction_ammount and gateway_id.
• akumar: You can update multiple columns of transaction tables based on order_id column present in both tables.
• Raunak Gupta: sorry if my question was not clear, Actually in my orders insert query I have set transaction_id to NULL which I want to update/replace it with 252 which is present in transactions table. I'm getting 252 with above mentioned condition like this WHERE order_id = 'a23' AND gateway_id IS NOT NULL AND active = 1 so how to do that.
• Rick James: I don't think you need to do the COUNT(*); simply let the UPDATE do nothing.