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

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