Range Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Range Update

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-12-23 : 00:04:17

Hello there,

I have 2 tables: Heading & Range
In Heading, the columns are: ID, Heading, Max, Min
In Range, the columns are: ID, HeadingID, Range

Now, I want whenever I insert the data in Heading like:

-------------------------
ID | Heading | Max | Min
-------------------------
1  |  ABC    |  0  |  3
2  |  XYZ    |  2  |  4
3  |  MNP    |  1  |  3
-------------------------

Then with help of Trigger, it should update the Range table in following way:

-----------------------
ID | HeadingID | Range
-----------------------
1  |    1      |   0
2  |    1      |   1
3  |    1      |   2
4  |    1      |   3
5  |    2      |   2
6  |    2      |   3
7  |    2      |   4
8  |    3      |   1
9  |    3      |   2
10  |    3      |   3
-----------------------

Please help!

Daipayan

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-23 : 00:29:45

Here your Trigger

Create TRIGGER my_tgr ON Heading
FOR INSERT
AS
BEGIN

declare @min int
declare @max int
declare @heading int
declare @i int

select @heading=Heading,@min=[min],@max=[max] from inserted

set @i=@min

while(@i<=@max)
Begin

insert into [Range](HeadingID , [Range])
select @heading,@i

set @i=@i+1
End

End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

daipayan
Posting Yak Master

181 Posts

Posted - 2009-12-23 : 00:45:28

Thank You Sir!

Daipayan

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-23 : 00:48:25

quote:
Originally posted by daipayan

Thank You Sir!

Daipayan

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 01:53:07

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

daipayan
Posting Yak Master

181 Posts

Posted - 2009-12-23 : 02:00:47

MS SQL 2000

Daipayan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 05:12:12

[code]
select headingid,range from
(
select number, id as headingid,case when [max]+number-1>[Min] then -1 else [max]+number-1 end as range
from your_table, master..spt_values
where type='p' and number between 1 and @total
) as t
where range>-1
order by 1[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources