CODEWITHSUNDEEP
mysqldatabasetriggers
ajsie
ajsie

Reputation: 79686

Get the latest inserted id in a trigger?

I use a trigger to insert a row and want to use the last created id for using in the subsequent query.

How could I do this?

The code looks like:

BEGIN
IF (NEW.counter >= 100) THEN
INSERT INTO tagCategories (name, counter) VALUES ('unnamed', NEW.counter);
// here i want to have access to the above inserted id
UPDATE tagCategories2tagPairs SET tagCategoryId = <<ID_HERE>> WHERE tagPairId = OLD.id
END IF;
END

Upvotes: 8

Views: 29702

Answers (6)

Mohammed_Moulla
Mohammed_Moulla

Reputation: 55

you can pass the index of the inserted row from the server (the loop index) and in the trigger "before insert" you can do the following :

BEGIN
if (NEW.id=0) then
set @A = (SELECT AUTO_INCREMENT
     FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = "schema"
    AND TABLE_NAME = "table");
    end if;
SET NEW.id  = NEW.id + @A ;
END

let we assume that LAST_INSERT_ID() = 5

and you are trying to add 3 rows at the same insert statement ...

so , the first row would have 5 + 0

second : 5 + 1

third : 5 + 2

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300559

Have you looked at LAST_INSERT_ID()? But be aware:

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.

Upvotes: 8

Антон Баранов
Антон Баранов

Reputation: 433

SET NEW.num=CONCAT("А-", (
  SELECT `auto_increment` 
  FROM INFORMATION_SCHEMA.TABLES    
  WHERE table_name = 'menu')
)

Upvotes: -1

user3785966
user3785966

Reputation: 2970

Following trigger will get the last Auto Increment value from the Information Schema. I had written this trigger to generate a slug.

For instances, if the column's name is "Name" and column has a value such as "Robin Shankar", the trigger would replace spaces with "-" and also append the Auto Increment Id to the end of the slug, hence making a unique slug.

robin_shankar_9071

DELIMITER //
CREATE TRIGGER insert_slug_sd
BEFORE INSERT ON `your_table_name`
FOR EACH ROW
BEGIN

DECLARE auto_increment_ INT(11);

SELECT 
    `auto_increment` 
INTO 
    auto_increment_
FROM INFORMATION_SCHEMA.TABLES
    WHERE 
table_name = 'your_table_name';

SET NEW.`Slug` = CONCAT(LOWER(REPLACE(NEW.`Name`,' ','-')),'-',auto_increment_);
END; //

DELIMITER;

Upvotes: -1

Darkhan ZD
Darkhan ZD

Reputation: 610

USE NEW.id

BEGIN
insert INTO test_questions (test_id, question, variant1, variant2, variant3, w1, type_id) 
SELECT NEW.id, t1.question, t1.v1, t1.v2, t1.v3, t1.answer, 1
FROM new_minitest_questions t1
WHERE t1.mt_id = NEW.old_id;
END

Upvotes: -1

AtlantaKid
AtlantaKid

Reputation: 113

I wanted to use a company initials like "AAA" and add the insert ID to it and use it as a internal company ID and this is how I picked up the last current insert ID

DELIMITER //
CREATE TRIGGER company_run_before_insert BEFORE INSERT ON ap_company 
FOR EACH ROW
BEGIN

SET @lastID = (SELECT id FROM ap_company ORDER BY id DESC LIMIT 1);
IF @lastID IS NULL OR @lastID = '' THEN
    SET @lastID = 0;
END IF;
SET @lastID = @lastID +1;
SET NEW.ap_company_id = concat(NEW.company_initials,'-', @lastID);
END;

Upvotes: 3

Related Questions