May 23, 2002 at 3:29 am
Hi,
We have an asp web aplication using COM on a SQL Server 2000 database. The application uses COM to perform the database access. On altering data, the code checks the @@rowcount to see if the intented insert or update was succesfull - this is done on changes in various colums in various locations in the code.
Now we need to add a trigger to update changed data into other tables. This is because we start migrating to a different data structure - while keeping most of the original code intact.
The problem is now, that the trigger beeing fired erases the @@rowcount. I can imagine others to have experienced the same problem. So, the big question is here:
Is there a solition/workaround to keep the @@rowcount on update/insert/deletes on a table evn if a trigger fires?
Thank you,
Marco van Schagen,
Crexx, the Netherlands
May 23, 2002 at 5:46 am
My trriger does not affect me in this way. Are you calling @@ROWCOUNT immediately after the INSERT/UPDATE/DELETE statement? Here is my Example
CREATE TABLE [A] (
[ONE] [int] NOT NULL ,
[TWO] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ONE]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO A (ONE, TWO) VALUES (1, 'good-bye')
INSERT INTO A (ONE, TWO) VALUES (2, 'B')
INSERT INTO A (ONE, TWO) VALUES (3, 'B')
INSERT INTO A (ONE, TWO) VALUES (4, 'B')
INSERT INTO A (ONE, TWO) VALUES (5, 'B')
GO
CREATE TRIGGER tr_TestIdea ON dbo.A
FOR UPDATE
AS
UPDATE A SET TWO = 'B' WHERE ONE = 3
GO
UPDATE A SET TWO = 'A' WHERE TWO = 'B'
SELECT @@ROWCOUNT
GO
The update will affect 4 rows and the Trigger only one but I get 4 on my @@ROWCOUNT. Are you sure something else is not affecting you?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 6:44 am
Thank you.
I tried your example on our database and you are completely right. The select @@rowcount outside the trigger returns the correct value (4) - for the user as if no trigger was executed.
I also tested the value of @@rowcount INSIDE the trigger, and added more code to the trigger in your example. The @@rowcount inside the trigger gave an other value (1) - it did not bother the @@rowcount outside the trigger.
I wrote a similar test on my trigger - same result (not noting any problems indeed).
I really wonder what really is going wrong; appearently my problem is caused somewhere else, I would think it beeing something between COM and SQL server. The COM component uses ADO to execute the commands that are issued.
Next is an excerpt from the code I use to issue the update command.
Where do I loose the rowcount?
Dim objCmd As ADODB.Command
Dim AdoConnection As ADODB.Connection
Dim strSQL As String
Dim lngRowsAffected As Long
Connect AdoConnection 'using our database definition
Set objCmd = New ADODB.Command
strSQL = "INSERT into tblFlexVacdataValues (VAC_GUID,FCF_Code,FVV_RepNum,FVV_Data) VALUES (?,?,?,'" & Replace(strData, "'", "'+CHAR(39)+'") & "')"
Ado_AddInputParameter objCmd, "tblFlexClientdataValues.VAC_GUID", adGUID, 16, strVAC_GUID
Ado_AddInputParameter objCmd, "tblFlexClientdataValues.FCF_Code", adVarChar, 25, strFCF_Code
Ado_AddInputParameter objCmd, "tblFlexClientdataValues.FVV_RepNum", adInteger, , intRepNum
'perform the action
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
objCmd.Execute lngRowsAffected
If lngRowsAffected <> 1 Then
Err.Raise ERR_UPDATE_FAILURE, ERR_SOURCE, "Save failed: wrong number of rows [" & lngRowsAffected & "], " & strActionDisplay & " [" & FCS_Target & " " & strFCS_Code & "/" & strFCF_Code & "]"
End If
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy