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
>
>

Don't Trigger

Is there anyway to not trigger (a trigger) upon a given statement i.e.:
DONT TRIGGER BABY
exec my_SP
thanks
capsch--DONT TRIGGER BABY
That would be a good suggestions for the next syntax release of SQL Server,
but until now you have to disable and enable the trigger again:
ALTER TABLE <SomeTable> DISABLE TRIGGER ALL
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"red" wrote:

> Is there anyway to not trigger (a trigger) upon a given statement i.e.:
> DONT TRIGGER BABY
> exec my_SP
>
> thanks
> capsch
>|||On 10 Aug 2005 01:22:39 -0700, red wrote:

>Is there anyway to not trigger (a trigger) upon a given statement i.e.:
>DONT TRIGGER BABY
>exec my_SP
>
>thanks
>capsch
Hi capsch,
As Jens says: you can use ALTER TABLE to disable trigger execution. But
be aware that this affects all copnnections, not just your connection.
If others are changing data at the same time, the trigger won't fire for
their modifications either!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Exactamondo Hugo thats the problem|||On 10 Aug 2005 06:01:10 -0700, red wrote:

>Exactamondo Hugo thats the problem
Hi red,
Unfortunately, there are no easy workarounds. You'll have to cheat in
some way or another.
One idea is to have the SP add a row in a helper table. Try to read that
row from the trigger, using a READPAST hint. If there's no row there, or
if it's there butinserted from another connection, execute the trigger.
If the row is read, return without doing anything. The downside of this
is that it will serialize execution of the stored procedure, and that
it's quite open to abuse by anyone who knows the trigger code.
You can use a similar trick with an extra column in the table, that can
be set to a "magic value" in the stored procedure.
Another option (but not exactly "safe" either): check @.@.NESTLEVEL to see
if the trigger is called from a stored procedure. Note that this won't
reveal which SP is active - it will only allow you to differentiate
between INSERT, UPDATE, DELETE executed from stored proc, or INSERT,
UPDATE, DELETE executed as a astraight query.
Of course, the main question should be: *WHY* do you want to bypass
trigger execution? Since a trigger is usually designed to maintain
integrity, bypassing it might be very dangerous!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Don't Trigger

Is there anyway to not trigger (a trigger) upon a given statement i.e.:
DONT TRIGGER BABY
exec my_SP
thanks
capsch
--DONT TRIGGER BABY
That would be a good suggestions for the next syntax release of SQL Server,
but until now you have to disable and enable the trigger again:
ALTER TABLE <SomeTable> DISABLE TRIGGER ALL
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"red" wrote:

> Is there anyway to not trigger (a trigger) upon a given statement i.e.:
> DONT TRIGGER BABY
> exec my_SP
>
> thanks
> capsch
>
|||On 10 Aug 2005 01:22:39 -0700, red wrote:

>Is there anyway to not trigger (a trigger) upon a given statement i.e.:
>DONT TRIGGER BABY
>exec my_SP
>
>thanks
>capsch
Hi capsch,
As Jens says: you can use ALTER TABLE to disable trigger execution. But
be aware that this affects all copnnections, not just your connection.
If others are changing data at the same time, the trigger won't fire for
their modifications either!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Exactamondo Hugo thats the problem
|||On 10 Aug 2005 06:01:10 -0700, red wrote:

>Exactamondo Hugo thats the problem
Hi red,
Unfortunately, there are no easy workarounds. You'll have to cheat in
some way or another.
One idea is to have the SP add a row in a helper table. Try to read that
row from the trigger, using a READPAST hint. If there's no row there, or
if it's there butinserted from another connection, execute the trigger.
If the row is read, return without doing anything. The downside of this
is that it will serialize execution of the stored procedure, and that
it's quite open to abuse by anyone who knows the trigger code.
You can use a similar trick with an extra column in the table, that can
be set to a "magic value" in the stored procedure.
Another option (but not exactly "safe" either): check @.@.NESTLEVEL to see
if the trigger is called from a stored procedure. Note that this won't
reveal which SP is active - it will only allow you to differentiate
between INSERT, UPDATE, DELETE executed from stored proc, or INSERT,
UPDATE, DELETE executed as a astraight query.
Of course, the main question should be: *WHY* do you want to bypass
trigger execution? Since a trigger is usually designed to maintain
integrity, bypassing it might be very dangerous!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Don't Trigger

Is there anyway to not trigger (a trigger) upon a given statement i.e.:
DONT TRIGGER BABY
exec my_SP
thanks
capsch--DONT TRIGGER BABY
That would be a good suggestions for the next syntax release of SQL Server,
but until now you have to disable and enable the trigger again:
ALTER TABLE <SomeTable> DISABLE TRIGGER ALL
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"red" wrote:
> Is there anyway to not trigger (a trigger) upon a given statement i.e.:
> DONT TRIGGER BABY
> exec my_SP
>
> thanks
> capsch
>|||On 10 Aug 2005 01:22:39 -0700, red wrote:
>Is there anyway to not trigger (a trigger) upon a given statement i.e.:
>DONT TRIGGER BABY
>exec my_SP
>
>thanks
>capsch
Hi capsch,
As Jens says: you can use ALTER TABLE to disable trigger execution. But
be aware that this affects all copnnections, not just your connection.
If others are changing data at the same time, the trigger won't fire for
their modifications either!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Exactamondo Hugo thats the problem|||On 10 Aug 2005 06:01:10 -0700, red wrote:
>Exactamondo Hugo thats the problem
Hi red,
Unfortunately, there are no easy workarounds. You'll have to cheat in
some way or another.
One idea is to have the SP add a row in a helper table. Try to read that
row from the trigger, using a READPAST hint. If there's no row there, or
if it's there butinserted from another connection, execute the trigger.
If the row is read, return without doing anything. The downside of this
is that it will serialize execution of the stored procedure, and that
it's quite open to abuse by anyone who knows the trigger code.
You can use a similar trick with an extra column in the table, that can
be set to a "magic value" in the stored procedure.
Another option (but not exactly "safe" either): check @.@.NESTLEVEL to see
if the trigger is called from a stored procedure. Note that this won't
reveal which SP is active - it will only allow you to differentiate
between INSERT, UPDATE, DELETE executed from stored proc, or INSERT,
UPDATE, DELETE executed as a astraight query.
Of course, the main question should be: *WHY* do you want to bypass
trigger execution? Since a trigger is usually designed to maintain
integrity, bypassing it might be very dangerous!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Does the UPDATE trigger fire when a record is updated or only when it is deleted?

I've gotten conflicting info about this in the past so I thought I'd try to get clarification.

When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?

ThanksThe update trigger doesnot fire on a delete.

however:

when an update occurs and the update trigger is fired, there are 2 virtual recordset that you can inspect to see what changed

inserted and deleted

the deleted recordset contains the old values
the inserted recordset contains the new values|||dpends on when you set the trigger to fire..could be update and/or delete and/or insert..

CREATE TRIGGER <trigger_name>
ON <table_name>
FOR DELETE, INSERT, UPDATE
AS ...

hth|||Thanks mbanavige. That's what I needed to know.