September 3, 2014 at 7:34 am
I have a table and it contains company name,address and city information. I would like to generate ID through Trigger for below conditions.
Company name (First three character)
City (First Three character)
&
two digit sequence No (0-99)
When data comes for append then we need to search the company+city+zip exists in table or not. If exists then we dont want to insert the same. If not exists then we need to insert the data with above logic.
Eg:
Company name : Dimentions Media
City : New York
ID would be DIMNEW01
(If the next record comes same company and same city and did not match the zip then we need to append the next entry like this DIMNEW02)
Please help me to create the Trigger above conditions.
September 3, 2014 at 8:47 am
joduvil (9/3/2014)
I have a table and it contains company name,address and city information. I would like to generate ID through Trigger for below conditions.Company name (First three character)
City (First Three character)
&
two digit sequence No (0-99)
When data comes for append then we need to search the company+city+zip exists in table or not. If exists then we dont want to insert the same. If not exists then we need to insert the data with above logic.
Eg:
Company name : Dimentions Media
City : New York
ID would be DIMNEW01
(If the next record comes same company and same city and did not match the zip then we need to append the next entry like this DIMNEW02)
Please help me to create the Trigger above conditions.
Don't do this to yourself. Your ID is not a good direction at all. What happens when the company name changes or they move? Now you have a very cryptic key that doesn't match the data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2014 at 8:57 am
Thanks Sean for your reply. We know the requirement is quite odd because if there is any spelling mistake or abbreviations is used in the company name it wont match against the existing one. We did share these obligations with client but they forcing me to implement with this logic ASAP. Kindly help me out.
September 3, 2014 at 9:14 am
Since you have to proceed with this I would look into using a computed column instead of trying to do this in a trigger. Is this value supposed to be dynamic and change with the data or only during insert?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2014 at 9:32 am
Thanks again.
Yes It is dynamic value and the ID will change during insert according to above conditions.
There are lots of end users who were inserting data into this table in different ways like manually, through application etc., so I thought TRIGGER is the only solution for validate the data first (exists or not in the table) before generating the ID. Also I am not aware of using computed column instead of Trigger.
September 3, 2014 at 9:36 am
joduvil (9/3/2014)
Thanks again.Yes It is dynamic value and the ID will change during insert according to above conditions.
There are lots of end users who were inserting data into this table in different ways like manually, through application etc., so I thought TRIGGER is the only solution for validate the data first (exists or not in the table) before generating the ID. Also I am not aware of using computed column instead of Trigger.
When you say inserting manually do you mean you have users regularly running insert statements themselves or do you mean through an application?
I realize the value for ID will be different for each row. What I mean is should the value change when other columns values change?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2014 at 9:51 am
The logic seems straight forward enough at least to check for the company ID, as pointed out though some of the other flaws with this such as what happens when that sequence on the end hits 99?
But the logic is straight forward enough.
CREATE TABLE #test
(
company_name varchar(30),
city varchar(30),
ID varchar(8)
)
INSERT INTO #test VALUES('asdfgh', 'fghdfgh', 'ASDFGH05')
DECLARE @search varchar(30)
DECLARE @new_id varchar(30)
DECLARE @max-2 int
SET @search = UPPER(LEFT('asdfgh' + ' ', 3)) + UPPER(LEFT('fghdfgh' + ' ', 3))
IF EXISTS(SELECT * FROM #test WHERE SUBSTRING(ID, 1, 6) LIKE @search)
BEGIN
SET @max-2 = (SELECT MAX(CAST(SUBSTRING(ID, 7, 2) as int)) FROM #test WHERE SUBSTRING(ID, 1, 6) LIKE @search)
SET @new_id = @search + RIGHT('00' + CAST(@max + 1 as varchar(2)), 2)
END
ELSE
BEGIN
SET @new_id = @search + '01'
END
SELECT @new_id
DROP TABLE #test
September 3, 2014 at 9:55 am
Inserting data into table both ways, through application and running insert statements as well.
Application : A process is running in client FTP this process picking data and inserting into table through application.
Manually : Sales team receiving leads from different sources and collecting the same and inserting into table manually.
The ID will be unique for all rows. Suppose one entry comes for insertion is the logic would be like this
> search company name+city+zip against the same table
> If exists then do not insert
>If not exists then generate ID with above logic and inserting into table
>If the 6 character of new ID (3 character from company name and 3 character from city) matches with existing ID then assign the next sequence no in last two digit (sequence no - 0 to 99)
like DIMNEW01, DIMNEW02, DIMNEW03 etc.
September 3, 2014 at 9:57 am
What I don't understand is why you'd want to insert the same company into your table more than once - unless this is a child table with a foreign key relationship to the table of companies? If that's the case, please will you post the DDL for the two tables?
John
September 3, 2014 at 10:03 am
joduvil (9/3/2014)[/b
Manually : Sales team receiving leads from different sources and collecting the same and inserting into table manually.
Meaning your sales teams has SSMS and is creating these insert queries themselves??? Just shoot me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2014 at 10:19 am
yes.. they are inserting themselves..
September 3, 2014 at 10:30 am
I have no words....
September 3, 2014 at 10:38 am
Yes.. inserting manually from their end - directly into table through statements
September 3, 2014 at 10:54 am
Well... then it's kind of their end not to screw it up.
September 3, 2014 at 11:02 am
joduvil (9/3/2014)
Yes.. inserting manually from their end - directly into table through statements
Why in the world are you allowing sales people to manually create and execute insert statements??? Not only is your very strange concept of a "key" flawed the process you allow in your company is downright scary.
What benefit are you going to achieve by incrementing the number of times an otherwise identical row has been inserted into your table? This whole thing screams of really bad architecture on top of really bad business decisions.
I am not trying to be rude but seriously you allow non technical people to use SSMS to create queries. Then you want to have the database protect these people from themselves.
If I get some time later today I will see if I can help you figure out a trigger to deal with this but to be honest it is going to a challenge because you have nothing in your data to uniquely identify a row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply