kc3dA8NU

1USE Test;
2
3DELIMITER $$
4CREATE DEFINER=`root`@`localhost` FUNCTION `RandString`(length SMALLINT(3)) RETURNS varchar(100) CHARSET utf8
5BEGIN
6SET @returnStr = '';
7    SET @allowedChars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890';
8    SET @i = 0;
9
10    WHILE (@i < length) DO
11        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
12        SET @i = @i + 1;
13    END WHILE;
14
15    RETURN @returnStr;
16END$$
17DELIMITER ;
18
19
20CREATE TABLE `Company` (
21  `CompanyId` int NOT NULL AUTO_INCREMENT,
22  `CompanyCode` varchar(25) NOT NULL,
23  `CompanyName` varchar(50) NOT NULL,
24  `CustomerOrderPrefix` varchar(10) DEFAULT NULL,
25  `CollectionName` varchar(50) NOT NULL,
26  `IsStoreTabVisible` bit(1) NOT NULL DEFAULT b'1',
27  `IsDifferentStoreTabVisible` bit(1) NOT NULL DEFAULT b'1',
28  `License` mediumtext,
29  `DateCreated` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
30  `CreatedBy` varchar(256) NOT NULL,
31  `DateModified` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
32  `ModifiedBy` varchar(256) NOT NULL,
33  PRIMARY KEY (`CompanyId`),
34  UNIQUE KEY `ix_companycode` (`CompanyCode`),
35  UNIQUE KEY `ix_companyname` (`CompanyName`)
36) ENGINE=InnoDB DEFAULT CHARSET=latin1;
37
38
39
40delimiter |
41
42CREATE TRIGGER before_insert_company
43  BEFORE INSERT ON Company
44  FOR EACH ROW
45  BEGIN
46    declare ready int default 0;
47    declare rnd_str text;
48    if new.CompanyCode IS NULL THEN
49		while not ready do
50			set rnd_str := RandString(9);
51			if not exists (select * from Company where CompanyCode = rnd_str) then
52				set new.CompanyCode = rnd_str;
53				set ready := 1;
54			end if;
55		end while;
56    end if;    
57	
58  END;
59  
60  delimiter ;