sizkT1Vx

1INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())
2	
3CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users`  
4FOR EACH ROW BEGIN
5SET NEW.default_assignment = NEW.userid;
6END;
7	
8CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
9BEGIN
10   DECLARE next_id INT;
11   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
12   SET NEW.field=next_id;
13END
14	
15(*) To auto-name an image:
16SET NEW.field = CONCAT('image_', next_id, '.gif');
17(*) To create a hash:
18SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );
19	
20CREATE TABLE `lritstsequence` (
21  `idsequence` int(11) NOT NULL auto_increment,
22  PRIMARY KEY  (`idsequence`)
23) ENGINE=InnoDB;
24
25CREATE TABLE `lritst` (
26`id` int(10) unsigned NOT NULL auto_increment,
27`bp_nr` decimal(10,0) default '0',
28`descr` varchar(128) default NULL,
29PRIMARY KEY (`id`),
30UNIQUE KEY `dir1` (`bp_nr`)
31) ENGINE=InnoDB;
32
33DELIMITER $$
34
35DROP TRIGGER /*!50032 IF EXISTS */ `lritst_bi_set_bp_nr`$$
36
37CREATE TRIGGER `lritst_bi_set_bp_nr` BEFORE INSERT ON `lritst`
38FOR EACH ROW
39BEGIN
40    DECLARE secuencia INT;
41    INSERT INTO lritstsequence (idsequence) VALUES (NULL);
42    SET secuencia = LAST_INSERT_ID();
43    SET NEW.id = secuencia;
44    SET NEW.bp_nr = secuencia;
45END;$$
46
47DELIMITER ;
48
49INSERT INTO lritst (descr) VALUES ('test1');
50INSERT INTO lritst (descr) VALUES ('test2');
51INSERT INTO lritst (descr) VALUES ('test3');
52
53SELECT * FROM lritst;
54
55Result:
56
57    id   bp_nr  descr 
58------  ------  ------
59     1       1  test1 
60     2       2  test2 
61     3       3  test3
62	
63CREATE TABLE `Temp` (
64  `id` int(11) NOT NULL auto_increment,
65  `value` varchar(255) ,
66  PRIMARY KEY  (`idsequence`)
67) ENGINE=InnoDB;
68
69
70CREATE TRIGGER temp_before_insert BEFORE INSERT ON `Temp`
71FOR EACH ROW 
72BEGIN
73    DECLARE m INT;
74
75    SELECT IFNULL(MAX(id), 0) + 1 INTO m FROM Temp;
76    SET NEW.value = m;
77    -- NOT NEEDED but to be save that no other record can be inserted in the meanwhile
78    SET NEW.id = m;   
79END;