Trigger Help Needed

[AnneThorne]

Hi, I have never written a trigger before. This is probably very simply, but I need some assistance: The basic concept is as follows. When user puts values into Table One, a TRIGGER occurs and values are automatically put into Table Two: Table One has 3 basic columns, and Table Two has 2 basic columns. Table One NumStart: 1 NumEnd: 3 Code: A Table Two Num: 1 Code: A Num: 2 Code: A Num: 3 Code: A Thank you in advance for any help you can give. Anne

[Stephan Samuel]

Anne, (I'm assuming SQL Server 2000.) It looks like you're trying to insert and expand in the trigger. The body of the trigger has access to a table called "inserted" that contains the inserted values. Inserting is easy, but running your expansion is more difficult and poor form in a set-based language like SQL. This does what you want: create trigger mytrigger on tableone for insert as begin -- trigger declare @ptr int, -- your loop variable @code char -- what to insert each time -- probably can combine the next two lines if clever select @code = code from inserted select @ptr = numstart from inserted while @ptr <= (select numend from inserted) begin -- while -- consider a select .. into instead of next insert into tabletwo (num, code) values (@ptr, @code) select @ptr = @ptr + 1 end -- while end -- trigger The motherhood-and-apple-pie stand on this, all else being equal, is that you should use neither a trigger nor the second table for this. It says that you should always avoid triggers unless there's no other solution (there is here), and you shouldn't store your data twice (table two simply repeats information you can find in table one). Instead, you should just store the start, end, and code, and craft a query that will give you a result set containing (1, A), (2, A), (3, A) that runs every time you want that result. I'm assuming you have a good business case for this though, like a performance enhancement or something. Stephan