how to update samehost database using trigger from

问题:

I need to update SAME HOST database using trigger

CREATE TRIGGER `StudentMaster` BEFORE INSERT ON `student_master`
FOR EACH
ROW INSERT INTO xxxxxx`.`xxxxx`
SELECT *
FROM `student_master`
ORDER BY `SM_NO DESC LIMIT 1` 

Above ex is what i have tried ?

回答1:

You can try the following:

/*Table structure for table `db1`.`student_master` */

DROP TABLE IF EXISTS `db1`.`student_master`;

CREATE TABLE `db1`.`student_master` (
  `sm_no` INT(10) UNSIGNED NOT NULL,
  `column0` VARCHAR(50) DEFAULT NULL,
  `column1` VARCHAR(50) DEFAULT NULL,
  `column2` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`sm_no`)
) ENGINE=INNODB;

/*Table structure for table `db2`.`student_master` */

DROP TABLE IF EXISTS `db2`.`student_master`;

CREATE TABLE `db2`.`student_master` (
  `sm_no` INT(10) UNSIGNED NOT NULL,
  `column0` VARCHAR(50) DEFAULT NULL,
  `column1` VARCHAR(50) DEFAULT NULL,
  `column2` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`sm_no`)
) ENGINE=INNODB;

/* Trigger structure for table `db1`.`student_master` */

DELIMITER $$

DROP TRIGGER IF EXISTS `db1`.`trg_ai_sm`$$

CREATE TRIGGER `db1`.`trg_ai_sm` AFTER INSERT ON `db1`.`student_master`
FOR EACH ROW
BEGIN
    INSERT INTO `db2`.`student_master` (`sm_no`, `column0`, `column1`, `column2`)
    SELECT NEW.`sm_no`, NEW.`column0`, NEW.`column1`, NEW.`column2`;
END$$

DELIMITER ;

Test:

INSERT INTO `db1`.`student_master` (`sm_no`, `column0`, `column1`, `column2`)
VALUES
(1, 'column0', 'column1', 'column2');