December 22, 2009 at 10:11 pm
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
December 22, 2009 at 11:24 pm
Hi,
--Tally TABLE
SELECT TOP 1000 IDENTITY(INT,0,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns
--use this statement in your trigger
select a.[ID],a.Heading,b.n
from MYTABLE a inner join
Tally b
on b.n between a.[MIN] and a.[MAX]
/*where conditions*/
December 22, 2009 at 11:30 pm
Thank You Sir/Madam.
Even I manage to create a following Trigger:
Create TRIGGER my_tgr ON Heading
FOR INSERT
AS
BEGIN
declare @min-2 int
declare @max-2 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
December 23, 2009 at 7:55 am
mail2payan, you should read Jeff Moden's article on the tally table, to understand the solution provided by arun.sas. It can be found here...http://www.sqlservercentral.com/articles/T-SQL/62867/. If you are not familiar with the concept, then do yourself a favor and read it. Also, you should read this one...http://www.sqlservercentral.com/articles/Best+Practices/61537/. It talks about the best way to post both your object structures and sample data to get quick replies. Since your data set was small I did it for you, but given a more complex problem, you will need to do this yourself, or many folks will not take the time to help with your problem.
So, here is how you can post the table set up...
--Create the tables
IF OBJECT_ID('Heading','u') IS NOT NULL
DROP TABLE Heading
CREATE TABLE Heading
(
ID INT IDENTITY(1,1),
Heading VARCHAR(20),
[Min] INT,
[Max] INT
)
IF OBJECT_ID('Range','u') IS NOT NULL
DROP TABLE Range
CREATE TABLE Range
(
ID INT IDENTITY(1,1),
HeadingID INT,
Range INT
)
Now, as for the trigger, the tally table approach works with your sample data. However, if you are ever doing inserts of disconnected records, or multiple ranges, you will get bad results. *If* your ranges are *always* contiguous, and *If* you are *always* only inserting one record per Heading at a time, then this should work for you. In any event, you should play with it in a test database somewhere, where you can try to break it. Also, you will need to make sure your tally table starts at 0, and not 1. If it starts at 1, you can just adjust the JOIN criteria in the trigger.
--Create the trigger
IF OBJECT_ID('my_tgr','tr') IS NOT NULL
DROP TRIGGER my_tgr
GO
CREATE TRIGGER my_tgr
ON Heading
FOR INSERT
AS
INSERT INTO Range (HeadingID,Range)
SELECT
i.ID,
n
FROM inserted i INNER JOIN Tally t
ON t.n > = i.[min]
AND t.n < = i.[max]
Now, insert your test data, which is provided in a readily consumable format.
--Insert the records
INSERT INTO Heading (Heading,[Min],[Max])
SELECT 'ABC',0,3 UNION ALL
SELECT 'XYZ',2,4 UNION ALL
SELECT 'MNP',1,3
Now see if the results are as you require. Again, your sample data was very small and tame, so make sure you test this out on a larger, more onery data set. And read the articles I suggested.
--See the results
SELECT
*
FROM range
ORDER BY HeadingID,range
Good luck, and let us know if you still need help.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply