Solved

Trigger in My SQL

Posted on 2014-11-19
4
164 Views
Last Modified: 2014-12-05
I have a website where the Users enter the product information by Single entry or through the batch upload of CSV file. In both cases the records get inserted to the same table which is in My SQL database.
The information is then retrieved and shown in the website too.  There is a product id column which has 20 characters. But when we are displaying it in the website we show only the first 10 records. As the next 10 are critical and is not displayed. Though the product ID is a unique column the first 10 characters are not unique. So I will be attaching product id +0001 when the information is displayed outside. So I prefer to insert a column in the existing table. The column will get update with product id +0001 on the first entry. Whenever there is a duplicate record gets inserted in the product id by matching the first 10 characters the sequence should be predicted +0002.
So I need a trigger and sequence code in mysql for this. Please help.
0
Comment
Question by:karthik80c
  • 2
4 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40452295
Please rephrase your question. Post a concise and complete example. Include sample data..
0
 
LVL 7

Expert Comment

by:Phil Davidson
ID: 40463819
Is the product ID numeric or alphanumeric?  You want a trigger to fire on an insert to the table or an update to the table or both events?  When you say "attaching +0001," do you mean to append to the value of product ID four characters of "0001"?  I want to make sure you aren't talking about prepending it or mathematically adding the value of 0001 to the ID.

This gives information about creating triggers:

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
0
 

Author Comment

by:karthik80c
ID: 40467277
Dear Phil
The product ID is alphanumeric.
The situation is like this

Product : Apple
Product ID : UID903456790090

Product : Orange
Product ID :UID903456790093

These two products have the first 10 character same.
I am displaying only the first 10 to the customer for some reason.
When I am displaying it if the customer calls in and says the product ID he will be able to say only the first 10
So my search string on the database will retrieve two records.
So am planning to attach a sequence to the product ID with 0001 for the first record and any duplicate product ID for the first 10 character will have it 0002. I will be creating a separate column for this sequence and while displaying it in the website I will be attaching them.
0
 
LVL 7

Accepted Solution

by:
Phil Davidson earned 500 total points
ID: 40468138
There will be a third column have the 0001 and 0002 etc. values.  I don't think you want the trigger to fire on insert but not update.

Let's assume your table is called coolTable and the column for the 0001 and 0002 values is called appendVal.

create trigger coolEE AFTER INSERT on coolTable
for each row
SET new.appendVal = 
select LPAD('(select max(appendVal) from coolTable 
where new.ProductID = ProductID) + 1)', 4, '000')

Open in new window



Notes:
LPAD is a function based on the concept of "left padding" a numeric value with 0s.  This way you get the desired leading zeroes.

There is a max function which is an aggregate function.  This can be a problem with performance if you have a high workload on your database server.  Lots of triggers can degrade performance too.  So this is ok for a smaller environment.  But this wouldn't work for a high volume OLTP database.

ANSI_NULLS need to be set to off because the trigger relies on nulls participating in the arithmetic as zeroes.  Upon not finding an appendVal associated with the productID (e.g., the first insertion of a row with such a productID), the max function will return a null.  

I believe this satisfies what you want.  How do you like this response?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Clean text to insert in database 9 50
Whether to use true/false, yes/no or 0/1 11 50
mysql joining from the same table 6 29
PHP: concatenate query 12 30
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now