Insert into with select + Trigger (3 tables)

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

SQL Server 2000 Forums

SQL Server Development (2000)

Insert into with select + Trigger (3 tables)

Author

Topic

e.bar
Starting Member

25 Posts

Posted - 2006-03-08 : 13:39:41

Hi, genius!

I have this:

insert into table2 (memberID, productID, total, period, firstpayment)
select memberID, productID, total, period, firstpayment
from table1

Running the query above, a trigger into table2 would send 5000 rows to table3, but send just the last row. I don´t know what to do to send 5000.

I need help.

Tks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 13:40:42

show us the trigger code.

Go with the flow & have fun! Else fight the flow

e.bar
Starting Member

25 Posts

Posted - 2006-03-08 : 14:07:32

[code]
CREATE TRIGGER InsPeriods ON dbo.table2
FOR INSERT
AS
declare
@indice int,
@itemID int,
@period int,
@payment_date datetime,
@period_value decimal(15,2)
select @period=period, @period_value=total/period, @payment_date=firstpayment, @itemID=itemID from inserted
set @indice = 1
while @indice <= @period
begin
insert dbo.table3 (itemID, installment, period_value, payment_date)
values (@itemID, @indice, @payment_value, DATEADD(month,@indice-(1),@payment_date))
set @indice= @indice+1
end
[/code]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 14:20:33

what you're doing is selecting the last row into the variables and inserting that.
for this you need to use a cursor.

of course almost anything can be done without cursors, so if you tell us in more detail what you want to do we might be able to help you more with setbased approach which will be faster.

Go with the flow & have fun! Else fight the flow

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-08 : 14:25:23

Your trigger is only designed to handle one row at a time.

A trigger only fires once per insert statement, no matter how any rows are inserted.

You will have to re-write it to handle multiple rows in the INSERTED table.

CODO ERGO SUM

e.bar
Starting Member

25 Posts

Posted - 2006-03-08 : 15:12:33

Please, how do I re-write it to handle multiple rows?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-08 : 15:52:06

It's all explained fairly well in SQL Server Books Online.

Start by reading the "CREATE TRIGGER" subject in BOL.

CODO ERGO SUM

e.bar
Starting Member

25 Posts

Posted - 2006-03-08 : 19:15:51

I´m new to triggers and I need help to. Follow the code that fires only one row:


CREATE TRIGGER InsPeriods ON dbo.table2
FOR INSERT
AS
declare
@indice int,
@itemID int,
@periods int,
@payment_date datetime,
@period_value decimal(15,2)
select @periods=periods, @period_value=total/periods, @paymentdate=startpayment, @itemID=itemID from inserted
set @indice = 1
while @indice <= @periods
begin
insert dbo.table3 (itemID, installment, periodvalue, paymentdate)
values (@itemID, @indice, @paymentvalue, DATEADD(month,@indice-(1),@paymentdate))
set @indice= @indice+1
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources