September 3, 2014 at 1:33 pm
100% I agree with you Sean but this the requirement from our client side. The logic is weird and it is going to be more discrepancy at their end. These things are already shared with them and they are not agree on this. Once the ID crosses 99 then we cant generate further no because only two digits are assigned for sequence no.
I appreciate you for helping to me for the same.
September 3, 2014 at 2:11 pm
OK here we go. This has to be one of the absolute scariest situations I have ever heard of. A truly bizarre "key" and sales people with access to SSMS so they can just add whatever they want. :w00t: You owe it to yourself and your client to create an application to get the sales people out of the database. These types of people should NEVER NEVER NEVER need to have direct access to a database. I rarely use absolutes when it comes to sql server but this is one. If I was working for a client and they gave me anything like the stuff you are telling me I would run away screaming. Remember it is YOU that is going to have to fix the crap they blow up because they won't let you do it right.
The code I came up with a complete kludge but this really odd requirement of smashing portions of different columns into a single column like this forces my hand. This solution is not elegant and may not perform well. Do yourself a favor and make sure you test test test this....also MAKE SURE you understand what it is doing 100%. Your phone is the one that will be ringing at 3am when Sally Saleswoman creates an insert statement and this blows up.
I have create a table named Something, the trigger and finally some test data. Good luck.
CREATE TABLE Something
(
CompanyName varchar(30),
City varchar(30),
ZipCode char(5),
ID varchar(8) null
)
go
create trigger tr_SomethingInsert
on Something INSTEAD OF INSERT as
insert Something(CompanyName, City, ZipCode, ID)
select top (select COUNT(*) from inserted) i.CompanyName
, i.City
, i.ZipCode
, LEFT(i.CompanyName, 3)
+ LEFT(i.City, 3)
+ RIGHT('0'
+ cast(isnull(
(
select MAX(cast(RIGHT(s2.ID, 2) as int))
from Something s2
where s2.CompanyName = i.CompanyName
and s2.City = i.City
and s2.ZipCode = i.ZipCode
)
+ ROW_NUMBER() over(partition by i.CompanyName, i.City, i.ZipCode order by (select null))
, CAST(ROW_NUMBER() over(partition by i.CompanyName, i.City, i.ZipCode order by (select null)) as varchar(2))
) as varchar(2)) --cast(isnull(
, 2) --RIGHT('0'
from inserted i
left join Something s on s.CompanyName = i.CompanyName
and s.City = i.City
and s.ZipCode = i.ZipCode
go
insert Something(CompanyName, City, ZipCode)
select 'Dimentions Media', 'New York', '12345' union all
select 'Dimentions Media', 'New York', '12345'
select * from Something
insert Something(CompanyName, City, ZipCode)
select 'Dimentions Media', 'New York', '12345'
select * from Something
insert Something
select 'asdf', 'city', '12345' union all
select 'asdf', 'city', '12345' union all
select 'asdf', 'city', '12345' union all
select 'asdf', 'city', '12345' union all
select 'asdf', 'city', '12345'
--truncate table something
_______________________________________________________________
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 3:42 pm
You're the best! IT WORKS!! I appreciate for your positive attitude and resolving this weird requirement from our client. Thanks a ton.
September 4, 2014 at 7:26 am
joduvil (9/3/2014)
You're the best! IT WORKS!! I appreciate for your positive attitude and resolving this weird requirement from our client. Thanks a ton.
You are welcome. I hope you take the time to understand the code because it is a bit complicated in spots. This is because a trigger needs to handle multiple row operations and this particular is quite unique.
Now, get those salespeople out of the database and back out in the field where they belong selling stuff so you can hire another developer to create them an application. 😀
_______________________________________________________________
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 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply