Problem in Update Trigger in SQL Server 7.0 - Need some help!

[Lucio Chiessi]

Hi for all on this...

I'm using MS SQL Server 7.0 SP4 in some customers to store some data
from an aplication developed by me.
I created an trigger to run on update.
When I run an update command on it's table and the where condition
returns only one row, the trigger is executed ok, but if where
condition returns more than one rows, the trigger don't run.

I don't know what happens there...
If any can help on this, I will be thankfull!

Regards!

Lucio

[John Bell]

Hi

You didn't post your code to it is hard to comment. Triggers need to cater
for multiple rows being effected by the statement that triggers them. It is
a common (and there are many examples of bad trigger code in these
newsgroups) such as this one http://tinyurl.com/aphnp

John

"Lucio Chiessi" <lu******************@vorio.eti.br> wrote in message
news:d9**********@domitilla.aioe.org...

Hi for all on this...

I'm using MS SQL Server 7.0 SP4 in some customers to store some data
from an aplication developed by me.
I created an trigger to run on update.
When I run an update command on it's table and the where condition
returns only one row, the trigger is executed ok, but if where
condition returns more than one rows, the trigger don't run.

I don't know what happens there...
If any can help on this, I will be thankfull!

Regards!

Lucio

[Erland Sommarskog]

Lucio Chiessi (lu******************@vorio.eti.br) writes:

I'm using MS SQL Server 7.0 SP4 in some customers to store some data
from an aplication developed by me.
I created an trigger to run on update.
When I run an update command on it's table and the where condition
returns only one row, the trigger is executed ok, but if where
condition returns more than one rows, the trigger don't run.

I don't know what happens there...
If any can help on this, I will be thankfull!

A common mistake is to believe that a trigger firers once per row. In
fact it fires once per statement.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lucio Chiessi [VORio]]

hI John. Thanks a lot for your reply!

I'm posting now my code. If you can help me, thanks a lot twice!

================================================== ===============

CREATE TRIGGER [Reg_Chg_Preco] ON [dbo].[PRECOS_ESPECIAIS]
FOR UPDATE
AS
Declare @cProdNo as char(10)
Declare @cFrClNo as char(10)
Declare @lIsCompra as bit
Declare @nPrecAnt as numeric(15,5)
Declare @nPrecNov as numeric(15,5)

IF Update(PRECO)
Begin
Select @nPrecAnt = del.PRECO from deleted as del
Select @cProdNo = ins.PRODNO, @cFrClNo = ins.FRCLNO,@nPrecNov =
ins.PRECO, @lIsCompra = ins.ISCOMPRA from inserted as ins
Insert into HIST_CHG_PRECO values
(GetDate(),@cProdNo,@cFrClNo,@lIsCompra,@nPrecAnt, @nPrecNov)
End
GO

================================================== ================

John Bell escreveu:

Hi

You didn't post your code to it is hard to comment. Triggers need to cater
for multiple rows being effected by the statement that triggers them.

[Lucio Chiessi [VORio]]

Ooops!!

I think that triggers was fired once per row. Am I wrong so!?
My trigger code was made to be used once per row and not once per
statement.
There is another way to made trigger be fired once per row?

Thanks a lot for your replay!!

Lucio

[John Bell]

Hi

This is almost exactly the same as the example in the link I posted,
although what is not clear is what your key is for the tables. If this is
PRECO and that is your only way of uniquely identifying a record, then you
may want to think creating an alternate key that will not change.

John
"Lucio Chiessi [VORio]" <lu***@vorio.eti.br> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

hI John. Thanks a lot for your reply!

I'm posting now my code. If you can help me, thanks a lot twice!

================================================== ===============

CREATE TRIGGER [Reg_Chg_Preco] ON [dbo].[PRECOS_ESPECIAIS]
FOR UPDATE
AS
Declare @cProdNo as char(10)
Declare @cFrClNo as char(10)
Declare @lIsCompra as bit
Declare @nPrecAnt as numeric(15,5)
Declare @nPrecNov as numeric(15,5)

IF Update(PRECO)
Begin
Select @nPrecAnt = del.PRECO from deleted as del
Select @cProdNo = ins.PRODNO, @cFrClNo = ins.FRCLNO,@nPrecNov =
ins.PRECO, @lIsCompra = ins.ISCOMPRA from inserted as ins
Insert into HIST_CHG_PRECO values
(GetDate(),@cProdNo,@cFrClNo,@lIsCompra,@nPrecAnt, @nPrecNov)
End
GO

================================================== ================

John Bell escreveu:

Hi

You didn't post your code to it is hard to comment. Triggers need to
cater
for multiple rows being effected by the statement that triggers them.

[Dan Guzman]

You can't make a trigger fire once per row in SQL 2000. As John mentioned,
you need a unique row identifier that does not change so that you can
correlate the before/after values in your update trigger. You can then use
a single insert statement in your trigger like the example below:

INSERT INTO HIST_CHG_PRECO
SELECT
GetDate(),
ins.PRODNO,
ins.FRCLNO,
ins.ISCOMPRA,
del.PRECO,
ins.PRECO
FROM inserted ins
JOIN deleted del ON ins.PK = del.PK

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Lucio Chiessi [VORio]" <lu***@vorio.eti.br> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Ooops!!

I think that triggers was fired once per row. Am I wrong so!?
My trigger code was made to be used once per row and not once per
statement.
There is another way to made trigger be fired once per row?

Thanks a lot for your replay!!

Lucio

[Lucio Chiessi [VORio]]

Hi Dan.
Help so much!!! Solved my problem...
My thanks to you, John Bell and Erland Sommarskog.
I apreciate very much your help...

Best Regards from Rio de Janeiro - Brazil

Lucio

Dan Guzman escreveu:

You can't make a trigger fire once per row in SQL 2000. As John mentioned,
you need a unique row identifier that does not change so that you can
correlate the before/after values in your update trigger. You can then use
a single insert statement in your trigger like the example below:

INSERT INTO HIST_CHG_PRECO
SELECT
GetDate(),
ins.PRODNO,
ins.FRCLNO,
ins.ISCOMPRA,
del.PRECO,
ins.PRECO
FROM inserted ins
JOIN deleted del ON ins.PK = del.PK

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Lucio Chiessi [VORio]" <lu***@vorio.eti.br> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Ooops!!

I think that triggers was fired once per row. Am I wrong so!?
My trigger code was made to be used once per row and not once per
statement.
There is another way to made trigger be fired once per row?

Thanks a lot for your replay!!

Lucio