MySQL Forums
Forum List  »  Triggers

Reserved IDs
Posted by: Nikolay Stoyanov
Date: October 06, 2014 03:08AM

I have a table with email templates named emails. There are system templates an user templates. All system templates are with ID <= 1000. All user templates have ID > 1000. I do this manually: when inserting system templates I set an ID for them in the SQL query. There is and another table named emails_i18n table where for each record from the emails table there is a record for each supporder language. I made a trigger, which changes the ID when inserting into emails table:

CREATE TRIGGER emails_reserve_id BEFORE INSERT ON emails
FOR EACH ROW
BEGIN
IF NEW.id IS NULL OR NEW.id='' THEN
IF NEW.`type` = 'system' THEN
SELECT MAX(id) INTO @new_id
FROM emails
WHERE id<1000;
ELSE
SELECT MAX(id) INTO @new_id
FROM emails
WHERE id>1000;
END IF;
IF @new_id IS NULL THEN
SET @new_id := 1001;
ELSE
SET @new_id := @new_id + 1;
END IF;
SET NEW.id = @new_id;
END IF;
END;

but sometimes there are sets of queries like this:

INSERT INTO `emails` ...;
INSERT INTO `emails_i18n` (id, ...) VALUES
(LAST_INSERT_ID(), ...),
(LAST_INSERT_ID(), ...);

and then the value returned from LAST_INSERT_ID() is not the one set by the trigger.

I changed the way setting the new ID like this:
SET NEW.id = LAST_INSERT_ID(@new_id);
but I found that this:
LAST_INSERT_ID(@new_id)
will update the last insert id only for the trigger and this is why when calling it for `emails_i18n` it doesn't returns the correct id.

Is there any way to reserve IDs in MySQL?

Options: ReplyQuote


Subject
Views
Written By
Posted
Reserved IDs
2330
October 06, 2014 03:08AM
1184
October 06, 2014 11:25AM


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.