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 7 2048
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.et i.br> wrote in message
news:d9******** **@domitilla.ai oe.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
Lucio Chiessi (lu************ ******@vorio.et i.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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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_ESPECIAI S]
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,@nPr ecNov =
ins.PRECO, @lIsCompra = ins.ISCOMPRA from inserted as ins
Insert into HIST_CHG_PRECO values
(GetDate(),@cPr odNo,@cFrClNo,@ lIsCompra,@nPre cAnt,@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.
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
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.et i.br> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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_ESPECIAI S] 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,@nPr ecNov = ins.PRECO, @lIsCompra = ins.ISCOMPRA from inserted as ins Insert into HIST_CHG_PRECO values (GetDate(),@cPr odNo,@cFrClNo,@ lIsCompra,@nPre cAnt,@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.
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.et i.br> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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
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.et i.br> wrote in message news:11******** *************@g 43g2000cwa.goog legroups.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dunc |
last post by:
I'm new to Postgres, and getting nowhere with a PL/Perl trigger that
I'm trying to write - hopefully, someone can give me some insight into
what I'm doing wrong. My trigger is designed to reformat /
standardize phone numbers and it looks like this:
CREATE or REPLACE FUNCTION fixphone() RETURNS trigger AS $$
$number .= $_TD->{new}{phone};
$number =~ s/(-|\.|\(|\)| )//g;
$number .= substr($number,0,3) . "." .
|
by: Manoj S. P. |
last post by:
Hi Folks,
My basic requirement is I want to write a trigger on a table based on
certain conditions post-update from another table in another database.
The actors in this scenario are:
Database D1, Table T1
Database D2, Table T2, T3
Action:
|
by: teddysnips |
last post by:
SQL Server 2000
I have a stored procedure that uses an extended SPROC to send an email
notification to my customers when a document is distributed.
However, the SPROC has an unexpected side effect.
If I run it in its current incarnation, it only sends one email and
then exits. However, if I remove or comment out the block
|
by: SUKRU |
last post by:
Hello everybody.
Unfortunately I am pretty new to sql-server 2000
I need some help with a Trigger I created. I created a trigger witch
takes the id of the affected row and does a update on a other table
with that ID.
The trigger works fine with one affected row. But when there are more
then one rows affected, i get an error.
I found out that SQL-server does not support row-level triggers.
I should probable make my own cursor and...
|
by: mob1012 via DBMonster.com |
last post by:
Hi All,
I wrote last week about a trigger problem I was having. I want a trigger to
produce a unique id to be used as a primary key for my table. I used the
advice I received, but the trigger is still not working correctly. Here is my
code:
create trigger emp_update_id
BEFORE update on emp_update
REFERENCING NEW AS N for each row
SET unique_id = Generate_unique();
| |
by: Benzine |
last post by:
Hi,
I have an issue with my replication at the moment. I will try to
describe the scenario accurately.
I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:
"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
|
by: silversubey |
last post by:
I am trying to setup a trigger that sends an email if a field is changed to specific data. The trigger works when ever the field is changed, but I only need an email if the field is changed to 'In Review'
Any help is greatly appreciated.
-- Create the trigger
CREATE TRIGGER reviewntc
--indicate which table the trigger is to be executed on
ON CltDue
--indicate that this an UPDATE Trigger
|
by: Yas |
last post by:
Hi everyone
I am trying to create a DELETE Trigger. I have 2 tables. Table1 and
Table2. Table 2 has all the same fields and records as Table1 + 1
extra column "date_removed"
I would like that when a record is deleted from Table 1, the trigger
finds that record in Table2 and updates the date_removed filed with
current time stamp.
The primary key on both is combination of domain,admin_group and cn.
|
by: Ed Dror |
last post by:
Hi there,
I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro.
I have a Price page (my website require login) with GridView with the
following columns
PriceID, Amount, Approved, CrtdUser and Date
And Edit and Delete buttons
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |