Doubt aboult loop in trigger?

Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
ThanksHi,
Anything done inside/called from the trigger will be in a transaction so can
cause blocking which can really quickly degrade the performance and
experience the user gets.
What loopnig are you trying to do, please post your trigger code.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I should do this.
> Thanks
>|||Generally, processing one row at a time inside a loop is not efficient,
compared to processing all rows using a single statement. In many cases,
loops and cursors can be avoided by using SET based queries.
Can you show us what is it that you are trying to do, and may be someone can
suggest an alternative for you.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
Thanks|||Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>|||Here's a small example, that uses a numbers table, to replace the loop with
a singel INSERT statement inside your trigger.
CREATE TABLE Trn (TranID int, TotalSum money, TotalInstallments tinyint)
CREATE TABLE Pmts (TranID int, InstallmentNo tinyint, InstallmentAmt money)
CREATE TABLE Numbers (Number tinyint)
GO
SET NOCOUNT ON
GO
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 12
BEGIN
INSERT INTO Numbers (Number)
SELECT @.i
SET @.i = @.i + 1
END
GO
CREATE TRIGGER TrnInsTrg
ON Trn
FOR INSERT
AS
BEGIN
INSERT Pmts (TranID, InstallmentNo, InstallmentAmt)
SELECT i.TranID, n.Number, i.TotalSum/i.TotalInstallments
FROM inserted AS i
INNER JOIN
Numbers AS n
ON n.Number <= i.TotalInstallments
END
GO
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 30000, 3)
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 120000, 12)
GO
SELECT * FROM Trn
SELECT * FROM Pmts
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ucBkH1i3FHA.4076@.TK2MSFTNGP15.phx.gbl...
Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>

double loop in stored procedure

Dear All,

Im working on a stored procedure that meant to mail out users some of their action items daily.

The procedure has a double loop, first the user ids and user email addresses are selected into a table, then the outer loop cycles through the user ids and selects relevant action items to another table. The inner loop then cycles through these action items and at the end of each outer loop a string is mailed out.

Problem is that as the outer loop selects the relevant items for a user, the table holding the action items basically gets filled with more and more records and the inner loop then adds every item in the table to the string that gets mailed out, ending up with more and more items going to all the users.

I have tried to delete all records from the actionItems table at the end of each outer loop after the content of the action Items are mailed out, however this seems to keep the actionItems table empty at all times.

Not sure if this description is clear enough but I cant see where Im going wrong in terms of approach.

Any ideas?I would suggest posting your SP here, so that we can check out what is going on in the T-SQL code.|||Any TSQL post that uses the word "loop" that frequently can't be good.|||Actually it's sorted, just added a variable that picks up the id of the last action item being added to the e-mail, then the count to add action items to the next e-mail starts from there + 1 AND also I had set this counter to 0 at the start as it would not start the loops while not initialised. Cheers|||Whatever. Continue merrilly down your path to the dark side...|||Actually it's sorted, just added a variable that picks up the id of the last action item being added to the e-mail, then the count to add action items to the next e-mail starts from there + 1 AND also I had set this counter to 0 at the start as it would not start the loops while not initialised. CheersHave you tried dropping charcoal briquettes or real cotton into fuming nitric acid?

-PatP