I have a table with following schema and some sample data
GID Name Rank
===========================
1 Binson 1
1 Manu 2
1 Anu 3
1 Shyam 4
My Problem is : If i insert a new set of values then all remainig row should update /Adjust by it rank column based on the rank value of new row value ie:
If i insert new set values :{ 1,Anju,3) Then my table should adjust by given format
You could do this from a
trigger within the database set to the table. The trigger could be an after insert trigger but if you have set the table to have a UNIQUE constraint on this column you would have to do it in a before trigger.
Marked as answer by Amy Peng - MSFT on Nov 19, 2012 12:40 AM
declare @tab table (
GID int, Name varchar(100), Rank int
)
insert into @tab values
(1, 'Binson', 1), (1, 'Manu', 2), (1, 'Anu', 3), (1, 'Shyam', 4)
select * from @tab
begin try
begin tran
declare @gid int, @name varchar(100), @rank int
set @gid = 1
set @name = 'Anju'
set @rank = 3
update @tab set Rank = Rank+1 where Rank>=@rank
insert into @tab values(@gid, @name, @rank)
commit
end try
begin catch
if @@TRANCOUNT>0
rollback
select ERROR_MESSAGE()
end catch
Please try to use the following code and it can help you:
create trigger testtrigger on testtable for insert
as
begin
update testtable set Rank=Rank+1 where Rank>=(select Rank from inserted)
update testtable set Rank=Rank-1 where Name=(select Name from inserted)
end
insert into testtable values(1,'Anju',3);
Regards,
Amy Peng
Please mark the replies as answers if they help or unmark if not.
Feedback to us
binson143
Member
424 Points
179 Posts
Itrate and update the Rows in Sql
Nov 09, 2012 11:35 AM|LINK
Dear all,
I have a table with following schema and some sample data
GID Name Rank
===========================
1 Binson 1
1 Manu 2
1 Anu 3
1 Shyam 4
My Problem is : If i insert a new set of values then all remainig row should update /Adjust by it rank column based on the rank value of new row value ie:
If i insert new set values :{ 1,Anju,3) Then my table should adjust by given format
GID Name Rank
===========================
1 Binson 1
1 Manu 2
1 Anju 3
1 Anu 4
1 Shyam 5
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Itrate and update the Rows in Sql
Nov 09, 2012 11:59 AM|LINK
Do the update before you add the row.
Update yourtable
set rank = rank + 1
where rank >= @newRank
insert into yourtable
etc
tmorton
All-Star
56351 Points
9692 Posts
ASPInsiders
Moderator
Re: Itrate and update the Rows in Sql
Nov 09, 2012 12:38 PM|LINK
And be sure to wrap both operations (the UPDATE and the INSERT) into a transaction, so that if either one fails you can roll back both of them.
ASP.NET/IIS.NET Website Manager, Neudesic
UselessChimp
Member
210 Points
110 Posts
Re: Itrate and update the Rows in Sql
Nov 10, 2012 08:20 PM|LINK
You could do this from a trigger within the database set to the table. The trigger could be an after insert trigger but if you have set the table to have a UNIQUE constraint on this column you would have to do it in a before trigger.
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Itrate and update the Rows in Sql
Nov 11, 2012 06:11 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone
Amy Peng - M...
Star
10135 Points
960 Posts
Microsoft
Re: Itrate and update the Rows in Sql
Nov 13, 2012 03:04 AM|LINK
Hi binson143
Please try to use the following code and it can help you:
create trigger testtrigger on testtable for insert as begin update testtable set Rank=Rank+1 where Rank>=(select Rank from inserted) update testtable set Rank=Rank-1 where Name=(select Name from inserted) end insert into testtable values(1,'Anju',3);Regards,
Amy Peng
Feedback to us
Develop and promote your apps in Windows Store