List:General Discussion« Previous MessageNext Message »
From:Jesper Wisborg Krogh Date:January 22 2011 1:31am
Subject:Re: CURRENT insert ID
View as plain text  
Hi,

On 22/01/2011, at 11:27 AM, Donovan Brooke wrote:

> Just an idear..
> 
> Don't auto_increment the main table.. create a unique Id table, auto_increment that,
> and grab that value first for use with both fields in your main table.

This can be wrapped into a trigger, so the main table functions as usual:

CREATE TABLE _sequence (
   Name varchar(20) NOT NULL PRIMARY KEY,
   Value INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE dupkey (
   id INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY,
   DupKey INT UNSIGNED NOT NULL DEFAULT 0,
   Value VARCHAR(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB;

INSERT INTO _sequence
VALUES ('dupkey', 0);

DELIMITER //
CREATE TRIGGER befins_dupkey BEFORE INSERT ON dupkey FOR EACH ROW
BEGIN
  DECLARE v_id INT UNSIGNED;
  
  UPDATE _sequence SET Value = (LAST_INSERT_ID(Value+1)) where name = 'dupkey';
  SET NEW.id     := LAST_INSERT_ID(),
      NEW.DupKey := LAST_INSERT_ID();
END//
DELIMITER ;

INSERT INTO dupkey (Value)
VALUES ('test 1'), ('test 2');

SELECT * FROM dupkey;
+----+--------+--------+
| id | DupKey | Value  |
+----+--------+--------+
|  1 |      1 | test 1 |
|  2 |      2 | test 2 |
+----+--------+--------+
2 rows in set (0.00 sec)

Cheers,
Jesper

> 
> Donovan
> 
> 
> -- 
> D Brooke
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 


Thread
CURRENT insert IDJerry Schwartz21 Jan
  • Re: CURRENT insert IDjoao21 Jan
    • Re: CURRENT insert IDDarryle Steplight21 Jan
  • Re: CURRENT insert IDjoao21 Jan
    • RE: CURRENT insert IDJerry Schwartz21 Jan
      • Re: CURRENT insert IDDonovan Brooke22 Jan
        • Re: CURRENT insert IDJesper Wisborg Krogh22 Jan
        • RE: CURRENT insert IDJerry Schwartz24 Jan
          • Re: CURRENT insert IDMark Goodge24 Jan
      • Re: CURRENT insert IDJaime Crespo Rinc√≥n24 Jan
  • Re: CURRENT insert IDCarsten Pedersen23 Jan
    • Re: CURRENT insert IDCarsten Pedersen23 Jan
    • RE: CURRENT insert IDJerry Schwartz24 Jan
Re: CURRENT insert IDMichael Dykman21 Jan
  • RE: CURRENT insert IDJerry Schwartz21 Jan
    • RE: CURRENT insert IDJerry Schwartz21 Jan
      • RE: CURRENT insert IDJerry Schwartz21 Jan
      • Re: CURRENT insert IDMichael Dykman21 Jan
        • RE: CURRENT insert IDJerry Schwartz21 Jan