获取触发器中最新插入的id?
我使用触发器插入一行,并希望使用最后创建的 id 以便在后续查询中使用。
我怎么能这样做呢?
代码如下:
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
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
您是否看过 LAST_INSERT_ID()< /a>?但请注意:
Have you looked at LAST_INSERT_ID()? But be aware:
我想使用公司首字母缩写(如“AAA”)并向其中添加插入 ID,并将其用作内部公司 ID,这就是我获取最后一个当前插入 ID 的方法
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
您可以从服务器传递插入行的索引(循环索引)
在触发器“插入之前”中,您可以执行以下操作:
假设 LAST_INSERT_ID() = 5
并且您尝试在同一插入语句中添加 3 行...
因此,第一行将有 5 + 0
秒: 5 + 1
第三次 : 5 + 2
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 :
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
使用新的.id
USE NEW.id
以下触发器将从信息架构中获取最后的自动增量值。我编写了这个触发器来生成一个 slug。
例如,如果列的名称是“Name”并且列具有诸如“Robin Shankar”之类的值,则触发器会将空格替换为“-”,并将自动增量 ID 附加到 slug 的末尾,从而生成唯一的蛞蝓。
robin_shankar_9071
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