0
DELIMITER $$
CREATE TRIGGER `merchant_insert` BEFORE INSERT ON `merchant_table` FOR EACH ROW 
BEGIN
  INSERT INTO merchant_seq VALUES (NULL);
  SET NEW.merchant_id = CONCAT('MC', LPAD(LAST_INSERT_ID(), 10, '0'));
END $$
DELIMITER ;
  • 1
    Please consider elaborating on what's happening and how you can see the trigger is not firing, so that the issue is clear. – Andriy M Nov 30 '17 at 8:20
0
INSERT INTO merchant_seq VALUES (NULL);
SET @last_ID = (SELECT MAX(merchant_id) FROM merchant_seq);
SET NEW.merchant_id = CONCAT('MC', LPAD(@last_ID, 10, '0'));
  • I want to add new.merchant_id for "merchant_table", merchant_id is not for merchant_seq. "merchant_seq" table is used for auto increment. CREATE TABLE merchant_seq ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB CREATE TABLE tbl_m_merchant ( merchant_id varchar(12) NOT NULL, created_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (merchant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; – Su Myat Mon Nov 30 '17 at 6:57
  • I updated my answer. – tatskie Nov 30 '17 at 7:25

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.