Re: Copy auto-increment value to another column on insert
Hi Larry,
An option is:
SELECT VERSION();
Result:
VERSION()
-------------------
5.0.45-community-nt
CREATE TABLE `lritstsequence` (
`idsequence` int(11) NOT NULL auto_increment,
PRIMARY KEY (`idsequence`)
) ENGINE=InnoDB;
CREATE TABLE `lritst` (
`id` int(10) unsigned NOT NULL auto_increment,
`bp_nr` decimal(10,0) default '0',
`descr` varchar(128) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `dir1` (`bp_nr`)
) ENGINE=InnoDB;
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `lritst_bi_set_bp_nr`$$
CREATE TRIGGER `lritst_bi_set_bp_nr` BEFORE INSERT ON `lritst`
FOR EACH ROW
BEGIN
DECLARE secuencia INT;
INSERT INTO lritstsequence (idsequence) VALUES (NULL);
SET secuencia = LAST_INSERT_ID();
SET NEW.id = secuencia;
SET NEW.bp_nr = secuencia;
END;$$
DELIMITER ;
INSERT INTO lritst (descr) VALUES ('test1');
INSERT INTO lritst (descr) VALUES ('test2');
INSERT INTO lritst (descr) VALUES ('test3');
SELECT * FROM lritst;
Result:
id bp_nr descr
------ ------ ------
1 1 test1
2 2 test2
3 3 test3
Subject
Views
Written By
Posted
17057
December 03, 2007 01:23PM
Re: Copy auto-increment value to another column on insert
8230
December 03, 2007 06:35PM
4399
December 04, 2007 09:36AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.