如何使用触发器根据产品详细信息的名称自动增加varchar数据类型？ [英] how to autoincrement varchar datatype depending upon the product detail's name using trigger?
问题描述
我有一个产品详细信息表，表中的字段是 Prod_Id，Product_Name，Quantity，Prefix，Slno 。现在我已经为varchar数据类型字段创建了触发器，并为Prod_Id（主键）和Slno实现了自动增量。代码创建prod_Id，如book_001，Shoe_002，Book_003，Flower_004，shoe_005等。
现在我需要的是，如果产品名称是RACK，它应该创建一个带有开始前缀字段的ID RACK_001（输入将由用户给出产品名称并使用前缀字段自动增加。对于所有产品名称，它应该相应地创建，如何做到这一点。
截至目前我正在使用触发器来自动增加varchar数据类型。 H $>
I have a product details table, the fields in the table are Prod_Id, Product_Name, Quantity, Prefix, Slno. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id (Primary key) and for Slno. the code creates prod_Id like book_001, Shoe_002, Book_003,Flower_004,shoe_005 and so on.
Now what i need is, if the product name is RACK it should create a id RACK_001 with the start Prefix field(input will be given by user) of the product name and auto increment automatically using prefix field. For all Product name it should create accordingly,how to do this.
As of now i am using a trigger for auto increment in varchar data type. H
DELIMITER $$
CREATE TRIGGER tg_product_details_INSERT
BEFORE INSERT ON product_details
FOR EACH ROW
BEGIN
INSERT INTO product_details_seq VALUES (NULL);
SET NEW.Created_Date = NOW();
SET NEW.Submitted_Date = NOW();
SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;
SET NEW.Prod_id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0'));
END
DELIMITER ;
我有concat前缀字段和id字段，所以我得到book_001但我需要一个不同的序列。
产品名称 - >>书，鞋，连衣裙，家具
所有书它应该创建一个自动增量ID BOOK_001，BOOK_002 并且对于所有鞋它应该创建一个自动增量ID，如 SHOE_001，SHOE_002 等等...... !!!
我需要为每个产品自动增量分开（SHOE_001，BOOK_001，SHOE_002，FLOWER_001，BOOK_002，SHOE_003 ....等等）
请帮助解决触发器代码。
谢谢，
Acube。
I have concat prefix field and id field, so i get book_001 but i need a different sequence.
product name -->> BOOK, SHOE, DRESS, FURNITURE
for all book it should create a auto increment id BOOK_001, BOOK_002 and for all Shoe it should create a auto increment id like SHOE_001, SHOE_002 and so on...!!!
I need like for each product the auto increment should be separate(SHOE_001,BOOK_001,SHOE_002,FLOWER_001,BOOK_002,SHOE_003....and so on)
Kindly help with the trigger code.
Thanks,
Acube.
推荐答案
创建 TRIGGER tg_product_details_INSERT
BEFORE INSERT ON product_details
FOR 每行
BEGIN
INSERT < span class =code-keyword> INT O product_details_seq VALUES （ NULL ）;
SET NEW.Created_Date = NOW（）;
SET NEW.Submitted_Date = NOW（）;
SET NEW.Slno = coalesce （（从 product_details中选择 max（Slno）， 0 ）+ 1 ;
SET NEW.Prod_id = CONCAT（（NEW.Prefix），LPAD（LAST_INSERT_ID（）， 3 ，' 0'））;
END
DELIMITER;
CREATE TRIGGER tg_product_details_INSERT BEFORE INSERT ON product_details FOR EACH ROW BEGIN INSERT INTO product_details_seq VALUES (NULL); SET NEW.Created_Date = NOW(); SET NEW.Submitted_Date = NOW(); SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1; SET NEW.Prod_id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0')); END DELIMITER ;
我有concat前缀字段和id字段，所以我得到book_001但我需要一个不同的序列。
产品名称 - >>书，鞋，连衣裙，家具
所有书它应该创建一个自动增量ID BOOK_001，BOOK_002 并且对于所有鞋它应该创建一个自动增量ID，如 SHOE_001，SHOE_002 等等...... !!!
我需要为每个产品自动增量分开（SHOE_001，BOOK_001，SHOE_002，FLOWER_001，BOOK_002，SHOE_003 ....等等）
请帮助解决触发器代码。
谢谢，
Acube。
I have concat prefix field and id field, so i get book_001 but i need a different sequence.
product name -->> BOOK, SHOE, DRESS, FURNITURE
for all book it should create a auto increment id BOOK_001, BOOK_002 and for all Shoe it should create a auto increment id like SHOE_001, SHOE_002 and so on...!!!
I need like for each product the auto increment should be separate(SHOE_001,BOOK_001,SHOE_002,FLOWER_001,BOOK_002,SHOE_003....and so on)
Kindly help with the trigger code.
Thanks,
Acube.
我想日志是得到产品记录的计数（*）
例如，如果你得到3条记录的书然后你的下一个id将是book_00（count（*）+ 1）
如果你的计数（*）= 0这就意味着没有这样的产品。因此你的id将是count（*）+ 1 ie rack_001
I guess the log is to get count(*) of record for the product
e.g for a book if you get 3 records then your next id will be book_00(count(*) + 1)
if for a rack your count(*) = 0 this means there is no such product. Hence your id will be count(*) + 1 ie rack_001
你有解决方案吗？
我有同样的问题
Do you have The solution?
I have The same problem
这篇关于如何使用触发器根据产品详细信息的名称自动增加varchar数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
