0

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
  • INDEX(order_id, active, gateway_id) will help performance. Could we see SHOW CREATE TABLE. – Rick James Jun 4 '17 at 0:34
  • @RickJames: I have updated my question, please have a look. – Raunak Gupta Jun 5 '17 at 7:34
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
  • 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. – Raunak Gupta Jun 1 '17 at 13:12
  • You can update multiple columns of transaction tables based on order_id column present in both tables. – akumar Jun 1 '17 at 13:24
  • 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. – Raunak Gupta Jun 1 '17 at 15:06
  • I don't think you need to do the COUNT(*); simply let the UPDATE do nothing. – Rick James Jun 6 '17 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.