Healthcare IT EMR PMS: MySQL Trigger Before Insert example

Here is an small example how to create trigger in MySQL after inserting record into table.
This examples how to generate random password in the MySQL and update the user name in the Before insert trigger.

Step 1 :
Let us create a small table with three fields such as ID, loginName and password. Here is the script for the same;

DROP TABLE IF EXISTS `appusers`;

CREATE TABLE `appusers` (
  `ID` bigint(20) NOT NULL auto_increment,
  `loginName` varchar(20) default NULL,
  `password` varchar(100) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2 :
Let us create a stored procedure which will return random password using MySQL RAND,ROUND and MOD function.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `getPassword`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getPassword`(OUT _password VARCHAR(7))
BEGIN
	DECLARE COUNT INT DEFAULT 0;
	DECLARE alphanum INT;
	DECLARE randomCharacter CHAR(1);
	DECLARE PASSWORD VARCHAR(10) DEFAULT "";
	
	WHILE COUNT<7 DO
		SET COUNT=COUNT+1;
		SELECT ROUND(RAND()*10) INTO alphanum;

		IF alphanum<5 THEN
			#Generate a random digit
			SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
		ELSE
			#Generate a random digit
			SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
		END IF;
		#CONCAT function concatenates two or more strings and returns result
		SELECT CONCAT(PASSWORD,randomCharacter) INTO PASSWORD;
	END WHILE;
	SET _password=PASSWORD;
    END$$

DELIMITER ;

Step 3 :
Here is trigger

DELIMITER $$


DROP TRIGGER /*!50032 IF EXISTS */ `updatepassword`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `updatepassword` BEFORE INSERT ON `appusers` 

    FOR EACH ROW BEGIN
	 DECLARE varPassword VARCHAR(100);
	 CALL getPassword(varPassword);
	 SET NEW.password = varPassword;
    END;
$$

DELIMITER ;

Now you can test by inserting record as follows:
INSERT INTO appusers(loginName) VALUES('John1');

X12 EDI Examples

MySQL Trigger Before Insert example